From: Salad on
I have a totals query that is giving me incorrect results. The query is

SELECT ForeignID, Last(StatusID) AS LastStatus, Last(StartTime) AS
LastStart, Last(EndTime) AS LastEnd, Last(CounterId) AS LastCounter
FROM BuildDeviceDetail
GROUP BY ForeignID;

CounterID is the autonumber field. ForeignID the detailID.

The counterID field might be 401, 402, 403, 404. Running the query the
LastCounter value will be 403. The LastStatus, LastStart, and LastEnd
are for the 403 record.

This is the second time this problem has occured in the past week. To
correct, I've gone to the backend, compact/repaired it, and the query
works correctly.

I am running the application using A2003 with SP3. The other users are
all using A2007. I don't have A2007 on the computer I've been assigned.

Am I using the Last() function incorrectly?

If so, since it works most of the time I am wondering if some folks are
fully up-to-date on the service releases for A2007 and others may not be
fully up-to-date and that has created corruption.

Any advice would be appreciated.




From: Rich P on
>
SELECT ForeignID, Last(StatusID) AS LastStatus, Last(StartTime) AS
LastStart, Last(EndTime) AS LastEnd, Last(CounterId) AS LastCounter FROM
BuildDeviceDetail
GROUP BY ForeignID;
<

Greetings,

Here is my interpretation of the query above:

Select ForeignID, 'constVal' as LastStatus, 'constVal' as LastStart,
'constVal' As LastEnd, 'constVal' As LastCounter From BuildDeviceDetail
Group By ForeignID

For every field that has a "Last" function - you will always get the
same value for that field. I say to try setting these values to some
Vars like

Dim LastStatus As String (whatever)
LastStatus = DLookUp("StatusID", "BuildDeviceDetail", "StatusID =
(select Last(StatusID) from BuilDeviceDetail)")
...


Now rewrite the query as
Select ForeignID, '" & LastStatus & "' As LastStatus, '" & ... & "', ...
From BuilDeviceDetail Group By ForeignID


Just a thought - but honestly, the query - at least as it is described
in your post - is not very comprehendible what it is supposed to return.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
From: paii, Ron on

"Salad" <salad(a)oilandvinegar.com> wrote in message
news:epadnXB-b-mPU6HRnZ2dnUVZ_rWdnZ2d(a)earthlink.com...
> I have a totals query that is giving me incorrect results. The query is
>
> SELECT ForeignID, Last(StatusID) AS LastStatus, Last(StartTime) AS
> LastStart, Last(EndTime) AS LastEnd, Last(CounterId) AS LastCounter
> FROM BuildDeviceDetail
> GROUP BY ForeignID;
>
> CounterID is the autonumber field. ForeignID the detailID.
>
> The counterID field might be 401, 402, 403, 404. Running the query the
> LastCounter value will be 403. The LastStatus, LastStart, and LastEnd
> are for the 403 record.
>
>
I'm guessing you are looking for information from the last StatusID for each
ForeignID
If so, query the last StatusID then use that to get the information

SELECT BuildDeviceDetail.ForeignID, Last(BuildDeviceDetail.StatusID) AS
LastStatus
FROM BuildDeviceDetail
GROUP BY BuildDeviceDetail.ForeignID;

Name it qryLastID

SELECT BuildDeviceDetail.ForeignID, BuildDeviceDetail.StatusID,
BuildDeviceDetail.StartTime, BuildDeviceDetail.LastEnd,
BuildDeviceDetail.CounterId
FROM BuildDeviceDetail INNER JOIN qryLastID ON (BuildDeviceDetail.StatusID=
qryLastID.LastStatus) AND (BuildDeviceDetail.ForeignID=
qryLastID.ForeignID);



From: Bob Barrows on
Salad wrote:
> I have a totals query that is giving me incorrect results. The query
> is
> SELECT ForeignID, Last(StatusID) AS LastStatus, Last(StartTime) AS
> LastStart, Last(EndTime) AS LastEnd, Last(CounterId) AS LastCounter
> FROM BuildDeviceDetail
> GROUP BY ForeignID;
>
> CounterID is the autonumber field. ForeignID the detailID.
>
> The counterID field might be 401, 402, 403, 404. Running the query
> the LastCounter value will be 403. The LastStatus, LastStart, and
> LastEnd are for the 403 record.
>
> This is the second time this problem has occured in the past week. To
> correct, I've gone to the backend, compact/repaired it, and the query
> works correctly.
>
> I am running the application using A2003 with SP3. The other users
> are all using A2007. I don't have A2007 on the computer I've been
> assigned.
> Am I using the Last() function incorrectly?
>
> If so, since it works most of the time I am wondering if some folks
> are fully up-to-date on the service releases for A2007 and others may
> not be fully up-to-date and that has created corruption.
>
> Any advice would be appreciated.

It sounds like you want the data from the record containing the maximum
counterID for each ForeignID. Last() is not guaranteed to get you that. If
you want the record with the maximum counterID, then you have to explicitly
ask for it:

select d.* from BuildDeviceDetail as d join (
select ForeignID,max(counterid) as MaxCounter from BuildDeviceDetail
group by ForeignID) as q on
d.ForeignID=q.ForeignID and counterID=MaxCounter

As has been repeatedly stated in this newsgroup, it is risky to depend on
Last() to identify what you expect to be the "last" record in a table. It is
up to the database engine to decide the order that records are stored, and
it might not choose the order you expect it to.


From: Salad on
Rich P wrote:

> SELECT ForeignID, Last(StatusID) AS LastStatus, Last(StartTime) AS
> LastStart, Last(EndTime) AS LastEnd, Last(CounterId) AS LastCounter FROM
> BuildDeviceDetail
> GROUP BY ForeignID;
> <
>
> Greetings,
>
> Here is my interpretation of the query above:
>
> Select ForeignID, 'constVal' as LastStatus, 'constVal' as LastStart,
> 'constVal' As LastEnd, 'constVal' As LastCounter From BuildDeviceDetail
> Group By ForeignID
>
> For every field that has a "Last" function - you will always get the
> same value for that field. I say to try setting these values to some
> Vars like
>
> Dim LastStatus As String (whatever)
> LastStatus = DLookUp("StatusID", "BuildDeviceDetail", "StatusID =
> (select Last(StatusID) from BuilDeviceDetail)")
> ..
>
>
> Now rewrite the query as
> Select ForeignID, '" & LastStatus & "' As LastStatus, '" & ... & "', ...
> From BuilDeviceDetail Group By ForeignID
>
>
> Just a thought - but honestly, the query - at least as it is described
> in your post - is not very comprehendible what it is supposed to return.
>
> Rich
>
> *** Sent via Developersdex http://www.developersdex.com ***

Hi Rich:

For an example, let's say an order has 4 items on it. I want to group
on the order number and get the last item of that order. In this case,
the values for order item #4.

I added a Order By on the OrderDetailID to ensure it orders correctly.

Not sure it that does the trick or not and fix the problem. Hopefully
it will. I did a test on my computer. I created a simple table with a
few fields, sorted on ANY field ascending or descending order, save the
table becuase I'm prompted, and the Last() finds the correct last record
no matter how I saved the table's sort order. So that's why I say hope.
Because the problem is with the A2007 computers. Without A2007 on my
computer it's kinda hard to test.