Return Date Based On Another Date

[seantnash]

I am beginning to bang my head against a brick wall now so hopefully a wonderful person will be able to help me out with building my query! I'm pretty new to access so a "dumbed down" explanation would be appreciated I have Access 2016.

I am building a sales database where each sale is recorded in a table (Sales).

When I input the sale date I would like the table to call up that particular sales person's previous sale date.

I can use the MAX function to call up their most recent sale but this will only display the most recent sale - rather than previous sale based on the current sale's date. So, if I am data inputting a load of sales after the sale this wouldn't work how I want it to - especially if sales are being input out of date order.

Here's a sample of the sale table:

Sales Person ID ...... Sale Date


1 .......................... 01/01/01
2 .......................... 02/02/02
3 .......................... 03/03/03
2 .......................... 02/03/02
3 .......................... 02/03/03
1 .......................... 01/02/01
2 .......................... 02/03/04
1 .......................... 01/01/00

Then, when inputting the data into the form, I want it to look at the sale date and find and display the previous sale date. So, I would end up with a form autopopulating the previous sale date field. In tabular form it would look something like this:

Sales Person ID ..... Sale Date ..... Previous Sale
1 ......................... 01/01/01 ........ 01/01/00
2 ......................... 02/02/02 ........ First Sale
3 ......................... 03/03/03 ........ 02/03/03
2 ......................... 02/03/02 ........ 02/02/02
3 ......................... 02/03/03 ........ First Sale
1 ......................... 01/02/01 ........ 01/01/01
2 ......................... 02/03/04 ........ 02/03/02
1 ......................... 01/01/00 ........ First Sale

Hopefully, that will make sense to somebody *fingers crossed*

[orange]

I am building a sales database where each sale is recorded

Tell us more. Most databases are not designed with a single table.

Usually there are other things, Customer, Product, Salesman, Sale....

You may get some ideas for getting previous values from this link by Allen Browne.

[seantnash]

Yeah there are other tables - such as the sales person's contact details. I just didn't mention them as I figured they weren't really relevant to my query.

Thanks for the link - I shall check it out!