Should I use Select First? (1 Viewer)

[DocNice]

I'm having trouble with a select first query. I'm getting an error saying whichever item follows my select first statement (in this example TariffID) "is not included in the aggregate function." See example. Background is below.

SELECT First (SchedulePage) as FirstPage, TariffID, TariffCPUCID, ScheduleID, [C&E]
FROM Tariffs
WHERE (((FirstPage) Is Not Null) AND ((ScheduleID)=[Forms]!...))
ORDER BY FirstPage, Tariffs.[C&E] DESC;


Background:

I have a table where records are assigned a Page. I need a query to pull up only one Page per page number. (ie - there are ten Page 1, and five Page 2, but I only need one of each). Preferably, this would pull up the one that is Current and Effective ([C&E], yes I know it's bad naming, oops), but the purpose of this query is to help when something gets messed up, so I can't rely on C&E being correct, and thus need to bring up the page, even if there isn't one marked C&E.

Long story short, I thought that by sorting by C&E, I could pull up only the first record. Similarly, I tried using Min to pull up the lowest C&E, though this may not work if there isn't one marked C&E.

 

[Pat Hartman]

First() and Last() refer to the position of a record in a recordset. In order to use them, the recordset needs to be sorted on something that makes the data you want show up first or last. Since you want to get all the data from a single record, use the First() aggregate on EVERY field except the one you want to group by.

SELECT First(SchedulePage) as FirstPage, First(TariffID) As FirstTariff, First(TariffCPUCID) As FirstTariffCPU, ScheduleID, First([C&E]) As FirstCE
FROM Tariffs
Group By ScheduleID
WHERE (((FirstPage) Is Not Null) AND ((ScheduleID)=[Forms]!...))
ORDER BY FirstPage, Tariffs.[C&E] DESC;