Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Identify the Max and compare with the deadlines (2003)

    I have the following columns in a query. I want to create another query using the data from this original query. My goal is to find the latest time for each endpoint for each company. There are two deadlines..a 9:30 AM and a 9:50 AM so any time after 9:50 would be considered "Late", any time after 9:30 would be considered "Last", all others are "Prior".
    So far this is what I have, I added a column for an Expr1 to extract the time by using the MID function but when I tried to add another Expr for the target time it would not accept the 9:30 AM. Could anyone give me an advice/suggestion on how to go about this??

    A. 10/14/2008 3:39:35 AM
    B. Company Name
    C. End points (there are 2-4 endpoints for each company and each endpoints can have multiple delivery times)

    Please explain it in layman's terms since I am very new to Access.
    Thank you in Advance

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Identify the Max and compare with the deadlines (2003)

    You can do this using two queries. I have attached a sample database that illustrates this approach.

    The first query is a totals query that groups by company and endpoint and returns the maximum date/time for each. This query is named qry1_MaxDateTime in the sample database.
    The second query is based on the first one. It calculates the time component of the maximum date/time by subtracting the integer part. I have called this calculated field T.
    This is compared to the deadlines in another calculated field:

    Score: IIf(<!t>[T]>#12/30/1899 9:50:0#,"Late",IIf(<!t>[T]>#12/30/1899 9:30:0#,"Last","Prior"))

    This query is named qry2_CompareToDeadlines.
    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identify the Max and compare with the deadlines (2003)

    Thank you the quick response Hans!! I'll give it a try. bedankt!

  4. #4
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identify the Max and compare with the deadlines (2003)

    I gave it run and it did not work. I'm not sure if is because your query was from a table and mine was from the result of a query. What I want to do is to query the result of a former query that was provided to me my my boss. His query has a lot of VBA code which I am unable to decipher. I formatted the result that he gave me into a table, please see attached.

    Example: If I query the date of 10/14/08 for customer name "Barron Associates" and the Receiver (endpoint) "1116". The result should be
    9:50 AM target -"Late" file is 6 (count) and $217.47 (amount); 9:30 AM target- "Last" file is 4 (count) and $2,102.50:

    Thank you soo much..Hope I didn't give you or anyone else a big headache.
    Attached Files Attached Files

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Identify the Max and compare with the deadlines (2003)

    Try this query:

    SELECT qrylatefile.PROC_DATE, qrylatefile.[PBS TIME], qrylatefile.Receiver, IIf(Format([PBS TIME],"hh:nn")>"09:50","Late",IIf(Format([PBS TIME],"hh:nn")>"09:30","Last","Prior")) AS [When], qrylatefile.Count, qrylatefile.Amount
    FROM qrylatefile
    WHERE (((qrylatefile.Receiver)=1116));

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Identify the Max and compare with the deadlines (2003)

    If I understand your new description correctly, you didn't want to identify the maximum time, you wanted to select all times later than 9:30 AM. Does the modified version of the query in the attached database do what you want?
    Attached Files Attached Files

  7. #7
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identify the Max and compare with the deadlines (2003)

    You guys are AWESOME!!! Thank you so much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •