Query giving crazy results

[studlength]

Hi to all. I've got a db with 3 tables.

tblCustomers- primary is CustomerID (auto #) one-to-many with
tblOrderskey- primary is OrderID (auto #), foreign is CustomerID
tblPayments- primary is PaymentID (auto #), foreign is CustomerID

There is a one-to-many relationship from tblCustomers to tblOrders as
well as a one-to-many relationship from tblCustomers to tblPayments.

I am trying to create a query to return all orders and all payments
made by all customers (not totals, I need each individual entry). When
I attempt this, I get all possible combinations of the numbers,
cartesian results I think. Please, someone tell me where I am going
wrong.
Are my tables set up properly? I want payments to be associated with
the customer, not an individual order. But I am wondering if using
customer number as the foreign key for both tblOrders and tblPayments
is correct.
I have played with inner joins, left joins, right joins, etc. on the
query design view but can't get it right. What type of joins should I
be using for my query?
I hope I have provided enough info, but if not, I would be happy to
provide more. Thanks.

 

[Jeff Boyce]

The way to get a Cartesian product is to pull all the tables into the query,
but NOT join them on their common fields.

The way to avoid this is to make sure you've joined the tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[E W]

Hi, you need a cascading effect where orders contain the customerid and
payments contain the orderid. Payments can contain customerid as well but
its redundant as the order table already has the customerid. Hope this helps

 

[studlength]

Hi, you need a cascading effect where orders contain the customerid and
payments contain the orderid. Payments can contain customerid as well but
its redundant as the order table already has the customerid. Hope this helps

Thanks for your fast response. I was trying to link payments to
customers instead of payments to particular orders. Our customers are
likely to send one payment for one order, one payment for many orders,
or many payments for one order, so I was trying to avoid the confusion
of a many-to-many relationship. If I follow your advice wont I be
linking payments to orders?