CODEWITHSUNDEEP
sqlms-accessjoin
hypetech
hypetech

Reputation: 166

Join Fields if another field equals something specific

I am trying to join several fields in one table to a field in another table. They will be differentiated by the values in another field. I am unsure how this join should work and would appreciate advice. Here's examples of the tables

Bldg|EMeter|GMeter|HMeter|CMeter
Bld1_1______1______1______1_____
Bld2_2______3______328____2_____
Bld2_NULL___4______NULL___NULL


Utility|Meter|TotCost|
E_______5_____20______
C_______5_____80______
H_______5_____123______
G_______5_____654______
E_______6_____999______
E_______7_____205______
G_______6_____200______
G_______7_____40______

These tables obviously just representative of what I'm dealing with. But I'm trying to write a query to match all the meter columns from the first table to the meter column in the second table, but only if the utility value matches the first letter in the meter (EMeter goes with E, GMeter goes with G, etc.) Also the building's can have one more than one record, because they can have more than one meter of a type. I'm trying to get a cost for each building, for each type of meter, so I add all the separate meters together for each building (ie. How bldg2 has two meters for G, those will each have total costs that need to be added together. I'm trying to run a group by buildings in my query to total all those together). I'm trying to use a select query to join the meters in the first one with the meters in the second one where the utilities match, but I can't really get the syntax or design of how to do that at all. Eventually I want to have a select by query displaying something like this

Bldg|ECost|GCost|HCost|CCost
Bld1_2000__39483_20____3829_
Bld2_2034__385___2839__3010_

Upvotes: 2

Views: 1428

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726489

Since the names of columns in the first table are fixed, you can build a query that uses an outer join, and then group by with SUM(), like this:

SELECT
    m.Bldg
,   SUM(e.TotCost) AS ECost
,   SUM(g.TotCost) AS GCost
,   SUM(h.TotCost) AS HCost
,   SUM(c.TotCost) AS CCost
FROM tblBldgMeters  m
LEFT OUTER JOIN qryMtrHistory e ON e.Utility='E' AND m.EMeter=e.Meter
LEFT OUTER JOIN qryMtrHistory g ON g.Utility='G' AND m.GMeter=g.Meter
LEFT OUTER JOIN qryMtrHistory h ON h.Utility='H' AND m.HMeter=h.Meter
LEFT OUTER JOIN qryMtrHistory c ON c.Utility='C' AND m.CMeter=c.Meter
GROUP BY m.Bldg

It is saying JOIN expression not supported

This is a problem with MS-Access SQL engine. You can rewrite this into a different query:

SELECT
    m.Bldg
,   SUM(e.TotCost * SWITCH(e.Utility='E', 1, true, 0)) AS ECost
,   SUM(g.TotCost * SWITCH(g.Utility='G', 1, true, 0)) AS GCost
,   SUM(h.TotCost * SWITCH(h.Utility='H', 1, true, 0)) AS HCost
,   SUM(c.TotCost * SWITCH(c.Utility='C', 1, true, 0)) AS CCost
FROM tblBldgMeters  m
LEFT OUTER JOIN qryMtrHistory e ON AND m.EMeter=e.Meter
LEFT OUTER JOIN qryMtrHistory g ON AND m.GMeter=g.Meter
LEFT OUTER JOIN qryMtrHistory h ON AND m.HMeter=h.Meter
LEFT OUTER JOIN qryMtrHistory c ON AND m.CMeter=c.Meter
GROUP BY m.Bldg

This moves the condition from the JOIN into a SWITCH.

Upvotes: 1

Related Questions