combining the records of 3 tables in a specific order

[wayneb777]

i have 3 tables with different fields in them except for two, account# and charge#. i need to combine in such a way that the first output row would have the table 1 fields, the next row table 2 fields and the next row table 3 fields for each account/charge number like so...


row 1: facility#, lic#, account type
row 2: diag#, diag description
row 3: proc#, proc description
row 4: facility#, lic#, account type for the next account/charge combination
row 5:...

row 4 would begin for the next account/charge.

i'm not sure how to output the data sorted in this way.

[orange]

Do NOT use embedded spaces or non-alphanumeric characters(#) in your field and object names. It will save you many syntax errors.

I suggest you
-tell us WHAT this data represents in simple English business terms, and
-show us some sample data to help us understand your situation.

[ranman256]

there is no 'row order' in access...its just how the table is currently sorted.
if your
row 1: facility#, lic#, account type
row 2: diag#, diag description
row 3: proc#, proc description

are all related, then I would add an extra field in all 3 tables, say, "batch"
so then, you can just throw all tables together, then sort the way you want.

row 1: facility#, lic#, account type, 1
row 2: diag#, diag description, 1
row 3: proc#, proc description, 1
row 4: facility#, lic#, account type, 2
row 5: diag#, diag description, 2

[June7]

Not seeing account# and charge# fields in the example data. Do they make up a compound PK/FK?

Assuming the 3 tables are something like: Facilities, Diagnoses, Procedures - think will need a UNION query.

SELECT [account#], [charge#], [facility#] AS somename, [lic#] AS somename, [account type] AS AccountType, "fac" AS Source, 1 AS Seq FROM Facilities
UN ION SELECT [account#], [charge#], [diag#], [diag description], Null, "diag", 2 FROM Diagnoses
UN ION SELECT [account#], [charge#], [proc#], [proc description], Null, "proc", 3 FROM Procedures;

Now use that query as source for subsequent queries and report. Since the Source field has values that happen to alphabetically fall into the order you want, the Seq field could be eliminated.

Advise no spaces and special characters/punctuation (underscore is exception) in naming convention.