Prev: unable to undo delete on form that uses ADO recordset
Next: ADO or DAO? Which one more readable?
From: Hans Up on 14 Jul 2010 01:15
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?

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

http://msdn.microsoft.com/en-us/library/bb177902%28office.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.
From: Salad on 14 Jul 2010 01:02
Hans Up wrote:

> 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?
>
>
> Here is what Microsoft says about the First and Last functions:
>
> http://msdn.microsoft.com/en-us/library/bb177902%28office.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.

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.
From: Rich P on 14 Jul 2010 11:36
Hi Salad,

>
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 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 ***
From: John Spencer on 15 Jul 2010 08:50
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:
> Hans Up wrote:
>
>> 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?
>>
>>
>> Here is what Microsoft says about the First and Last functions:
>>
>> http://msdn.microsoft.com/en-us/library/bb177902%28office.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.
>
> 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.
First  |  Prev  | 
Pages: 1 2
Prev: unable to undo delete on form that uses ADO recordset
Next: ADO or DAO? Which one more readable?