Fixing existing relationship

[AnnieBC]

I am helping out at a nonprofit and I think their database has some structural issues that I'm not sure how to fix.

The biggest issue is as follows... In their db, they are tracking product sales. There are three main tables that need to talk.

1. Orders - Includes OrderID, Order Date, Address info

2. Order Item - Includes OrderID, Product ID, and the type of item (product vs. calendar). One order may have multiple products associated of course.

3. Order Payment - Includes OrderID, and payment information. One order may include multiple payments.

So the problem is that in some of the reports, I need to show both the payment info and the type of item (many to many). How can I now create this linkage since there is already lots of data? I'm thinking I need some sort of PaymentID or something but not sure how to implement without completely starting over.

Thanks!

Annie

[bcooney]

Welcome Annie!

Would you detail the report? I can't picture a report that shows all OrderLineItems and Payments. Perhaps if you "mock up" a report...

In general, when you need to report from a parent with two child tables, you can:

1. bring child info up into the parent using List().

2. send the data into a temp report table.

[AnnieBC]

Great, this may be easier than I thought then! Here is an example of one of the reports.

It is a report showing deposits amounts, order id and product information. Normally, it is queried by deposit date range (order payments table) but the results need broken out by Type (order items table). Right now, if one order id contains both types (product and calendar), the report lumps everything into the first type.

Does that make sense?

P.S. I can also post a mock-up next time I'm in the office if that would be helpful.

Thanks!

Edited April 29, 2009 by Guest

[bcooney]

Payments aren't by type, so breaking out payments by type doesn't make sense to me. You also use the words payments and deposits (which are very different things) interchangeably. Please explain.

I see this report being based on the OrderLineItems table. Can you relate an orderlineitem to a payment. I don't think that you can...