Two Column Report

[DS]

I want to do a two column report as such...

Date Time Credit Debit
1/1/08 4:30 $6.00
1/3/08 4:24 $2.00
1/4/08 1:30 $4.00

etc.

The problem is the credit amount comes from one table and the debit from
another, there is also the main table holding customer info. So it's three
tables. The main one and two detail tables. How can I accomplish this?
Thanks
DS

 

[Duane Hookom]

Create a union query of the Credit and Debit tables like
SELECT "Credit" as TransType, CustomerID, TheDate, TheTime, AmtField
FROM tblCredit
UNION ALL
SELECT "Debit", CustomerID, TheDate, TheTime, AmtField
FROM tblDebit;

Then join the union query to the customer info.

Finally, create a crosstab query with the Customer fields, TheDate, TheTime
as Row Headings, TransType as Column Heading, and Sum of AmtField as the
Value.

 

[DS]

Thanks Duane, Just one other question. Should I not have the two tables?
Should I have both Credits and Debits in one table wlth a checkbox to
indicate a debit?
Thanks
DS

 

[Duane Hookom]

If I were to start over with your application, I would probably create a
single table for debits and credits.

 

[DS]

Thanks Duane,
I did just that, since I was still at a point where it could be done. For
some reason I thought that it would work better having them seperate, but
instead it made it harder on the reporting front. Thank you for your help
and suggestions, I appreciate them both.
DS