Results 1 to 9 of 9
  1. #1
    KatBaroo is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2021
    Location
    Brisbane
    Posts
    16

    Query with MAX function to get latest entry for multiple records

    Hi All

    I hope the following makes sense enough that you can help:

    I have a table with the following fields:
    *ID
    PartCode
    DateSet
    Tooling Cost
    Packaging Cost
    Other Cost
    Transport
    Repaint Cost


    LocalManCost
    Margin
    Rebate

    The list looks like
    ID PartCode DateSet Tooling Cost Packaging Cost Other Cost Transport Repaint Cost LocalManCost Margin Rebate
    15 700-037 1/11/2021 $0.00 $0.00 $0.00 22.00% $0.00 30.00% 5.00%
    16 700-050 1/11/2021 $0.00 $0.00 $0.00 22.00% $0.00 48.00% 5.00%
    306 700-050 25/10/2021 $0.00 $0.00 $0.00 22.00% $0.00 30.00% 5.00%
    17 700-052 25/10/2021 $0.00 $0.00 $0.00 22.00% $0.00 30.00% 5.00%
    300 700-052 1/11/2021 $0.00 $0.00 $0.00 22.00% $0.00 72.00% 5.00%
    18 700-053 25/10/2021 $0.00 $0.00 $0.00 22.00% $0.00 30.00% 5.00%

    Data entry for each Part Code + DateSet and relevant costing.
    I need to filter out the latest entries for ALL PartCodes which may not be the same date.
    I tried the following:

    SELECT tblPricingExtraCost.PartCode, Max(tblPricingExtraCost.DateSet) AS MaxOfDateSet, tblPricingExtraCost.[Tooling Cost], tblPricingExtraCost.[Packaging Cost], tblPricingExtraCost.[Other Cost], tblPricingExtraCost.Transport, tblPricingExtraCost.[Repaint Cost], tblPricingExtraCost.LocalManCost, tblPricingExtraCost.Margin, tblPricingExtraCost.Rebate INTO tblPricingExtraCostLatest
    FROM tblPricingExtraCost
    GROUP BY tblPricingExtraCost.PartCode, tblPricingExtraCost.[Tooling Cost], tblPricingExtraCost.[Packaging Cost], tblPricingExtraCost.[Other Cost], tblPricingExtraCost.Transport, tblPricingExtraCost.[Repaint Cost], tblPricingExtraCost.LocalManCost, tblPricingExtraCost.Margin, tblPricingExtraCost.Rebate;

    But that resulted in a full list of all partcodes with all dates, i.e. double ups.

    Then I tried:

    SELECT tblPricingExtraCost.PartCode,Max(tblPricingExtraCo st.DateSet) AS MaxOfDateSet
    FROM tblPricingExtraCost
    GROUP BY tblPricingExtraCost.PartCode
    tblPricingExtraCost.[Tooling Cost], tblPricingExtraCost.[Packaging Cost], tblPricingExtraCost.[Other Cost], tblPricingExtraCost.Transport, tblPricingExtraCost.[Repaint Cost], tblPricingExtraCost.LocalManCost, tblPricingExtraCost.Margin, tblPricingExtraCost.Rebate INTO tblPricingExtraCostLatest
    FROM tblPricingExtraCost
    GROUP BY tblPricingExtraCost.PartCode, tblPricingExtraCost.[Tooling Cost], tblPricingExtraCost.[Packaging Cost], tblPricingExtraCost.[Other Cost], tblPricingExtraCost.Transport, tblPricingExtraCost.[Repaint Cost], tblPricingExtraCost.LocalManCost, tblPricingExtraCost.Margin, tblPricingExtraCost.Rebate;

    But now I am getting
    “Syntax error (missing operator) in query expression “tblPricingExtraCost.PartCode tblPricingExtraCost.[ToolingCost’.

    Once I have filtered the records I have to calculate the actual prices so I thought to create a table (not yet done) with the results to use for the price calculation.

    I am looking at it and my eyes are bleeding. I am sure it is something simple I am not seeing.

    Katbaroo - because the Yak yaks -

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    My advice to you would be to re-post or edit first post.
    a) put code tags (# on forum posting toolbar) around any code/sql and use proper indentation where required. If you'll notice, the forum injects spaces at around the 50 contiguous character mark, which often raises questions from those who don't know this (e.g. (tblPricingExtraCo st.DateSet) )

    b) for tables like yours, either create in Excel (I find centering in Excel helps) and paste here - you'll get a nice table. Or use Advanced features and create your own. Maybe some will try to figure out which values belong in which column/field but when mis-aligned like that, that group does not include me.
    Y'all have to realize we're unpaid volunteers, each with their own limits as to how much we'll struggle with information provided. Maybe others can handle that, but why limit yourself to such a small group?

    EDIT - if you intend to store calculations, it is usually not advised. Storing a sale price would not be an example of that since they are subject to increases or discounts. Storing the line total (qty*price=$x) and the invoice total would be.
    Last edited by Micron; 11-09-2021 at 09:56 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    this confused me, not clear what you are trying to do

    I need to filter out the latest entries for ALL PartCodes which may not be the same date.
    so if you have dates of

    1st Jan
    1st Feb
    1st Mar

    you don't want the 1st Mar but do want 1st Jan and 1st Feb. Is that correct?

    and what does the same date mean? same date as what?

    Suggest from the example data you provided, show which records you want returned, what they need to look like and explain the basis for why

    I suspect you need a subquery of some sort, but can't be sure until it is clear what the requirement actually is

  4. #4
    KatBaroo is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2021
    Location
    Brisbane
    Posts
    16
    Thanks for the tips and tricks. Will update the post soon.

    Not sure I understand the calculation comment. Have to think it about it more to see the difference.

  5. #5
    KatBaroo is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2021
    Location
    Brisbane
    Posts
    16
    Thanks for trying to understand what I tried - and failed to explain.

    I will post the table again and try and clarify my problem.

  6. #6
    KatBaroo is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2021
    Location
    Brisbane
    Posts
    16

    Question More clarity to find help for MAX

    Hi All

    It appears my attempt at clarity resulted in more mud. So let me try again so all you good people can look at helping.

    The data I have in my table is the following

    ID
    PartCode
    DateSet
    Tooling Cost
    Packaging Cost
    Other Cost
    Transport
    Repaint Cost
    LocalManCost
    Margin
    Rebate
    15
    700-037
    1/11/2021
    $ -
    $ -
    $ -
    22%
    $ -
    $ -
    30%
    5%
    16
    700-050
    1/11/2021
    $ -
    $ -
    $ -
    22%
    $ -
    $ -
    48%
    5%
    306
    700-050
    25/10/2021
    $ -
    $ -
    $ -
    22%
    $ -
    $ -
    30%
    5%
    17
    700-052
    25/10/2021
    $ -
    $ -
    $ -
    22%
    $ -
    $ -
    30%
    5%
    300
    700-052
    1/11/2021
    $ -
    $ -
    $ -
    22%
    $ -
    $ -
    72%
    5%
    18
    700-053
    25/10/2021
    $ -
    $ -
    $ -
    22%
    $ -
    $ -
    30%
    5%


    My query should give me the cost and margins for the latest entry for that part
    i.e ID 15, ID16, ID300 - all 1/11/2021 + ID 18 from 25/10/2021

    Over time there may well be a wider date range. Updating all of the items all the time for some changes for some items didn't make sense to me, that's why I have this table: Just add the changes for the item that is being changed.

    I hope this explains a bit more what I am after. Once I have filtered these values out I need to store them in a table to assist with the price calculation - which is another query.

    Thank you

    Katbaroo - now the Yak really yaks

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    It is a question of terminology and this illustrates how describing a problem can be misinterpreted

    using your example data - partcode 700-050. It is there twice, once dated 1/11/2021 on id 16 and again dates 25/10/21 on id 306.

    You said

    I
    need to filter out the latest entries for ALL PartCodes which may not be the same date.


    Filtering out means you exclude them

    However now you have provided an example of what needs to be returned (i.e. not filtered out), it is much clearer

    There are several methods to do this but the most efficient would be to

    1. create a new query - something like

    Code:
    SELECT PartCode, Max(dateset) as MaxDate
    FROM tblPricingExtraCostGROUP BY PartCode
    (no need to sort)

    2. then in another query join this to tblPricingExtraCost on PartCode and date - something like

    Code:
    SELECT tblPricingExtraCost.*
    FROM tblPricingExtraCost INNER JOIN qry ON tblPricingExtraCost.partcode=qry.partcode and tblPricingExtraCost.dateset=qry.maxdate

  8. #8
    KatBaroo is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2021
    Location
    Brisbane
    Posts
    16
    You genius person you - thank you so much. Seeing the code showed me where I went wrong in mine. I was close but somehow managed to select TOP 1. Thank you for stopping the eye bleed.

    Katbaroo - The Yak yaks no more

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    happy to help

    I you wanted to know the price on a specific date in the past, modify the first query to

    Code:
    SELECT PartCode, Max(dateset) as MaxDate
    FROM tblPricingExtraCost
    WHERE dateset<=[enter some date]
    GROUP BY PartCode
    And if you want to keep it all in one query

    Code:
    SELECT tblPricingExtraCost.*
    FROM tblPricingExtraCost INNER JOIN (SELECT PartCode, Max(dateset) as MaxDate
                                                              FROM tblPricingExtraCost
                                                                 WHERE dateset<=[enter some date]
                                                                  GROUP BY PartCode) AS qry 
    ON tblPricingExtraCost.partcode=qry.partcode and tblPricingExtraCost.dateset=qry.maxdate

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 07-31-2018, 08:47 AM
  2. Latest Records to a query from the table
    By Perfac in forum Queries
    Replies: 2
    Last Post: 04-01-2018, 07:41 PM
  3. Replies: 4
    Last Post: 07-04-2013, 12:07 PM
  4. Replies: 8
    Last Post: 07-13-2012, 04:53 PM
  5. Query to show latest records
    By Conceptz in forum Queries
    Replies: 3
    Last Post: 05-24-2012, 01:01 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums