Query to get the next to last record?

[TylerTand]

I have a table that tracks the purchases of my customers in a table. The Table has the customers id number, date of purchase, and Item. I need to be able to know what they bought the NEXT to last time they came to my store. I can use the date and the max function to get the last purchase but not the next to last purchase. I have over 600 people and they each have multiple records. I have VBA experience but was hoping I could use a regular query to do this. If I could query for the last record (descending order) by date and say move next , but I don't know how ot capture the second record . Any ideas would be appreciated.

 

[raskew]

Hi -

Take a look at this: http://www.access-programmers.co.uk/forums/showthread.php?t=139748

HTH - Bob

 

[dallr]

You can do this using a query alone and it would be much faster than anything VBA can put together.

First You need to create a query that gives you the last two orders that a customer purchases.

SELECT Table1.ID, Table1.CustomerIDNumber, Table1.DateOfPurchase, Table1.Item
FROM Table1
WHERE Table1.DateofPurchase IN (SELECT TOP 2  T.DateOfPurchase FROM Table1 T WHERE T.CustomerIDNumber = Table1.CustomerIdNumber ORDER BY T.DateofPurchase Desc);

Then you need to use the above query and remove the last record by filtering out the max record.

SELECT qry_LastTwoOrders.ID, qry_LastTwoOrders.CustomerIDNumber, qry_LastTwoOrders.DateOfPurchase, qry_LastTwoOrders.Item
FROM qry_LastTwoOrders
WHERE qry_LastTwoOrders.DateOfPurchase NOT IN (SELECT MAX(Q1.DateOfPurchase) FROM qry_LastTwoOrders Q1 WHERE Q1.CustomerIDNumber = qry_LastTwoOrders.CustomerIDNumber);

See attached sample database in 2000 file format.

Dallr

 

[dallr]

Please note that if the customer only has one purchase it will not show in the query above, since they do not have a second to last purchase to reference.

If however you want to show the only (one purchase) purchase a customer made along with the second to last purchase of customer who made multiple purchases then replace the second query I gave above with the following. I hope I have explained this clearly enough.

SELECT qry_LastTwoOrders.ID, qry_LastTwoOrders.CustomerIDNumber, qry_LastTwoOrders.DateOfPurchase, qry_LastTwoOrders.Item
FROM qry_LastTwoOrders
WHERE (((qry_LastTwoOrders.DateOfPurchase)  In (SELECT TOP 1 Q1.DateOfPurchase FROM qry_LastTwoOrders Q1 WHERE Q1.CustomerIDNumber = qry_LastTwoOrders.CustomerIDNumber ORDER BY Q1.DateOFPurchase asc)));

Dallr

 

[raskew]

Hi -

That works well!

...and it would be much faster than anything VBA can put together.

Granted, provided you don't count the time spent setting up the queries to meet the specific situation. The referenced VBA solution essentially sets up and runs the queries, based on existing tables and or queries.

Best wishes - Bob

 

[dallr]

I ment no disrespect when I say that it would run faster than VBA. (i.e the solution in the link). I was just stating a well known fact.

It took me less than 5 minutes to create the query that i suggested as a possible solution. I said that to say, the more an indiviudal understands a set based approach using SQL (Query Writing) it should not be a problem to create or recreate any query.

The main reason I highlighted the performance aspect is becuase when you are speaking about queries one must always look for solutions that optimize query performance. As we all know, as the size of the data increases the performance of the query can suffer.

dallr

 

[raskew]

Hi -

The main reason I highlighted the performance aspect is because when you are speaking about queries one must always look for solutions that optimize query performance. As we all know, as the size of the data increases the performance of the query can suffer.

If you'll look again you'll see that the referenced solution use less than 20 lines of working VBA code to structure and run queries. It takes 15 seconds or less to set up the call and the whole thing, code and queries, processes in about 750 milliseconds.

The difference between the individualized solution which, as said earlier-- runs marvelously--and a generic VBA + query solution, is the ability to apply the generic solution to a variety of situations without reconstructing queries or code. The resulting queries are similar and, under normal circumstances, the VBA/query approach wouldn't result in any more DB bloat than the individualized solution.

Bob

 

[dallr]

I appreicate your feedback and comments regarding the solution you provided.

However, you are justifying the use of your solution on how easy it is to setup and reuse. While that is might be a valid point in another context, it is not advantageous in this case.

When dealing with queries one must ALLWAYS construct them in such a way that they perform the fastest. One might even write them in various ways and test them to see which one performs the best. All this is done to ensure the data is returned as fast as possible.

