Aggregate and Non-Aggregate

[dr4ke]

Afternoon,



A, hopefully, easy question for a Friday afternoon!

I know you can't use an aggregate and a non-aggregate function in a Query, however is there a workaround which would allow me to do the below?

'Painting the picture'; There is a table with 4 columns. 'SanName', 'AggregateName', 'Used', 'Date'. For simplicity we'll say there are two combinations of these (SAN1 Aggregate1 & SAN1 Aggregate2), so to compare the data between the two dates I have to use the FIRST(), LAST() expressions. When I do this I can't then SELECT 'SanName' or 'AggregateName' due to the 'aggregate and non-aggregate' error.

Is there a way to use the FIRST(), LAST() and SELECT expressions in the same query? So the output would be;

SANName	AggregateName	FirstAmount	LastAmount
SAN1	Aggregate1	5000	6000
SAN1	Aggregate2	5000	6000

Any help appreciated

Dr4ke

[pbaldy]

Sure, you likely just need to add the GROUP BY clause:

SELECT SanName, AggregateName, First(Amount) As FirstAmount, Last(Amount) As LastAmount
FROM TableName
GROUP BY SanName, AggregateName

You may find first and last don't do what you expect:

http://support.microsoft.com/kb/208190

It sounds vaguely like you might be going in this direction:

http://www.baldyweb.com/LastValue.htm

[dr4ke]

Thank you! The FIRST() LAST() now does exactly what I want it to do!

I did look into the MIN() and MAX() way, but it would involve me creating 4 different queries to get the same output and then have to create another layer of queries to do Math on them. It seems as though the FIRST() and LAST() is the way to go! I just need to figure out how to implement the below; I can't seem to include a WHERE in this now due to the 'aggregate, non-aggregate' thing now. Any ideas? I have a Form, for the End User, where they select the date range they want to see the data between.

WHERE ((HighLevelCapacity.Date) Between (Forms!ReportDataRangeSelect!SelectStart) And (Forms!ReportDataRangeSelect!SelectEnd))

[pbaldy]

You should be able to do that. In design view, change Group By to Where on that field. In SQL view, make sure the date field is not in the SELECT clause or the GROUP BY clause.

[dr4ke]

Absolutley spot on!!

I was putting the WHERE after the GROUP BY! I've learnt my lesson!! There doesn't appear to be any problems with my FIRST() LAST() Query not giving the desired outcome... Am I at risk?

[pbaldy]

Excellent!

You're not at risk if you've reviewed the link and understand what they are returning, which is often not what people expect.

[dr4ke]

Thank you