Terrible db design to work with, but...

[jamesfreddyc]

Can someone help me understand this relationship between 2 tables and how to
build a Query that will work?

TIA...

Apperantly, this seems like a "workaround" to a spatial relationship between
IMPID's and CIPAREAS, but there is no spatial data for which to determine
this and must be handled by attribute relationships. Anyway, tblM holds 3
possible "CIP areas" that a particular row could be part of (IMPID1, IMPID2,
IMPID3). IMP.FEE is derived by tblCIP.IMPCHARGE for each of the tblM.IPID1 +
tblM.IPID2 + tblM.IPID3, for each row.

tblM tblCIP
IMPID1 IMPID
IMPID2 IMPAREANAME
IMPID3 IMPCHARGE
FEE

*tblM.IMPID1 Rel To tblCIP.IMPID
*tblM.IMPID2 Rel To tblCIP.IMPID
*tblM.IMPID3 Rel To tblCIP.IMPID


tblCIP
IMPID IMPNAME IMPCHARGE
0 NONE 0.00
1 AREA1 12.69
2 AREA2 1.99
3 AREA3 100.78
4 AREA4 67.33


So I need this result:

tblM
IMPID IMPID2 IMPID3 FEE
1 0 4 80.02
0 1 1 25.38
2 1 0 14.68
3 0 0 100.78

 

[Michel Walsh]

SELECT impid1, impid2, impid3, Nz(v1.fee,0)+Nz(v2.fee,0)+Nz(v3.fee,0) AS
totalFee
FROM (( tblM LEFT JOIN tblCIP AS v1 ON tblM.impid1=v1.impd )
LEFT JOIN tblCIP AS v2 ON tblM.ipid2=v2.impd)
LEFT JOIN tblCIP AS v3 ON tblM.ipid3=v3.impd



Basically, each impid field from table M tries to find its match in table
CIP over the impd value, and pump matching fee and in the end, we sum those
fee.




Vanderghast, Access MVP

 

[jamesfreddyc]

Yes it does. And you are awesome.

The lesson is greatly appreciated.

j