How to find the most recent transaction date of a unique item

Topic

New Reply

MS Access 2016

Hello! I have an access table that records dates of a transaction and I’m trying to create a single query that will show the most recent date of a specific transaction. I cannot give the exact details of what my data – so will try to explain what I’m trying to doing with other examples:).

Say I have a table that lists dogs and a record of the date they had a bath. I need to maintain the historical record of the dates they had a bath. So I have Spike, Jaws, Princess, Thumper, and Buster. Each of them have multiple date entries of each time they’ve had a bath. I also have Tiger and Piglet, but they don’t get their baths with us anymore. So- they’re not active customers. I cannot delete their historical bath dates.

Is it possible to create a query that my boss can pull that will show each current customer/dog and the most recent date that they had a bath? And then – is it possible, in that same query to calculate what date they would be due for a bath again…say 30days from the date of their most current bath date?

 

Thank you in advance for any help! And thank you for playing along in my make believe scenario!

Reply | Quote

Replies

Does your dataset have details of which clients are active and which are not, and also, do you have multiple animals per client?
It would impact how a query would be created.

Reply | Quote

Hi Kristy! Thank you for asking a follow-up! The dogs, for this example, are considered my clients-they don’t have owners, so I guess they would be likened to patients. In my client table, the days are my primary and there is a yes/no column for “active.” In my office visit table lists the dogs (related to main table) and every date they came in for a bath (with a column that calculates the next bath due date). But it does not have the active column – and and because I have a historical record of their bath visits, I also have a historical record of their next due dates… In hindsight, that column probably shouldn’t be in the main table, but in the query/report that I’m looking to build…. I don’t need to keep a record their historical next due dates, just the record of when they actually came in.

Reply | Quote

This is an example of a possible solution.

Suppose we have a table:
CREATE TABLE DOGWASH
(OWNER-NAME CHAR(30) NOT NULL,
DOG-NAME CHAR(30) NOT NULL,
BATH-DATE DATE NOT NULL);

Then the query that is asked for will be:
SELECT
OWNER-NAME,
DOG-NAME,
MAX(BATH-DATE) AS LAST-BATH,
DATEADD(DAY, 30, MAX(BATH-DATE)) AS NEXT-BATH
FROM DOGWASH
GROUP BY OWNER-NAME, DOG-NAME
ORDER BY OWNER-NAME, DOG-NAME;

 

Reply | Quote