Group By error message

[stephiesunny]

Hi, I keep getting the following error message from the Query below:

"You tried to execute a query that does not include the specified expression
'Trans ID' as part of an aggregate function."



SELECT PaymentNet_Transactions.[Trans ID],
Sum(PaymentNet_Transactions.[Trans Amount]) AS [SumOfTrans Amount],
PaymentNet_Transactions.[Post Date], Normalization.MERCHANT,
IIf(Normalization.DISPLAYNAME Is
Null,PaymentNet_Transactions.Merchant,Normalization.DISPLAYNAME) AS
DisplayName
FROM PaymentNet_Transactions LEFT JOIN Normalization ON
PaymentNet_Transactions.[Trans ID] = Normalization.[Trans ID]
GROUP BY IIf(Normalization.DISPLAYNAME Is
Null,PaymentNet_Transactions.Merchant,Normalization.DISPLAYNAME);

I'm trying to get summations of total dollars spent by vendor. But it sounds
like it's because I have a specific transaction ID for each line item that I
can't group the items that way. Is this what it means? And if so, how do I
group the transaction totals by vendor, but still retain the transaction ID
in some way? I was thinking perhaps that I would have to have 2 separate
queries, but I just wanted to ask first. Thanks!!

 

[KARL DEWEY]

Try this --
GROUP BY PaymentNet_Transactions.[Trans ID], IIf(Normalization.DISPLAYNAME Is
Null,PaymentNet_Transactions.Merchant,Normalization.DISPLAYNAME);

 

[John W. Vinson]

Hi, I keep getting the following error message from the Query below:

"You tried to execute a query that does not include the specified expression
'Trans ID' as part of an aggregate function."



SELECT PaymentNet_Transactions.[Trans ID],
Sum(PaymentNet_Transactions.[Trans Amount]) AS [SumOfTrans Amount],
PaymentNet_Transactions.[Post Date], Normalization.MERCHANT,
IIf(Normalization.DISPLAYNAME Is
Null,PaymentNet_Transactions.Merchant,Normalization.DISPLAYNAME) AS
DisplayName
FROM PaymentNet_Transactions LEFT JOIN Normalization ON
PaymentNet_Transactions.[Trans ID] = Normalization.[Trans ID]
GROUP BY IIf(Normalization.DISPLAYNAME Is
Null,PaymentNet_Transactions.Merchant,Normalization.DISPLAYNAME);

I'm trying to get summations of total dollars spent by vendor. But it sounds
like it's because I have a specific transaction ID for each line item that I
can't group the items that way. Is this what it means? And if so, how do I
group the transaction totals by vendor, but still retain the transaction ID
in some way? I was thinking perhaps that I would have to have 2 separate
queries, but I just wanted to ask first. Thanks!!

If there are multiple transactions - each with its own Trans ID - that you're
summing, what do you want to see!? The sum of Trans Amount doesn't pertain to
any individual Trans ID; what purpose does the trans ID serve in your query?
Why do you need it?