Counting number of records within a query

[SHAWN]

Hi, guys

Need your help, PLEASE....


Got a query that looks like:

Car UnloadDate Deliv_Date SomeOtherField
A123 13/3/2003 14/3/2003 VALUE1
A123 4/5/2003 4/5/2003 VALUE2
A123 4/5/2003 5/5/2003 VALUE3

The query is based on two tables:table1 has unique
[car],[unloaddate],[SomeOtherField],table2 has
[car],[unloaddate],[deliv_date].

Joins are by [CAR] AND [UNLOADDATE].[DELIV_DATE] IS NOT UNIQUE per
[car] and [unloaddate] combination.

What I need is to count the number of Deliv_Date per specific
[Car]&[UnloadDate] combination, i.e. first record should have a count
of 1, records 2 and 3 should have a count of 2, etc...

I tried subqueries, DCOUNT - of no avail.

TIA.

SHAWN

 

[Van T. Dinh]

1. Post the SQL of your attempts. Generally, the SQL will
tell us more on what you want.

2. In table2, is the combination Car/UnloadDate/Deliv_Date
uniquely indexed? i.e. Can you have more than 1 Record
with the same values for Car / UnloadDate AND Deliv_Date?

3. Do you need the values of SomeOtherFields in the Query
or you simply want Car / UnloadDate and CountofDeliv_Date
in the result of your Query?

Van T. Dinh
MVP (Access)

 

[SHAWN]

Hi, Van

Thanks for you response.

1.Here is my SQL with subselect:
SELECT Exceptions_History.[Car Number], SAP_Car_History.[Unload
Date], Exceptions_History.[SomeOtherField],
SAP_Car_History.[Deliv_Date], (SELECT Count(A.DELIV_DATE)
FROM (select SAP_Car_History.[RailCar ID], SAP_Car_History.[Unload
Date], SAP_Car_History.DELIV_DATE from SAP_Car_History
GROUP BY SAP_Car_History.[RailCar ID], SAP_Car_History.[Unload Date],
SAP_Car_History.DELIV_DATE) AS A
GROUP BY A.[RailCar ID], A.[Unload Date]) AS CountOfDelDate
FROM Exceptions_History INNER JOIN SAP_Car_History ON
(Exceptions_History.[Car Number] = SAP_Car_History.[RailCar ID]) AND
(Exceptions_History.UnloadDate = SAP_Car_History.[Unload Date]) IN
'\\SOME_DEPARTMENT\TrackingSystem\Trace_History\NCRTD_History.mdb'
WHERE (((Exceptions_History.ConstructivePlacement1) Is Not Null) AND
((Len([SAP_CAR_History].[Order]))=5))
GROUP BY Exceptions_History.[Car Number], SAP_Car_History.[Unload
Date], Exceptions_History.[SomeOtherField],
SAP_Car_History.[Deliv_Date];

I get "At most one record can be returned by this subquery....".


2.The Car/UnloadDate/Deliv_Date is not uniquely indexed. The prime key
is Autonumber.
There will only be one record per Car/UnloadDate/Deliv_Date
combination.

3.Yes, I do need SomeOtherFields in the query.

Thanks, again

SHAWN

 

[Van T. Dinh]

Not sure if this is what you want but try:

****
SELECT DISTINCT EH.[Car Number], EH.[Unload Date],
EH.[SomeOtherField], Count([Deliv_Date]) As CountOfDeliv_Date

FROM Exceptions_History As EH INNER JOIN SAP_Car_History As SAP IN
'\\SOME_DEPARTMENT\TrackingSystem\Trace_History\NCRTD_History.mdb'

ON EH.[Car Number] = SAP.[RailCar ID] AND
EH.[Unload Date] = SAP.[Unload Date]


WHERE (EH.ConstructivePlacement1 Is Not Null)
AND (Len(SAP.[Order])=5)

GROUP BY EH.[Car Number], EH.[Unload Date], EH.[SomeOtherField];
****

 

[SHAWN]

Thanks Van, works perfect!!!!