Customers / Invoices help

[DanielUK]

Hi,

I enclose a copy of the relationships my simple database is using. I'm trying to work out how to put on my customers form "Customer X bought Y amount of Product Z" - if that makes sense?

In Access, I would simply run a query: "SELECT * from customers, invoice_lineitems WHERE customers.id = 1 AND invoice_lineitems.CustomerID = 1 AND invoice_lineitems.prodid = 1" - or similar.

Help!

Many thanks,

Dan.

[Fenton]

Do you only want to see 1 product at a time? And, if so, it would only make sense to choose from products that the customer had bought, instead of all products (this would require a simple relationally filtered value list).

Or do you want to see all products that the customer purchased, in a portal? This would give the most comprehensive view. But it might need a fair amount of layout space, depending on how many different products were purchased.

Because you'd want to see multiple purchases of the same product only once in the portal (though with the full count of that product for that customer), you'd want to show only the first instance (mark and/or filter).

It's all doable. But the optimal view above is not so simple. An easier method would be to go to their product sales records, via Go To Related Record (in a new window). Then sort by ProductID. Enter Preview mode. View the quantity in a Summary field, in a Subsummary part (break on ProductID). Buttons don't work on a Preview mode window, but a "Continue" button could close the window.

[David Kachel]

DanielUK,

Of course there are exceptions to every rule, but in the case of any kind of report, always do it in the farthest table, in this case the orders_lineitems table and you should have no problem. Put another way, if the information you want to report on is seen through a portal, you are trying to create your report in the wrong table.