Combing two tables together

[Jerseynjphillypa]

I have two tables called Data1 and Data2 with the same fields.

I want to include all the DateEnter fields from both tables by ItemName.
So if the date matches both tables, I want to pull the Data1Total and Data2Total into one table by ItemName and DateEnter.

But if a date is on one table and not the other, I also want to pull the respective Data1Total or Data2Total according to the DateEnter off the table




The fields I want for the new table to pull data off the two tables are DateEnter,ItemName,Data1Total, and Data2Total.

[Bob Fitz]

Can you attach a mdb version of your db for those of us that are not using A2007 or A2010.

[Jerseynjphillypa]

No problem..

[Bob Fitz]

Not sure that I really understand your required result but take a look at the attached db. I have created a union query which I have then used to create a Totals query. Does this give the required result.

[Jerseynjphillypa]

Thanks for your help Bob Fitz.

Everything was great but I would like to separate the Total with two columns called Data1Total and Data2Total like the table I created in the db.

So if the Data1total is coming for Data1 Table that's where the data will go and if the Data1total is coming for Data2 table that's where the data will go in the
qryTotals table

Also how to expert the UnionJoin into a table

[Bob Fitz]

Hopfully the attached db will have what you require.
Take a look at the query "qryBothTotals"

[Jerseynjphillypa]

thank you for all your help Bob Fitz

[Bob Fitz]

You're welcome. Glad to help.