Query design

[Not_a_notes_fan]

Hi. I might be doing this the wrong way, but I'm open to suggestions.

I have 3 tables, tblCustomers, tblProducts and tblPurchases. I want
tblPurchases to hold records of orders, so I have the fields:
Order ID (PK) Autonum
Customer ID Number
Order Date Date/Time
Line 1 Number
Quantity 1 Number
Line 2 Number
Quantity 2 Number
....
Line 10 Number
Quantity 10 Number

I have 1 to many relationships from Line 1, Line 2 etc to the Product ID
(PK) within tblProducts. When I run the query I want it to return all the
details of the products, not just the product ID's. At the moment I not
getting any results at all, even though there is some test data.

Here is the SQL, although I dont understand SQL code!:
SELECT tblCustomers.[Account No], tblCustomers.[Customer Name],
tblCustomers.[Address 1], tblCustomers.[Address 2], tblCustomers.Town,
tblCustomers.County, tblCustomers.Postcode, tblPurchases.[Order ID],
tblPurchases.[Customer ID], tblPurchases.[Line 1], tblPurchases.[Quantity 1],
tblPurchases.[Line 2], tblPurchases.[Quantity 2], tblPurchases.[Line 3],
tblPurchases.[Quantity 3], tblPurchases.[Line 4], tblPurchases.[Quantity 4],
tblPurchases.[Line 5], tblPurchases.[Quantity 5], tblPurchases.[Line 6],
tblPurchases.[Quantity 6], tblPurchases.[Line 7], tblPurchases.[Quantity 7],
tblPurchases.[Line 8], tblPurchases.[Quantity 8], tblPurchases.[Line 9],
tblPurchases.[Quantity 9], tblPurchases.[Line 10], tblPurchases.[Quantity
10], tblPurchases.[Order Date/Time]
FROM tblProducts INNER JOIN (tblCustomers INNER JOIN tblPurchases ON
tblCustomers.ID = tblPurchases.[Customer ID]) ON (tblProducts.ID =
tblPurchases.[Line 1]) AND (tblProducts.ID = tblPurchases.[Line 2]) AND
(tblProducts.ID = tblPurchases.[Line 3]) AND (tblProducts.ID =
tblPurchases.[Line 4]) AND (tblProducts.ID = tblPurchases.[Line 5]) AND
(tblProducts.ID = tblPurchases.[Line 6]) AND (tblProducts.ID =
tblPurchases.[Line 7]) AND (tblProducts.ID = tblPurchases.[Line 8]) AND
(tblProducts.ID = tblPurchases.[Line 9]) AND (tblProducts.ID =
tblPurchases.[Line 10])
ORDER BY tblCustomers.[Customer Name];

I am a relative newby so please write any replies in simplistic terms!! I am
open to re-design suggestions if you think I am going about this the wrong
way.

Thanks!

 

[Tom van Stiphout]

On Sat, 18 Oct 2008 00:49:00 -0700, Not_a_notes_fan

Yes you are going about this the wrong way. Take a look at the
Northwind sample application. Order Details records should go in their
own table.

-Tom.
Microsoft Access MVP