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;