Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009

    Unanswered: Access 2000 query last and second last

    Hi all,
    I am having trouble with an access query

    in a nut shell, I am trying to get the last record and the second last record from a table after it has already been refined by a location.

    up to 10 records per day per location recorded with a primary key
    autonumber: statsID,
    the date: SDate,
    and Location: LocationID

    trying to get a query to get the second last record for a particular location.

    Morning and afternoon they record how much fuel they have left in separate fields, lets say FuelAM and FuelPM, easy to see what the usage is, but what I am after is how much is used overnight, and, how much people use on the weekend where it is not recorded. Any thoughts?

    In regards to the weekend, the date isn't the best thing to do it off as there are long weekends, also need the over night thing.

    Any help would be appreciated and I hope I have given enough info to go off.

  2. #2
    Join Date
    May 2005
    Welcome to the forums

    Would this be of help to you?
    Me.Geek = True

  3. #3
    Join Date
    Jun 2009
    Quote Originally Posted by nckdryr
    Welcome to the forums

    Would this be of help to you?
    Cheers, that will be useful for other things that I am doing and have done, first method.

    I ended up realizing that I couldn't do it as straight forward as I wanted so ended up making a form with 2 sub forms based on the same query, record last, and record last then previous. This actually works better for me as i can go back and see the previous records right back to the beginning.

    Now I have what I originally set out to do, though in a different way, and it's more flexible.

    I'll hit you with a better question on my third post if i can.


  4. #4
    Join Date
    Jun 2009
    Hit with the same problem again though on a report.
    Figured out how to get the last record and then the second last record by making queries based on queries.

    make a query to get the data I want, then show the top 2 results limited by one station
    new query to show the first query and arrange the date by descending and limit the results to 1 showing fuel reading at the end of the day for one station
    new query to show the first query and arrange the date by ascending and limit the results to 1 showing fuel reading at the start of the day for the same station

    so now i have 2 queries that display the data that I need from 2 different dates, showing the ending fuel from the previous day, and the starting fuel from the current day for the station that I need.

    The question is now, how to do do the totals for them on a report? and what is worse than that, how do i do it for 11 stations? 22 queries based on 11 queries based on the 2 last recorded records for that station makes 22 sub reports on a main report. Can anyone say overload?

    @nckdryr I can't see how i can apply the microsoft solution to this unfortunately so I am up for some new ideas, hopefully that don't involve making 22 sub reports.

  5. #5
    Join Date
    Dec 2004
    Madison, WI
    Not sure I fully understand (without seeing how you specifically designed the queries) but I'll often create a Make-Table query to make a temp table (which I then use to link back to my main table in another query.) What I'll usually do is find some type of key field(s) to use in the Make-Table query whereby I can link that/those field back to my table to get the "set" of records I want for the query report (ie. it may be a join on 2 fields to the main table.) For example, it might be an ID type field (StatsID?) or a combination of other fields. It could also involve making the first query a Make-Table query and another query to append more data to the temp table. The goal basically being a temp table which has the key fields to link back to my main table.

    Hence, my temp table might look like this:
    StatsID SomeOtherFieldIdentifyingtheGroupOfRecords(ie. SDate or LocationID or both)
    23 1/5/2009
    24 2/4/2009
    26 5/3/2009

    Then I'd link the temp table to my main data table, linking the StatsID and/or SDate/LocationID to get the rest of the info or sum totals.

    When I first read that you wanted to get the 2nd to last record (if I interpreted that correctly), my first thought was to write some vba code to open a recordset, go to the last record and then back 1 record. But I got a tad confused when you said you did it with queries. Usually trying to find the 2nd to last record in a dataset of many records using a query is open to problems (although you could write 1 query to get the last record (see last line in this post), then design an unmatched type of query against that query and then perhaps a query to get the last record from the unmatched query). Getting totals could then be done either via another query or summing the values in the report's footer. Assuming the query is setup correctly, I think it would work (although I again, usually make a temp table.) Not sure how it would work for your situation.

    Perhaps you could share with us your query syntax to derive the 2nd to last record which will help determine the answer for your last post.

    You definitely don't want to design 22+ queries and sub reports though. That's extremely inefficient and probably a high maintenance method. I'm also not sure I'd use the autonumber and criteria such as "Like *0" to find every record that's 10, 20, 30, etc.. That again, might be open to problems when records might get deleted or returning wrong records. Instead, I might use the summing feature in the query (the funny look E symbol - in the middle top menu) and then set the key column to "Last" or "Max" and use the method described above.
    Last edited by pkstormy; 09-07-09 at 07:26.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Jun 2009
    I hope the below code is readable
    I didn't make the sql for this either, I used the query window in access for this as its about the only way that my queries work.
    Main query to get the last 2 results which include all fields needed for the other 2 queries, UPL Start, ULP End, Deisel Start, Diesel End

    SELECT TOP 2 tbl_StationStats.StationStatsID, tbl_StationStats.StationID, tbl_StationStats.Date, tbl_StationStats.ULPLeftLitresStart, tbl_StationStats.DeiselLeftLitresStart, tbl_StationStats.ULPLeftLitresEnd, tbl_StationStats.DeiselLeftLitresEnd
    FROM tbl_StationStats
    WHERE (((tbl_StationStats.StationID)=2))
    ORDER BY tbl_StationStats.Date DESC;
    query based on the first query to get the last result out of the 2 results the initial query returns with ULP Start and Deisel Start

    SELECT TOP 1 qry_FuelUsageStation02.StationStatsID, qry_FuelUsageStation02.StationID, qry_FuelUsageStation02.Date, qry_FuelUsageStation02.ULPLeftLitresStart, qry_FuelUsageStation02.DeiselLeftLitresStart
    FROM qry_FuelUsageStation02
    ORDER BY qry_FuelUsageStation02.Date DESC;
    last query to get the second last result, well actually its the first result from the first query that only has 2 results using ULP End and Deisel End.
    on the reports the start value is taken from the end value to show what went missing in the night \ weekend.

    SELECT TOP 1 qry_FuelUsageStation02.StationStatsID, qry_FuelUsageStation02.StationID, qry_FuelUsageStation02.Date, qry_FuelUsageStation02.ULPLeftLitresEnd, qry_FuelUsageStation02.DeiselLeftLitresEnd
    FROM qry_FuelUsageStation02
    ORDER BY qry_FuelUsageStation02.Date;
    Last edited by niak32; 09-07-09 at 07:20.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts