Creating a list of three tables

[Guest]

I have three separate tables in the accounting department:

Account codes, Locations, Program codes.

I want to create (I guess through using a query) an output of all potential
combinations among three tables.

Example: accounts: 1000, 2000. Locations: 100, 200. Programs: 01,02

This is what I would like to get:

Account Location Program
1000 100 01
1000 100 02
1000 200 01
1000 200 02
2000 100 01
2000 100 02
2000 200 01
2000 200 02

Please let me know a.s.a.p. if this is possible to do in Access (if not how
else).

Thanks in advance,

Arnon

 

[Guest]

What you want is a Cartesian result - normally undesireable product.

Put all three tables in a query and do not join. Put each of the fields in
the output row.

Your results should be the multiplication of all the different items.
Apple
Orange - 2

Pretzels
Peanuts - 2

Rice
Corn
Wheat - 3

2 * 2 * 3 = 12
Fruit Grain Snacks
Apple Rice Pretzels
Orange Rice Pretzels
Apple Rice Peanuts
Orange Rice Peanuts
Apple Corn Pretzels
Orange Corn Pretzels
Apple Corn Peanuts
Orange Corn Peanuts
Apple Wheat Pretzels
Orange Wheat Pretzels
Apple Wheat Peanuts
Orange Wheat Peanuts

 

[John Spencer]

Easy.

Add all three tables to the query grid.
Do not join them in anyway.
If you get a join line between the tables, click on the join line and delete it.
Select the fields you want to show

Run the query.

An SQL statement would look like

SELECT [Account Codes].Account, Locations.Location, [Program Codes].Program
FROM [Account Codes], Locations, [Program Codes]