Summarizing Many to Many Relationships

[butchm]

Hi All,

This has me stumped. Hope there is a simple solution to this that I haven't thought about yet. I have three tables: Items, Locations, and Transactions.

Items looks like this:

001 Widget

002 Gadget

Locations looks like this:

001 Warehouse

002 Branch

Transactions looks like this:

001 Widget 10pcs Warehouse

002 Widget 20pcs Warehouse

003 Gadget 30pcs Branch

004 Gadget 40pcs Branch

I would like to have a portal inside the Items file that contains a summary of the number of pieces in each branch, like this:

001 Widget

001 Warehouse 30pcs

002 Branch 0pcs

002 Gadget

001 Warehouse 0pcs

002 Branch 40pcs

Although I could do this report under the transactions file, I also need a report that looks like I need a portal:

Warehouse Branch

001 Widget 30pcs 0pcs

002 Gadget 0pcs 70pcs

Any ideas on how I can get this to work? Thanks in advance!

ButchM

[RalphL]

For starters your Transaction table needs to be fixed.

001 Widget 10pcs Warehouse

002 Widget 20pcs Warehouse

003 Gadget 30pcs Branch

004 Gadget 40pcs Branch

Should be:

001 Widget 30pcs Warehouse

002 Gadget 0pcs Warehouse

003 Widget 0pcs Branch

004 Gadget 70pcs Branch

You should only have one record for each posible combination. In a many to many relationship the primary key of the join table is the concatenation of the 2 foreign keys. Fix you data and life will get easier.

[Ender]

Your structure looks fine for getting your first report. Create a sub-summary report on a layout based on the transaction table.

Your second report is more tricky. This is kind of a spreadsheet sort of thing. But we can create this with explicit relationships for each column, counting Transactions based on each of those relationships. This works fine if you know ahead of time how many Locations you need to report on (and it's not too many.)

I've attached an example of this showing a report for three locations.

Widget.fp7.zip

[butchm]

Thanks very much for the replies! It helped a lot!

Ralph,

I fixed the join table (actually created another table) since the transactions are coming from a lot of other different sources (invoices, purchases, inventory adjustments). Now everything is working as planned. Thanks!

Ender,

Thanks for the sample file. The relationships look like a lot to digest, but I'll go through them in the next few days.