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

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » How to find the most recent transaction date of a unique item

    Author
    Topic
    #2337929

    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!

    Viewing 2 reply threads
    Author
    Replies
    • #2338831

      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.

    • #2339776

      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.

    • #2342221

      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;

       

    Viewing 2 reply threads
    Reply To: How to find the most recent transaction date of a unique item

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: