Combining 2 tables into 1 query

[Xina5280]

Hi, I am trying to combine 2 tables with the same headings selected in the query wizard. The headings im using are Phase, Date, Total. What I would like to happen is the phases combine to match, sort by month and then calculate the totals from both tables into one column. Everytime I try to do this it comes back with missing info and doesnt fully combine like it should. My relationship is set up with phase to phase outer join (middle option selected). What am I doing wrong? Thanks

 

[jal]

Hi, I am trying to combine 2 tables with the same headings selected in the query wizard. The headings im using are Phase, Date, Total. What I would like to happen is the phases combine to match, sort by month and then calculate the totals from both tables into one column. Everytime I try to do this it comes back with missing info and doesnt fully combine like it should. My relationship is set up with phase to phase outer join (middle option selected). What am I doing wrong? Thanks

I don't even know where these options are found.

However, I do know that a LEFT join preserves all the records of the left table (even those with no matching record in the right table)

SELECT L.*, R.*
FROM LeftTable AS L
LEFT JOIN RightTable as R
On R.Phase = L.Phase


SELECT L.*, R.*
FROM LeftTable AS L
LEFT JOIN RightTable as R
On R.Phase = L.Phase

And then for totals maybe do

SELECT L.Phase, Sum(Total)
FROM LeftTable AS L
LEFT JOIN RightTable as R
On R.Phase = L.Phase
GROUP BY L.Phase

Not sure about the rest. Maybe clarify what you want, a bit more.

 

[boblarson]

Two things -

I would say you probably are looking for a UNION query.

And second, if you have field names of DATE change them now! That is an Access reserved word and you will only cause yourself pain and suffering by using that as a field name.

 

[Xina5280]

Re

They are located in the QUERY WIZARD because I dont know code. If I have too ill learn it. As for what I am trying to do, Ill go as detailed as possible:
Table1 HEADINGS: Phase, Date, ticket, employee, equipment, rate, description, hours, total

Table2 HEADINGS Phase, date, company, invoice #, description, tons, Total

I want to combine the following into a new table: Phase, Date, Total.
There are up to 10 phases that when the tables combine they would be grouped together. Dates would be sorted into months. Total would be the total amount per phase with both tables combined. Hope thats enough detail for ya. Thanks

 

[twoplustwo]

I did something similar the other day and ended up running two append queries with code hiding the warnings on a temporary table (deleting existing records first).

You could just of course run two queries into a normal table to store the values.

 

[neileg]

I would say you probably are looking for a UNION query.

What Bob said.