Combine data in similar records and then remove duplicates

[Bitstreams]

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?

[CJ_London]

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

[Bitstreams]

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

[CJ_London]

you don't need this criteria

having count(*)>1

[mike60smart]

Hi
Do you have another table related to your Purchase Information table?

[madpiet]

I did it like this:
first, group the Product, Size, Color, and aggregate the others (so I get one record per product)

First Query:

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:

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;

[alansidman]

Another option is to bring your data into Power Query and apply the following Mcode

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.

[madpiet]

Oh cool, didn't even think about using PowerQuery!