SQL First() aggregate function not working as expected

[Seth Schrock]

I have two queries. The first manipulates the data and sorts it. The second query is based on the first and groups one field and displays another. I'm using the First() function on the field that is displayed, but it is pulling the second one (also the last one, as there are only two in that group). Originally, I thought I just needed to sort it on that field in order to make sure that the order was correct, but that didn't fix it. I originally had several fields, but I have eliminated all but the one and it still has the problem.

Here is the SQL for the trimmed down query:

SELECT First(qryFixedDates.pol_idx) AS Firstpol_idx
FROM qryFixedDates
GROUP BY qryFixedDates.Policy
ORDER BY First(qryFixedDates.pol_idx);

And here is the SQL for the whole query:

SELECT qryFixedDates.Policy
, First(qryFixedDates.pol_idx) AS Firstpol_idx
, First(qryFixedDates.brch) AS Firstbrch
, Max(qryFixedDates.effective) AS Maxeffective
, Max(qryFixedDates.expired) AS Maxexpired
, Min(qryFixedDates.datewritten) AS Mindatewritten
, Sum(qryFixedDates.prem) AS TotalPrem
, First(qryFixedDates.prem) AS FirstPrem
, First(qryFixedDates.bco) AS FirstOfbco
, Count(qryFixedDates.Pol_idx) AS Num
FROM qryFixedDates
GROUP BY qryFixedDates.Policy
HAVING (((Max(qryFixedDates.expired))>#12/1/2015#))
ORDER BY First(qryFixedDates.pol_idx);

As you can see in the whole query, there are a few fields on which I'm using the First() function and they are all doing the same thing. I'm not sure what is wrong.

[Rabbit]

The First and Last functions in Access are not intuitive. It ignores all ordering, indexes, and primary keys. Instead it picks the first or last non-deleted row based on some sort of internal ordering.

[Seth Schrock]

So nice of Microsoft to make such handy functions, LOL. What good are they if you can't know for sure what the results will be?

Oh, well. Thanks for your answer Rabbit. I think that I'll have to use a sub-query to get the results that I want.

[NeoPa]

Hi Seth.
Sorting by the result you are trying to determine would never get you what you want anyway. You're sorting by the value already determined to be first.
You could try sorting that which provides the data for the GROUP BY in a sub-query and see if that returns the value you want. Alternatively, if it's the first ordered by that field, then wouldn't Min() be a better function to use?

In case you need First() for some reason then what I'm talking about is :

SELECT   First([pol_idx]) AS [Firstpol_idx]
FROM     (SELECT   [Policy]
                 , [pol_idx]
          FROM     [qryFixedDates]
          ORDER BY [Policy]
                 , [pol_idx]) AS [sQ]
GROUP BY [Policy]
ORDER BY [Policy]

[hvsummer]

haha, I know your feeling Seth, I've deal with that not long ago.
First and last sql completely useless if we want to get first or last of value each parent groups.

I suggest you to use ADO, use stringSQL to open recordset that have "Order by" to sort, then .Movelast or .movefirst to get the last or first record.

UDF function can be slow, but it's accuracy
just trade off.

[Seth Schrock]

@NeoPa I'm sorting on the value that I want to be first because the GROUP BY field changes the sort to the [Policy] field instead of the [pol_idx] field. Actually, in the case of the [pol_idx] field, I can use the min() function, but not on the [prem] field. I'm going to see if I can rethink how I'm getting my data and how each group relates to each other.

@hvsummer I'm already having to perform lots of UDFs on my query, so performance is already low and I can't afford to lower it anymore. I think that I'm going to try some other method for the moment.

[mbizup]

If your pol_idx field is an autonumber, perhaps you can use it in conjuction with the record date in a self join (subquery), to pull the other fields...

Something like this:

SELECT t.* 
FROM YourTableOrQuery t INNER JOIN
(SELECT MIN(IDField), MIN(TimeStampField) 
FROM YourTableOrQuery 
GROUP BY  {your grouping fields)) q
ON q.IDField = t.IDField AND q.TimeStampField = t.TimeStampField

In the SQL above, t is an alias for the table; q is an alias for a subquery based on that table. The join on ID and timestamp fields allows you to pull the rest of the fields from the records selected in the subquery (you may be able to get by with only one of those fields in the subquery too).