Union Query Help

[atgordon]

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.

 

[MyBoo]

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]

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:

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]

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]

can you post your sql?

 

[atgordon]

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]

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]

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.