Even if you say it takes 15 seconds to implement. How is that beneficial to the client/customer who is going to use the database on a day in and day out basis. 15 seconds benefit for the developer but the customer has to wait a few seconds longer each time they run the query because it could have been better optimized. And as i pointed out earlier this timeframe could get worst and worst as the data grows.

processes in about 750 milliseconds.

Processes in about 750 millesconds. Where are you getting this information from? You cannot say how long this will take becuase it will vary from recordset to recordset along with other factors (eg. how many attributes are being returned, etc).

The resulting queries are similar and, under normal circumstances, the VBA/query approach wouldn't result in any more DB bloat than the individualized solution.

What bloat are you talking about here? Select queries will not result in any database bloat?. I have also examined the VBA solution in the link and it will not cause any bloating either!

In short, you are trying to justify a solution when there is a better option that can be adopted. I close by saying, the original poster (OP) has two solutions now, and the choice of which solution they want to go with is up to them. Once they have a FULL understanding of the advantages and disadvantages of each.

Dallr

Dallr

 

[raskew]

Hi -

Timing a process:

Option Compare Database
Option Explicit

Private Declare Function a2Ku_apigettime Lib "winmm.dll" _
Alias "timeGetTime" () As Long
Dim lngstartingtime As Long

Sub a2kuStartClock()
lngstartingtime = a2Ku_apigettime()
End Sub
Function a2kuEndClock()
a2kuEndClock = a2Ku_apigettime() - lngstartingtime
End Function


Sub QueryTimer(strQueryName As String)

Dim db As Database
Dim qry As QueryDef
Dim rs As Recordset

Set db = CurrentDb
Set qry = db.QueryDefs(strQueryName)

'Start the clock
a2kuStartClock
Set rs = qry.OpenRecordset()

'Stop the clock and print the results to the debug window
Debug.Print strQueryName & " executed in: " & a2kuEndClock & _
" milliseconds"

rs.Close
db.Close
Set db = Nothing

End Sub

Bob

 

[TylerTand]

Guys! At ease! I appreciate both of your points and your passion. I have a question for you Raskew. I have some experience using VBA code in Access but I have never set up a piece of code that is public and can be access anywhere. All of the code I have written has been in one module. I could use a little guidance in creating another module and then how to make calls to that module for the code. I have slightly disguised the use for this type of query because of the nature of my occupation. But the end result is the same. I need to be able to compare the last "purchase" to the next to last "purchase". This is for statistical analysis of each type of "purchase". I like the idea of writing the code once and just changing one variable for each type. I have 11 different purchase types that I need to calculate and creating 3 tables to figure out each one isn't an option. Your help is appreciated.
Tyler

 

[WayneRyan]

Tyler,

Nice thread y'all have going on here!

Bob's (very nice code) just allows one to easily produce a query with the
previously mentioned attributes. It has some limitations as it is single
dimensal ... Only one item is returned. In your case you might want the
date, item and price. But is is still a nice piece of code! If your user
was deciding (at runtime) whether they wanted the 5th, 6th or whatever
element, this will do it for you! Nice work Bob!

To use it, just put the code in your modules collection on your database
tab. It doesn't matter what you call the module.

If your form had a textbox for the user to enter which record to retrieve
and it was called WhichRecord:

call selmid2("query3", "fullname", 10, Me.WhichRecord, True, "nTest2")

Then, you could use query3 in any number of ways:

To feed a form,
With a DLookUp to get the next-to-last item.
With a DSum to sum their last 5 purchases, etc.

Pretty nice, and we don't even have to pay Bob.

End-of-rant ---> nice software Bob.




From another perspective:

dallr has a point in that one should always optimize queries, but when
the conditions change, one shouldn't always manually create a new query.
People are expensive!

Performance:

I don't know what Bob was basing the 750 ms on, but "generally" a user
shouldn't noticably wait. That doesn't seem too long (IN GENERAL).

But, nested Selects can be very detrimental to JET. Even with small
tables, the wait can be interminable.

Anyway, sorry for butting in Tyler, but it is an interesting topic.
I'd use Bob's routine to generate the query from a command button on
your form. Then just use his query.

