Forum Discussion

Darkmjolnir's avatar
Darkmjolnir
Copper Contributor
Mar 12, 2025

I would like help writing a query to determine when people should follow up on a late shipment

Basically, I have a table that is a log for when issues of different magazines are received.  Here are the fields:

 

Title of the magazine

Frequency (Monthly, bimonthly, annual, etc)

Issue date (the date printed on the magazine)

Received Date (The date the item was received)

Expected Next Issue Date (Calculated field that figures out when the next issue should come in based on the Received Date and the Frequency)

 

I want a query that, for each title, returns the record for the last issue that was received, but ONLY if it is after the "Expected Next Issue Date" for that record.

 

For example, Time is Biweekly.  So If they receive it January 1, and it is now January 15 and the next issue was not received, I want the query to return the record for the issue that was received on January 1, but not any of the issues that were returned before that (so not any issues that were received in December of the previous year).

 

Right now I have a query that does that, but it requires the user to check off old issues as they receive things, which is not ideal.  

 

Any ideas on how to write a query that will do what I want?  

access
Query

3 Replies

Sort By
  • arnel_gp's avatar
    arnel_gp
    Steel Contributor
    Mar 14, 2025

    You may also try:

    SELECT 
    [Title of Magazine], 
    Frequency,  
    [Issue Date], 
    [Received Date], 
    [Expected Next Issue Date]
FROM YourTable 
WHERE 
[Issue Date] = DMax("[Issue Date]", "YourTable", "[Title of Magazine] = '" & [Title of Magazine] & "'") 
AND 
Date() < [Issue Date] + Switch([Frequency]="Monthly", 30, [Frequency]="Bimonthly", 15, True, 365)

     

  • Darkmjolnir's avatar
    Darkmjolnir
    Copper Contributor
    Mar 13, 2025

    Thanks for replying.  What i ended up doing that worked is i did a simple query for the table and used max from the sigma symbol options and did <Date() in the criteria for the received date field.  Which is probably more or less the same thing as what you said, i'm not sure (i'm new to making access databases so i'm not great with the lingo).

  • Tom_van_Stiphout's avatar
    Tom_van_Stiphout
    Steel Contributor
    Mar 13, 2025

    I typically use 2 queries when I need all fields of the matching record.

    Q1 gets me the dates for each title that matches your requirements:

    Select Title, max(ReceivedDate)

    From tblLog

    Where ExpectedDate < Date()

    Group by Title

    Then I create Q2 with tblLog and Q1, joining on both Title and ReceivedDate.

    The same can be accomplished with a single subselect query.

Resources