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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

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