Help resolving multi to multi relationship

[Smacs]

I have two tables that I need to pull revenue data from and link to a
customer, but am running into a problem.

Here's what the tables look like...

TABLE_1
CID
AcctNum
ProdID
Revenue

TABLE_2
AcctNum
ProdID
Revenue

Note: each table references different ProdIDs.

So the problem I'm having is that there are multiple AcctNums per CID (not
surprising - I'd just group by CID, if that were the only thing), but ALSO
some AcctNums map to multiple CIDs. I've determined that in these cases, the
CIDs that map to the same AcctNum ARE in fact the same customer, there have
just been multiple CIDs setup for them for some reason.

Now, I need to map all the revenue to a single CID, so my sense is that I
need to:
1. Flatten all the CIDs that map to a single AcctNum while summing together
all their revenue so that none of it is lost
2. Map the revenue from the Acctnum (as well as any OTHER AcctNums that map
to any of the CIDs that have been flattened) to the single CID, without
losing OR double counting any of the revenue from TABLE_2

What I had been trying to do was something like this, but of course it's
resulting in Revenue from TABLE_2 being counted again for every AcctNum that
maps to the same CID...

SELECT TABLE_1.CID, TABLE_2.ProdID, Sum(TABLE_2.Revenue) AS Revenue
FROM TABLE_1 INNER JOIN TABLE_2 ON TABLE_1.AcctNum=TABLE_2.AcctNum
GROUP BY TABLE_1.CID, TABLE_2.ProdID;

Would really appreciate some guidance here. Thank you in advance.

 

[XPS350]

I have two tables that I need to pull revenue data from and link to a
customer, but am running into a problem.

Here's what the tables look like...

TABLE_1
CID
AcctNum
ProdID
Revenue

TABLE_2
AcctNum
ProdID
Revenue

Note: each table references different ProdIDs.

So the problem I'm having is that there are multiple AcctNums per CID (not
surprising - I'd just group by CID, if that were the only thing), but ALSO
some AcctNums map to multiple CIDs. I've determined that in these cases, the
CIDs that map to the same AcctNum ARE in fact the same customer, there have
just been multiple CIDs setup for them for some reason.

Now, I need to map all the revenue to a single CID, so my sense is that I
need to:
1. Flatten all the CIDs that map to a single AcctNum while summing together
all their revenue so that none of it is lost
2. Map the revenue from the Acctnum (as well as any OTHER AcctNums that map
to any of the CIDs that have been flattened) to the single CID, without
losing OR double counting any of the revenue from TABLE_2

What I had been trying to do was something like this, but of course it's
resulting in Revenue from TABLE_2 being counted again for every AcctNum that
maps to the same CID...

SELECT TABLE_1.CID, TABLE_2.ProdID, Sum(TABLE_2.Revenue) AS Revenue
FROM TABLE_1 INNER JOIN TABLE_2 ON TABLE_1.AcctNum=TABLE_2.AcctNum
GROUP BY TABLE_1.CID, TABLE_2.ProdID;

Would really appreciate some guidance here. Thank you in advance.

If you have a multi to multi relationship, you need te introduce one
more table. This table will contain at least two fields: the keys of
table1 and table2.

Groeten,

Peter
http://access.xps350.com

 

[Smacs]

I see...so, if I understand correctly, XPS350, I need to create a TABLE_3
that contains something like the following:

TABLE_3
UniqueID CID AcctNum
-----------------------------
1 001 00001
1 001 00002
2 002 00003
2 003 00003
3 004 00004
....

Then I can use UniqueID as the GROUP BY field. Is this correct?

Do you have any suggestions for how to do this automatically? This makes
sense conceptually, but my tables are in the hundreds of thousands of
records, so I'll need to find a way to populate those UniqueIDs in some
automated fashion.

 

[XPS350]

I see...so, if I understand correctly, XPS350, I need to create a TABLE_3
that contains something like the following:

TABLE_3
UniqueID   CID   AcctNum
-----------------------------
1              001   00001
1              001   00002
2              002   00003
2              003   00003
3              004   00004
...

Then I can use UniqueID as the GROUP BY field. Is this correct?

Do you have any suggestions for how to do this automatically? This makes
sense conceptually, but my tables are in the hundreds of thousands of
records, so I'll need to find a way to populate those UniqueIDs in some
automated fashion.

I don't understand what is in your tables at the moment. In my opinion
the only way to implement a multi to muliti relationship is by having
3 tables. You have only 2 so I can't see how you could build the right
situation from those 2 tables.

Groeten,

Peter
http://access.xps350.com