Count/SUM Distinct Help

[kidrobot]

I've searched on google for count distinct help but I had no luck. I'm counting 2 different columns and when the query is run the counts in both columns appear exactly the same. Also, I need a SUM distinct (if possible) to sum only where it is being counted in 'CountOfCar_Nr'. Here is my SQL

SELECT tblCars.origin_date, tblCars.Train, tblCars.train2, tblCars.OS, tblCars.city_name, tblCars.Ns_Origin_Os, tblCars.Ns_Dest_Os, tblCars.Ns_Origin_City, tblCars.Ns_Dest_City, tblCars.Origin_Road, tblCars.Dest_Road, tblUnits.Shipper_Name, tblUnits.Shipper_332, IIf([car_type]="V","MULTI",IIf([Load_Empty]="E","EMPTY",[Block])) AS new_block, 
[B]Sum(tblCars.Car_Length) AS SumOfCar_Length, 
Count(tblUnits.Equip_Nr) AS CountOfEquip_Nr, 
Count(tblCars.Car_Nr) AS CountOfCar_Nr[/B]

FROM tblUnits RIGHT JOIN tblCars ON (tblUnits.Car_Nr=tblCars.Car_Nr) AND (tblUnits.Car_Init=tblCars.Car_Init) AND (tblUnits.OS=tblCars.OS) AND (tblUnits.train_id=tblCars.train_id) AND (tblUnits.origin_date=tblCars.origin_date)

GROUP BY tblCars.origin_date, tblCars.Train, tblCars.train2, tblCars.OS, tblCars.city_name, tblCars.Ns_Origin_Os, tblCars.Ns_Dest_Os, tblCars.Ns_Origin_City, tblCars.Ns_Dest_City, tblCars.Origin_Road, tblCars.Dest_Road, tblUnits.Shipper_Name, tblUnits.Shipper_332, IIf([car_type]="V","MULTI",IIf([Load_Empty]="E","EMPTY",[Block]));

 

[neileg]

Count works on the number of records in the query. So it doesn't matter which field you count, you get the same answer. If you want counts of different datasets, you either need different queires, or use DCount()

 

[kidrobot]

dcount("Eq_nr","tblCars, everything?)
dcount("car_nr","tblUnits, everything?)

what would I use for criteria?

 

[Guus2005]

dcount("Eq_nr","tblCars, everything?)
dcount("car_nr","tblUnits, everything?)

what would I use for criteria?

Dont need criteria.

dcount("Eq_nr","tblCars)
dcount("car_nr","tblUnits)

Perhaps you shouldn't try to solve your problem with just one query. It gets complicated that way.