Aggregate Function Error 1

[longhair]

afternoon all,
i have the following query:

SELECT aptrxp.type, aptrxp.voucher, aptrxp.[po-num], IIf([aptrxp].[type]="V",[aptrxp].[po-num],"") AS PO, IIf([aptrxp].[type]="V",[aptrxp].[inv-num],[aptrxp].[po-num]) AS [Inv-Chk], IIf([aptrxp].[type]="V",[vch-hdr].[inv-date]+30,[aptrxp].[dist-date]) AS Due, aptrxp.[dist-date], IIf([aptrxp].[type]="V",[aptrxp].[inv-amt],[aptrxp].[amt-paid]*-1) AS amt, ([vch-hdr].[inv-amt]-Sum([aptrxp].[amt-paid])) AS [Net Due], aptrxp.[amt-paid], aptrxp.[vouch-seq], aptrxp.[check-num], aptrxp.txt, aptrxp.[inv-amt], [vch-hdr].[inv-amt]
FROM [vch-hdr] INNER JOIN aptrxp ON ([vch-hdr].voucher = aptrxp.voucher) AND ([vch-hdr].[vend-num] = aptrxp.[vend-num])
WHERE ((([vch-hdr].voucher)=[pQueryVoucher]))
GROUP BY aptrxp.type, aptrxp.voucher, aptrxp.[po-num], aptrxp.[dist-date], aptrxp.[amt-paid], aptrxp.[vouch-seq], aptrxp.[check-num], aptrxp.txt, aptrxp.[inv-amt], [vch-hdr].[inv-amt]
ORDER BY aptrxp.[vouch-seq];

when i try to run it i receive the following error:
"You tried to execute a query that does not include the specific expression 'IIF([aptrxp].[type]="V",[aptrxp].[inv-num],[aptrxp].[po-num])' as part of an aggregate function."
any suggestions as to what is missing?
thanks in advance.
regards,
longhair

 

[PHV]

The GROUP BY clause should include ALL fields not used in an aggregate function.

Anyway, having amt-paid both aggregated and grouped has no meaning for me ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[longhair]

PHV,

thank you for the reply.
if i understand correctly the aggregate functions are those sections in the SELECT portion of the SQL that make use of the IIF() function?
i understand how having amt-paid both ways does not make sense. the first amt-paid (in the aggregate function - if i understand correctly) is the net effect on th G/L account the second instance of amt-paid is the actual amount paid. so they will always be the opposite of each other (when one is positive the other is negative). they are not represented by 2 different fields in the tabel in our MRP system but they are represented as 2 different fields on the screen.

regards,
longhair

 

[PHV]

Aggregate functions: Sum, Count, Max, Min, Avg, First, Last, StDev, StDevP, Var and VarP.


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[longhair]

PHV,
thank you for the definition.
i think i'm almost there. just fiddlin with some vba now.
if the aggregate functions are the ones you describe why did it throw the error on an expression that does not contain one of them? just an access / jet quirk?
anyway, the solution i found was to make sure that i pulled all of the fields that were in any of my expression and just unchecked the show button.
regards,
longhair

 

[lespaul]

maybe this will help you understand:
The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.

The syntax for the GROUP BY clause is:

SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n;

aggregate_function can be a function such as SUM, COUNT, MIN, or MAX.

[from

http://www.techonthenet.com/sql/group_by.php

there are examples here too]

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design