Trying to return a prior stored value in Access

[dragonflydei]

Hello all. I did not see a similar topic posted, but I have a bit of a tough query I am trying to figure out. I have a list of say customer visits, and they may have visited one of 7 store sites on any particular visit. I want to know for that customer what their last visit date was for that same store. For example, I go to FL store on Jan 1, 2011; my next visit is GA store in February 2011; In April 2011 I visit the FL store again. I want to create a query that gives me the April visit information, and a field called last visit, and pull the last FL visit date from the prior record for that state store. This is an extremely simplified version of what I need but I hope that you get the idea. I know the field, I have a way of tying the customers together, I just cannot figure out the code to have it return the next to last visit at that store. (Last returns me the visit data from the same record I am looking at). I have the feeling when I see the solution, I will feel silly for not getting it, but I have a mental block. Any thoughts?

 

[Red17]

The SQL that I would use based on your simple example above is:

SELECT tblCustomerVisits.fldCustomer, tblCustomerVisits.fldStorevisited, Max(tblCustomerVisits.fldVisitdate) AS MaxOffldVisitdate
FROM tblCustomerVisits
GROUP BY tblCustomerVisits.fldCustomer, tblCustomerVisits.fldStorevisited;

 

[dragonflydei]

The problem with this is the same as the LAST function, these are stored tables and I am pulling this information after the fact, so the MAX brings me the same result as the current visit. So I get my latest visit result and that same date as my prior store visit. I am not sure if I am making sense. Since it is all stored data already, max brings me the last visit date, not the prior to that visit date that I am trying to get.

Thank you for any further direction that you can provide.

Kind Regards,
Dei

 

[BobMcClellan]

In SQL you could use a ranking function in a CTE. (If your app is an adp I'd explore that).

If it's an .mdb or .accdb, I'm sure there are other ways but.. I would probably create a temp table with the columns you need + an auto-incrementing ID for the PK.

then you can write a routine that purges and repopulates the data, ordered by Company & Date.

Then you query for the max date for each company and get the ID.

From there you can query on the ID's that were returned and ID-1.
This should give you the last date and the previous date.
hth,
..bob