Look Up most recent data for an employee

[Gladman]

Hi All,

I am trying create a query that will look at an employees past sales but I want to add a field that will show the employees last sale value / quantity of products and have tried using the DLast function but it only displays a random value which is the same for all employees. Not what I want.

If I use "John Smith" as my employee i want (may need to copy and paste to excel to view);

EmplyeeName OrderDateAndTime OrderNo OrderValue ItemQuantity LastOrderDateAndTime LastOrderValue LastItemQuantity
John Smith 15/12/2016 12:05 11585 £120.55 4 14/12/2016 16:45 260.47 6
John Smith 14/12/2016 16:45 11524 £260.47 6 12/12/2016 11:15 184.95 1
John Smith 12/12/2016 11:15 11421 £184.95 1 12/12/2016 10:30 126.44 2
John Smith 12/12/2016 10:30 11414 £126.44 2 11/12/2016 13:48 206.33 2
John Smith 11/12/2016 13:48 11385 £206.33 2 08/12/2016 09:36 154.36 1
John Smith 08/12/2016 09:36 11154 £154.36 1

What I am actually getting is the exact same LastOrderValue, LastOrderValue & LastItemQuantity

Even when i remove the John Smith filter every employee has the exact same value.
I have tried DLast function but with no success. Any help on what the correct function i should use will be great.

Cheers

 

[Minty]

You need to use a sub query http://allenbrowne.com/subquery-01.html this will get you the last orderid for each employee, you can then link that order id back in to bring in the details.

If you can't make the sub query work, then create a query that simply finds the last order id for each employee. Save that query. Then use that query in another query to restrict the records you view.

 

[Frothingslosh]

I'd actually use Minty's second suggestion first. Access has a really annoying habit of calculating a subquery once for each record in the main query unless you set it up just right, and as a result of that, it's usually MUCH faster to use a query as a record source instead.

So your first query you'd simply pull employee ID, order ID, and OrderDateAndTime. Turn on sorting and grouping, leave the two ID fields at 'group by', and change the sorting/grouping for the date/time field to 'Max'.

Your second query would be based on the same table you're currently using plus the new query, linking them on order ID.

Now if you're using a SQL Server back end, then the subquery is definitely the way to go.

 

[Gladman]

Thank you both for your help.
I currently link my db to sql to perform ranking functions for weekly / monthly most valued customers and I believe we will one day use a full sql database so I will keep both your comments on board.
The code I used to get my results is below.
Thanks once again.

LastOrderValue: (SELECT TOP 1 Dupe.OrderValue
FROM tblOrders AS Dupe
WHERE Dupe.EmployeeID = tblEmployees.EmployeeID AND Dupe.OrderID<tblOrders.OrderID
ORDER BY Dupe.OrderID DESC)

 

[Frothingslosh]

Actually, my advice is a bit off - my proposed query would NOT get you want you want. This is a working approach:

qryLastOrderDates: This one you just do an aggregate query (the sorting and grouping) with the employee ID as group by and order date as max.

qryLastOrders: This one is linked to tblEmployees, tblOrders, and qryLastorderDates. Join tblEmployees to tblOrders however you need, and tblOrders to qryLastOrderDates on both the employee ID and order date fields. Then either just use SELECT DISTINCT or turn it into an aggregate query, pull the fields you want, and you should be good.

I bring this up because using OrderID the way you did assumes that orders will always stay in sequence. While that is, admittedly, normally the case in an autonumber field, what happens if someone, for some reason, needs to enter backdated orders or changes a date? As a rule, surrogate primary keys really shouldn't be used as actual data, and odd situations can result in the most recent OrderID *NOT* being the most recent sale.

 

[Gladman]

I see what you are saying here. the OrderID is an autonumber field so that is why my test was successful. I did try using the DateAndTime but it was asking for parameter values. The spellings seemed ok so I just tried the OrderID and it worked. I will have a re-try with your suggestion.

 

[The_Doc_Man]

This question illustrates an example of the "Old Programmer's Rule" that Access won't tell you anything you didn't tell it first. In brief, if you are going to ask a question about date and time for some event then you have to record the date and time for that event. Then you can query that information (with ORDER BY DESC MyDateFieldName as part of the query) to see the most recent items first.

but it only displays a random value which is the same for all employees

This normally happens when the DLast isn't properly constrained. Odds are that the "random value" is actually the last legitimate update for ANY employee.