Prev: unable to undo delete on form that uses ADO recordset
Next: ADO or DAO? Which one more readable?
From: Salad on 13 Jul 2010 16:35 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 13 Jul 2010 17:04 > 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 13 Jul 2010 17:21 "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 13 Jul 2010 17:22 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 13 Jul 2010 17:43 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.
|
Next
|
Last
Pages: 1 2 Prev: unable to undo delete on form that uses ADO recordset Next: ADO or DAO? Which one more readable? |