Union Query Help

atgordon

Board Regular
Joined
Nov 17, 2004
Messages
118
I am trying to write a union query that joins three tables that have the same exact format. Each table has 3 columns - invoice number, payment amount and payment date. Each of the tables has data from a different month (August, September and October). What I would like the union query to do is to provide me with a table listing all of the unique invoices from the 3 tables and the total amount paid on each and the latest payment date. It is possible for an invoice to be partially paid in Auugst and the rest paid in September. So if invoice 123 had $100 paid on August 7 and $150 paid on September 12, I would like the table to show me invoice 123 with $250 paid and September 12 as the payment date.

I think my problem is that I do not know how to sum in a union query or to show the max date in SQL.

Any help would be greatly appreciated. Thanks.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

MyBoo

Board Regular
Joined
Aug 9, 2006
Messages
217
First.... this is a very poor table design.

You should have ONE table to hold this data, not one for each month of the year. You're making your queries much harder to create based on your table design. If you merge all of your data into one table, you don't need a Union query.
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
I totally agree with myBoo that these shouldn't be separate tables, and that it would be a lot easier to do this if it were one table. However if you have some need to keep them separate you can do this with a union query. The sql qould look like this:
Code:
select invoice, sum(payments), max(payment_date) from (
select invoice, payments, payment_date
from tbl1
union
select invoice, payments, payment_date
from tbl2
union
select invoice, payments, payment_date
from tbl3
) 
group by invoice
hth,
Giacomo
 

atgordon

Board Regular
Joined
Nov 17, 2004
Messages
118
Join Query Help

Thanks for both of your replies. I tried to use the union query code you sent but as soon as I entered it and tried to run, I got an error message saying "Syntax error (missing operator) in query expression 'Invoice'" I am not sure how to fix this. I tried putting brackets among other things, but nothing works.

Any help would be greatly appreciated.

Thanks.
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796

ADVERTISEMENT

can you post your sql?
 

atgordon

Board Regular
Joined
Nov 17, 2004
Messages
118
Join Query Help

Below is my sql. I pretty much copied it from your example.

select Invoice No, sum(Payment Amount), max(Payment Date) from (
select Invoice No, Payment Amount, Payment Date
from September 2006 Cash
union
select Invoice No, Payment Amount, Payment Date
from August 2006 Cash
union
select Invoice No, Payment Amount, Payment Date
from July 2006 Cash
)
group by Invoice No
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
does this work?

select [Invoice No], sum([Payment Amount]), max([Payment Date]) from (
select [Invoice No], [Payment Amount], [Payment Date]
from [September 2006 Cash]
union
select [Invoice No], [Payment Amount], [Payment Date]
from [August 2006 Cash]
union
select [Invoice No], [Payment Amount], [Payment Date]
from [July 2006 Cash]
)
group by [Invoice No]
 

atgordon

Board Regular
Joined
Nov 17, 2004
Messages
118
Join Query Help

Yes, it now works. Thank you so much for your help.

Since you were able to help this amateur Access user out, perhaps you can help me on another quick question. Rather than post another string, I was hoping you might be able to assist me.

I am writing a query that pulls fields from one table (Table 1) for its columns, but for one of the columns, i want to see if the invoice number is present in another table (Table 2) and if it is, I want the output to show Yes in that column. In essence, when run, the query would show all the info from Table 1 except for the column "invoice number check," in whcih case it would check if that invoice number is present in Table 2 and if so, it would display yes for that row.

Hopefully this makes some sense and is not too confusing.

Thanks again for all your help. This is really helping me out.
 

Forum statistics

Threads
1,136,272
Messages
5,674,756
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top