If that doesn't work out, experiment with it, you just need to apply
two queries in succession (see Bob's code).

Wayne

 

[TylerTand]

O.K. I figured that one out. Thank you all for your help but I have another.....

I need to run a query on a table that lists multiple records for different individuals. I need to be able to group them by the subject identifying field "FINS" and then count the unique records where their Station is "IMB" and their Dispo is "ASID Full VR". SO I have to group by the FINS to get a list of unique fins with a Dispo of ASID Full VR and a station field of IMB. Then I need to count how many records there are. I don't want to create extra tables to store the list of FINS after the group by part is done. I am close, I know the key is in a nested query but I can't seem to get the syntax right or something because I get a criteria mismatch error message. Here is what I have so far:
SELECT Count(Encounter.FINS) AS TotalASIDFULLVR
FROM Encounter
HAVING (((Count(Encounter.FINS)) In (SELECT Encounter.FINS FROM Encounter WHERE (((Encounter.Dispo)="ASID Full VR") AND ((Encounter.Station)="IMB")) GROUP BY Encounter.FINS)));

Can you guys help me out with this one? Thanks again for you help.

 

[Reese]

I add my thanks to everyone for this technique, I used it for a project of mine and it works great!

I'd like some further help, though. I'm using this for a database that books events for clients. So instead of purchases, I have a table of clients and a table of events and if the client is a returner, I use a version of this query to pull information from their last event and display it via subforms in the form that I use to enter a new event.

I would like to integrate time as well as date into the equation. Sometimes I book multiple events for the same client on the same day, but because the date is the same this system doesn't work. Is there a way that I can change this so it recognizes an event that has occured earlier in the day?

Thanks!

 

[wcrimi]

I have a similar need, but it's not exact.

I have two racing tables (tabel1 and table2). table1 has a trackid, racedate, and racenum. table2 has participantname, trackid, racedate, racenum, and a racetime.

The second table has all of each person's races.

I want to get the next to last race of each participant that raced at a specific track/date/race, but not not always from the current date.

If table1 has a date of 9/1/16, I want to pick up all the data from 8/1/16 in table2.

If table1 has a date of 10/1/16, I want to pick up all the data for 9/1/16 and so on.

10/1/16
9/1/16
8/1/16
7/1/16

So it would work the same in that I need to select 2 races and then have to eliminate the last one, but I may have to start my positioning somewhere in the middle of all a person's races and not just take his last 2.

Thanks

 

[Arthur_Dhas]

You can do this using a query alone and it would be much faster than anything VBA can put together.

First You need to create a query that gives you the last two orders that a customer purchases.

SELECT Table1.ID, Table1.CustomerIDNumber, Table1.DateOfPurchase, Table1.Item
FROM Table1
WHERE Table1.DateofPurchase IN (SELECT TOP 2  T.DateOfPurchase FROM Table1 T WHERE T.CustomerIDNumber = Table1.CustomerIdNumber ORDER BY T.DateofPurchase Desc);

Then you need to use the above query and remove the last record by filtering out the max record.

SELECT qry_LastTwoOrders.ID, qry_LastTwoOrders.CustomerIDNumber, qry_LastTwoOrders.DateOfPurchase, qry_LastTwoOrders.Item
FROM qry_LastTwoOrders
WHERE qry_LastTwoOrders.DateOfPurchase NOT IN (SELECT MAX(Q1.DateOfPurchase) FROM qry_LastTwoOrders Q1 WHERE Q1.CustomerIDNumber = qry_LastTwoOrders.CustomerIDNumber);

See attached sample database in 2000 file format.

Dallr

You can do this using a query alone and it would be much faster than anything VBA can put together.

First You need to create a query that gives you the last two orders that a customer purchases.

SELECT Table1.ID, Table1.CustomerIDNumber, Table1.DateOfPurchase, Table1.Item
FROM Table1
WHERE Table1.DateofPurchase IN (SELECT TOP 2  T.DateOfPurchase FROM Table1 T WHERE T.CustomerIDNumber = Table1.CustomerIdNumber ORDER BY T.DateofPurchase Desc);

Then you need to use the above query and remove the last record by filtering out the max record.

SELECT qry_LastTwoOrders.ID, qry_LastTwoOrders.CustomerIDNumber, qry_LastTwoOrders.DateOfPurchase, qry_LastTwoOrders.Item
FROM qry_LastTwoOrders
WHERE qry_LastTwoOrders.DateOfPurchase NOT IN (SELECT MAX(Q1.DateOfPurchase) FROM qry_LastTwoOrders Q1 WHERE Q1.CustomerIDNumber = qry_LastTwoOrders.CustomerIDNumber);

See attached sample database in 2000 file format.

Dallr

thanks this solved my problem