Pull data from multiple tables without a join

[RobbieP]

I have monthly static tables with exactly the same fields. I want to pull the claim number which matches a criteria within the same monthly table into one set of data for each month. A bonus would be to add a field which states the source table for each claim. Say I have three tables: JanClaims, FebClaims and MarClaims. I want to pull the claim number (which is a field in each table) from each table where the claim has a status (another field) of "OPEN". The third field it would pull would be the source (JanClaims, FebClaims or MarClaims).

Is there a slick way of doing this without writing a select statement with a join command over and over again (I need 18 months of data for now...)

Rob

 

[sheetspread]

You can build the query with a VBA loop to avoid manual repetition, but isn't it better to keep all the months in a single table?

 

[RobbieP]

yes, it would be better but I fighting seven years of historic data being stored monthly. This would be a one time need.