Transaction Processing Combing Record Types

[banker123]

I have two tables, one with check data, and one with invoice data. I
would like to combine the records from each table using the
transaction number to join records and creating a query that displays
each record type as displayed below in the output table. Help!
Thanks!

Check Table
Record Transaction Amount Check Number
04 1 100 12345

Invoice Table
Record Transaction Invoice Amount
06 1 1234 50
06 1 5678 50


Output
Transaction Record Amount Invoice/Check Number
1 04 100 12345
1 06 50 1234
1 06 50 5678

 

[John Spencer]

That sounds like a UNION query to me. Two things, Union queries cannot be
built in the query grid, but must be built in the SQL view and Union queries
are not updateable.

SELECT Transaction, Record, Amount
, [Check Number] as [Invoice/Check Number]
, "Check" as ItemType
FROM [Check Table]
UNION ALL
SELECT Transaction, Record, Amount, Invoice
, "Invoice" as ItemType
FROM [Invoice Table]
ORDER BY Transaction, ItemType, Record

Lazy Man's hint.
Build the two parts of the union query in the query grid in two separate
queries. Switch to SQL view. Open a third query and copy-paste from the
SQL view of the first two queries. Remove the semi-colons at the end of the
two and insert UNION ALL between the two.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[banker123]

Worked great, thanks!