Multiple Dates in a table

[Pete]

I have a downloaded table in which each item (primary key) has four date
fields in mmm-yyy format. Each date field has a corresponding quantity field
associated with it: contract date-contract quantity, delivery date-delivery
quantity,manufacturing date-manufacturing quantity, and ship
date-ship-quantity. I would like to get an output that compares the quanities
by date. For example, show me the contract quantity, the manufactured
quantity, the (promised) delivery quantity and the (actual) shipped quantity
by month. I've tried a variety queries, but the output either does not sum
the quantities correctly or it leaves months out. Thanks, Pete

 

[KARL DEWEY]

Change your table structure to this --
Item Activity ActionDate Quanity
123 contract 2/3/2009 45
123 delivery 3/5/2009 40
123 manufactur 4/1/2009 44
123 ship 5/5/2009 35

Use a union query to get the data into the new format --
SELECT [Item], "Contract" AS Activity, [contract date] AS ActionDate,
[contract quantity] AS Quanity
FROM YourTable
UNION ALL SELECT [Item], "Delivery " AS Activity, [delivery date] AS
ActionDate, [delivery quantity] AS Quanity
FROM YourTable
UNION ALL SELECT [Item], "Manufacturing" AS Activity, [Manufacturing date]
AS ActionDate, [Manufacturing quantity] AS Quanity
FROM YourTable
UNION ALL SELECT [Item], "Ship" AS Activity, [Ship date] AS ActionDate,
[Ship quantity] AS Quanity
FROM YourTable

Then use a totals query like this --
SELECT Item, Activity, Format([ActionDate], "mmm yyyy") AS [Month]
Sum([Quanity]) AS [Total]
FROM qryUnionActivity
GROUP BY Item, Activity, Format([ActionDate], "mmm yyyy")
ORDER BY Item, Activity, Format([ActionDate], "yyyymm");