Jump to content
Sign in to follow this  
AnnieBC

Fixing existing relationship

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by Guest

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Who Viewed the Topic

    1 member has viewed this topic:
    ayescas 
×

Important Information

By using this site, you agree to our Terms of Use.