CODEWITHSUNDEEP
sqlms-access
hypetech
hypetech

Reputation: 166

Using SQL alias statement in SELECT query

I've been trying to get this access query to work, I'm learning to write SQL so I wanted to try using alias's for my table to make it easier to use but for some reason I keep getting errors. Here is my original SQL query

SELECT qryCurrentMonthMtrHis.Utility
    ,qryCurrentMonthMtrHis.MeterID
    ,qryLastYearAvgMtrHis.AvgOfUnits AS LYRAvgOfUnits
    ,qryLastYearMtrHis.Units AS LYRUnits
    ,qryLastMonthMtrHis.Units AS PrevMonUnits
    ,qryCurrentMonthMtrHis.Units AS CurrentUnits
    ,qryLastYearAvgMtrHis.AvgOfTotCost
    ,qryLastYearMtrHis.TotCost AS LYRTotCost
    ,qryLastMonthMtrHis.TotCost AS PrevMonTotCost
    ,qryCurrentMonthMtrHis.TotCost AS CurrentTotCost
FROM qryLastYearMtrHis
RIGHT JOIN (
    qryLastYearAvgMtrHis RIGHT JOIN (
        qryLastMonthMtrHis RIGHT JOIN qryCurrentMonthMtrHis ON (qryLastMonthMtrHis.Utility = qryCurrentMonthMtrHis.Utility)
            AND (qryLastMonthMtrHis.MeterID = qryCurrentMonthMtrHis.MeterID)
        ) ON (qryLastYearAvgMtrHis.Utility = qryCurrentMonthMtrHis.Utility)
        AND (qryLastYearAvgMtrHis.MeterID = qryCurrentMonthMtrHis.MeterID)
    ) ON (qryLastYearMtrHis.Utility = qryCurrentMonthMtrHis.Utility)
    AND (qryLastYearMtrHis.MeterID = qryCurrentMonthMtrHis.MeterID);

And here is the one I'm trying to do with alias

    SELECT cm.Utility
    ,cm.MeterID
    ,lyra.AvgOfUnits AS LYRAvgOfUnits
    ,lyr.Units AS LYRUnits
    ,pm.Units AS PrevMonUnits
    ,cm.Units AS CurrentUnits
    ,lyra.AvgOfTotCost
    ,lyr.TotCost AS LYRTotCost
    ,pm.TotCost AS PrevMonTotCost
    ,cm.TotCost AS CurrentTotCost
FROM qrylastYearMtrHis lyr
RIGHT JOIN (
    qryLastYearAvgMtrHis lyra RIGHT JOIN (
        qryLastMonthMtrHis pm RIGHT JOIN qryCurrentMonthMtrHis cm ON (lyr.Utility = cm.Utility)
            AND (pm.MeterID = cm.MeterID)
        ) ON (lyra.Utility = cm.Utility)
        AND (lyra.MeterID = cm.MeterID)
    ) ON (lyr.Utility = cm.Utility)
    AND (lyr.MeterID = cm.MeterID);

When I try to run the second one though, it says "syntax error in join operation" and highlights the "lyr" in lyra.AvgOfUnits. From what I've read online it should be working, so I was wondering if anyone could offer any insight?

Upvotes: 2

Views: 2140

Answers (2)

dnoeth
dnoeth

Reputation: 60502

You use a wrong alias in a join:

RIGHT JOIN (
    qryLastYearAvgMtrHis lyra RIGHT JOIN (
        qryLastMonthMtrHis pm RIGHT JOIN qryCurrentMonthMtrHis cm ON (lyr.Utility = cm.Utility)

Must be pm.Utilityinstead of lyr.Utility.

Didn't you use a search&replace?

Upvotes: 2

Matt
Matt

Reputation: 15061

SELECT cm.Utility, cm.MeterID, lyra.AvgOfUnits AS LYRAvgOfUnits, lyr.Units AS LYRUnits,
       pm.Units AS PrevMonUnits, cm.Units AS CurrentUnits, lyra.AvgOfTotCost, 
       lyr.TotCost AS LYRTotCost, pm.TotCost AS PrevMonTotCost, 
       cm.TotCost AS CurrentTotCost
FROM qryLastYearMtrHis lyr 
RIGHT JOIN (qryLastYearAvgMtrHis lyra 
    RIGHT JOIN (qryLastMonthMtrHis pm 
        RIGHT JOIN qryCurrentMonthMtrHis cm ON (pm.Utility = cm.Utility) 
                                            AND (pm.MeterID = cm.MeterID))
        ON (lyra.Utility = cm.Utility) AND (lyra.MeterID = cm.MeterID)) 
ON (lyr.Utility = cm.Utility) AND (lyr.MeterID = cm.MeterID);

Upvotes: 1

Related Questions