help with query to combine independent tables 1

[safaryan]

this may sound a really dumb question since I'm new to Access and relational databases - I have 3 independent tables: tblBudget_CA, tblBudget_WA, tblBudget_OR (one for each State) with identical fields each: Market, Brand, Account, Amount. I need a query that combines all three in one table with the same fields: Market, Brand, Account, Amount. I have tried a make-Table query and it doesn't work since I get duplicate fields (3 Market fields, 3 Brand fields, etc). The key is to keep the tables independent. Any help is greatly appreciated.

Max

 

[Golom]

Select * INTO NewTable

From
(
Select 'CA' As State, X.* From tblBudget_CA X
UNION ALL
Select 'WA', X.* From tblBudget_WA X
UNION ALL
Select 'OR', X.* From tblBudget_OR X
)

 

[safaryan]

Golom
I tried it and it worked, you are the best - thanks! the only problem is I get weird field names - should I replace the Xs with something else?

Max

 

[Golom]

You should be getting "State" as the first field name followed by the field names as they appear in tblBudget_CA. Is that not what you are seeing?

Can you provide more detail about how the field names are "weird"?

 

[safaryan]

I've got "X7YZ_____1_State" as my State field. I guess I had to replace the Xs in your code with the field names - correct?

 

[Golom]

I'm not sure but maybe "State" is a reserved word. Try

Select * INTO NewTable

From
(
Select 'CA' As [red][[/red]State[red]][/red], X.* From tblBudget_CA X
UNION ALL
Select 'WA', X.* From tblBudget_WA X
UNION ALL
Select 'OR', X.* From tblBudget_OR X
)

 

[safaryan]

Golom - it worked - thanks!