complicated query, PLEASE help.

[Ian W.]

Hi,
I'm trying to write a query to consolidate records. I have 5 fields.
"SHIPTO" "TRACKING_NUMBER" "DELIVERY DATE" "SHIP_DATE" and"RUWEIGHT".
All records in the file are shipments. I need to combine records based on
the "ship to" then by "delivery date". I want the query to return records
where the "delivery dates" correspond with "shipdates"s, this, while still
combining records from the "ship to" field that have the same "delivery date"
except with a later ship date. Additionally, I need to know the sum of the
weights for the individual records it consolidates as well as how many
shipments were combined to form each new record(done by using "count" in the
"total" field of the design view for the field "tracking number").

What the query dosent do is: Pick only the 1st ship date that corresponds to
the delivery date. I'm getting all shipments that correspond to that delivery
date.

I can't figure out how to get access to do this with out only matching
"delivery dates" with "ship dates". How can I have records with later "ship
date"s consolidated into the same new record as records with the earliest
"ship date" based on the "delivery date". I need the new record to only
display the earliest "ship date" How can I do this? I hope that made sense.
I've attached a copy of the SQL as well as the design view specifications.
If anyone could help I would REALLY appreciate it.
Thanks in advance!
-Ian


SELECT [consul ship oly aur].SHIPTO, Count([consul ship oly
aur].TRACKING_NBR) AS CountOfTRACKING_NBR, [consul ship oly
aur].DELIVERYDATE, First([consul ship oly aur].SHIP_DATE) AS
FirstOfSHIP_DATE, Sum([consul ship oly aur].[RU WEIGHT]) AS [SumOfRU WEIGHT]
FROM [consul ship oly aur]
GROUP BY [consul ship oly aur].SHIPTO, [consul ship oly aur].DELIVERYDATE
ORDER BY [consul ship oly aur].DELIVERYDATE, First([consul ship oly
aur].SHIP_DATE);


Field: SHIPTO TRACKING_NBR DELIVERYDATE SHIP_DATE RUWEIGHT
Table: (all from same table)
Total: group by count group by
first sum
Sort: ascending
ascending