Results 1 to 8 of 8
  1. #1
    Bitstreams is offline Novice
    Windows 11 Office 365
    Join Date
    Feb 2024
    Posts
    2

    Combine data in similar records and then remove duplicates

    Please move this to a sub-forum if it sits better elsewhere.

    I have a table that holds purchase information for a number of items, and in many cases the only difference is the purchase date and quantity. e.g:



    Description Qty Size Colour Date purchased
    Rocky Road Trainer 1 12 Red 12/01/2023
    Rocky Road Trainer 3 12 Red 14/02/2023
    Blue Lagoon Sneaker 2 14 Blue 09/02/2023
    Blue Lagoon Sneaker 1 14 Blue 11/12/2023

    I need to summarise this information so that the quantities and dates a combined (sum the quantity and add a field for date range)

    Description Total Qty Size Colour Date Range
    Rocky Road Trainer 4 12 Red 12/01/2023-14/02/2023
    Blue Lagoon Sneaker 3 14 Blue 09/02/2023-11/12/2023

    Once I have this format I need to be able to push the table back out as a CSV.

    I assume that I would first create the additional fields (for total qty and date range) for every record (update query?) and then remove duplicates. So, how can I sum the qty based upon fields that match and how can I find the earliest and latest dates based on fields that match?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,354
    No need for another table- just use an aggregate query

    group on description, colour and size, sum quantity and min and max the date

    then use the transfertext function to export the query to a text file

  3. #3
    Bitstreams is offline Novice
    Windows 11 Office 365
    Join Date
    Feb 2024
    Posts
    2
    That's really helpful - thank you. I created an 'Aggregate duplicates' query based on the "Find duplicates query wizard" and this produces an output of 46 records (from a base of 479) with the sums and min/max exactly as I want them. But I assume this does not include records where there were no duplicates?

    The Criteria in the first field of the query does a 'select as tmp group by ... having count(*)>1

    Do I need to do a similar unmatched query and combine the two?

    EDIT: - I beg your pardon - I think even the individual entries are in there - fantastic

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,354
    you don't need this criteria

    having count(*)>1



  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,547
    Hi
    Do you have another table related to your Purchase Information table?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  6. #6
    madpiet is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Feb 2023
    Posts
    97
    I did it like this:
    first, group the Product, Size, Color, and aggregate the others (so I get one record per product)

    First Query:
    Code:
    SELECT Items.Product, Items.Size, Items.Colour, Sum(Items.Qty) AS [Total Qty], Min(Items.[Date purchased]) AS StartDate, Max(Items.[Date Purchased]) AS EndDateFROM Items
    GROUP BY Items.Product, Items.Size, Items.Colour;
    Then just use FORMAT to convert the dates to text and "merge" them
    Second Query:
    Code:
    SELECT qryMinMAXProduct.Product, qryMinMAXProduct.Size, qryMinMAXProduct.Colour, qryMinMAXProduct.[Total Qty], 
    Format([StartDate],"mmm dd yyyy") & " to " & Format([EndDate],"mmm dd yyyy") AS StartEnd
    FROM qryMinMAXProduct;

  7. #7
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Another option is to bring your data into Power Query and apply the following Mcode

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date purchased", type date}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"Description", "Size", "Colour"}, {{"Total", each List.Sum([Qty]), type number}, {"Start Date", each List.Min([Date purchased]), type nullable date}, {"End Date", each List.Max([Date purchased]), type nullable date}})
    in
        #"Grouped Rows"
    Close and Load the query results to the Data Model and then export it to a CSV file. Power Query is called Get and Transform Data and found on the Data Tab of the Ribbon in Excel.

  8. #8
    madpiet is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Feb 2023
    Posts
    97
    Oh cool, didn't even think about using PowerQuery!

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

Similar Threads

  1. Combine similar records
    By tareyj8569 in forum Access
    Replies: 7
    Last Post: 10-27-2016, 03:38 PM
  2. Replies: 9
    Last Post: 09-29-2015, 12:53 AM
  3. Combine similar data in report
    By pipoconanan45 in forum Reports
    Replies: 2
    Last Post: 09-17-2015, 12:04 PM
  4. Combine Similar Data From Three Queries
    By Kerberos in forum Reports
    Replies: 2
    Last Post: 05-02-2014, 11:27 AM
  5. Replies: 1
    Last Post: 12-23-2013, 03:19 PM

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