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.

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 ***

"Salad" <salad (AT) oilandvinegar (DOT) com> wrote

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);

Salad wrote:
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.

Rich P wrote:

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.

Hans Up wrote:

I agree with what you write. BTW, thanks for the link.

In the end I decided to create 2 queries. The first got the max id per
unit from the table. Then I created a query that joined the maxid to
the table id. It's quick and it will be accurate.

Salad wrote:
Here is what Microsoft says about the First and Last functions:

http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

"They simply return the value of a specified field in the first or last
record, respectively, of the result set returned by a query. Because
records are usually returned in no particular order (unless the query
includes an ORDER BY clause), the records returned by these functions
will be arbitrary."

So you should probably try an ORDER BY in your query.

However, I gave up on First and Last because they aren't supported in
SQL Server, and I've been trying to design Access applications so that
they may later be upsized with minimum fuss. Don't know whether that is
a concern for you. But in your case, think I would use Max(CounterID)
instead of Last.

It's interesting that compact cures your ordering problems at least
temporarily. Supposedly records are written to disk in primary key
order during compact. However, that ordering is not maintained with
table changes ... it only happens again at the next compact. I wouldn't
depend on it to enforce your desired order; use ORDER BY instead.

Hi Salad,

the order number and get the last item of that order. In this case, the
values for order item #4.
<

I was thinking that maybe you wanted to perform something like this - a
form of deduping in a sense. I have encountered issues like this before
in this NG. Jet Sql does not support this kind of operation in one
shot. You have to use the "Top" operator, a "Where Exists" clause and
then order by. I have a Tsql query that does this very thing. I have
tried to adapt it to Jet sql, but no go. And no one else has come up
with a workaround to do this type of query in one shot in Access. Here
is a sample from the Tsql:

SELECT * FROM tbl1 t1 WHERE EXISTS
(SELECT * FROM (SELECT TOP 1 * FROM tbl1 t2 WHERE t2.RecordId =
t1.RecordId Order By t2.DateFld) t3 WHERE t3.rowID = t1.rowID)

In this sample -- rowID is the unique key of the table, and RrecordID is
the field that groups the various sets of rows. It sounds like you want
to get the "Last" row for each group in your table. Your table will
need a unique key field where each row has an individual rowID (so to
speak). Then, say CustID is the field you are grouping on. You want to
get the Last OrderNo for each customer. That would look something like
this:

SELECT * FROM tbl1 t1 WHERE EXISTS
(SELECT * FROM (SELECT TOP 1 * FROM tbl1 t2 WHERE t2.CustID = t1.CustId
Order By t2.OrderNo DESC) t3 WHERE t3.rowID = t1.rowID)

You will order the OrderNo field DESCENDING so that the Top operator
will consistently select the "Last" OrderNo. And remember also -- this
is Tsql -- only works with sql server. The workaround in Access is that
you will have to use a DAO loop and loop through each group of
CustIDs's. This would be 2 queries. The first query provides a list of
distinct CustID's to loop through. The 2nd query will be the query
where you pick the "Last OrderNo"

'1st query:

Set RS = CurrentDB.OpenRecordset("Select Distinct CustID From tbl1 Order
By CustID")

Do While Not RS.EOF
'2nd query:
Set RS2 = CurrentDB.OpenRecordset(Select Top 1 * From tbl1 Where
CustID = " & RS(0) & " Order By OrderNo DESC"}

'now do something with RS2 values -- store in a temp tbl

RS.MoveNext
Loop


Rich

*** Sent via Developersdex http://www.developersdex.com ***

A one query solution (results are not updateable).

SELECT BuildDeviceDetail.ForeignID
, StatusID
, StartTime
, EndTime
, CounterId
FROM BuildDeviceDetail INNER JOIN
(SELECT ForeignID, Max(CounterID) as LastID
FROM BuildDeviceDetail
GROUP BY ForeignID) as qLast
ON BuildDeviceDetail.ForeignID = qLastForeignID
AND BuildDeviceDetail.CounterID = qLast.LastID

An alternative (but slower) query that is updateable would be

SELECT ForeignID
, StatusID
, StartTime
, EndTime
, CounterId
FROM BuildDeviceDetail
WHERE CounterID =
(SELECT Max(CounterID) as LastID
FROM BuildDeviceDetail as Temp
WHERE Temp.ForeignID = BuildDeviceDetail.ForeignID)

Hope this helps.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Salad wrote: