Add all transactions under each other

[jamesborne]

Dear buddy access programmers,

I have a database consisting of 2 tables:
Table A: Sales
Table B: Returns

I am trying to create a query that has the following fields:
Date
Transaction type (sales/return)
Quantity

Can you guys help me out?



Again, my main question is how do I put the sales and returns under each other according to the date.

[Note: I am not a beginner so please don't try explaining the basics of creating a query.]

Thank you in advance.
James Borne

[jzwp11]

You will need to create a UNION query which must be done in SQL view something along these lines


SELECT A.Date, A.Quantity, "Sales" as TransType
FROM A

UNION

SELECT B.Date, B.Quantity, "Returns" as TransType
FROM B


If you are able to change your table stucture, I would recommend 1 table to hold all transactions

tblTransactions
-pkTransID primary key, autonumber
-dteTrans (transaction date)
-qty
-TransType (or you can handle returns as just negative quantites and sales as positive quanitites then you would not need this field)

[jamesborne]

Thank you for your quick reply!

I have done exactly what you wrote. That worked a treat however I faced a little issue:

The records seem to not all be there in the union query. For example, in my sales table I have 1000 records. In my returns table I have 500. That's a total of 1500. However, in the union query I have something like 1100 total.

Do you happen to know what's causing the problem?

Thanks again buddy.
James Borne

[jzwp11]

If I remember correctly, a union query will return unique records, so if there are records in either source that have the same date & quantity (as well as the same values in any other field) then the union query will only return 1 record.

Do you have a field with unique values (such as a primary key field) that you can include in both subqueries to see if that makes a difference in the records returned?

[jamesborne]

Hey!
I managed to work it out actually. The main trick here is as follows:

Instead of simply using "Union"... just replace with "Union all"...

That worked a treat.

Thanks for your help mate, that was splendid

James Borne.

[jzwp11]

Glad you got it worked out. Good luck with your project.