Query problem

C

campbellbrian2001

Thanks in advance for any help with this query. I'm getting syntax
errors and "too many arguments" errors.

What I'm trying to do is count the number of trucks only if the
TotalLoss is above $0.00:

Count(IIf(qryTrucks.TotalLoss > 0),(qryTrucks.SerialNumber)) AS
CountOfTrucks,
 
C

campbellbrian2001

Sorry I made an error in typing and describing this, it's part of a
larger query, still the "count" syntax is throwing me:

SELECT qryTrucks.TagNumber, qryTrucks.Driver,
Count(IIf(qryTrucks.TotalLoss > 0),(qryTrucks.TagNumber)) AS
CountOfTrucks, Sum(qryTrucks.LossAmt) AS SumOfLossAmt,
Sum(qryTrucks.IndemCost) AS SumOfIndemCost, Sum(qryTrucks.TotalLoss) AS
SumOfTotalLoss
FROM qryTrucks
GROUP BY qryTrucks.TagNumber, qryTrucks.Driver
HAVING (((Sum(qryTrucks.LossAmt))>0) AND
((Sum(qryTrucks.TotalLoss))>0));
 
J

John Spencer

SELECT qryTrucks.TagNumber, qryTrucks.Driver,
Count(IIf(qryTrucks.TotalLoss > 0,qryTrucks.TagNumber,Null))
ASCountOfTrucks,
Sum(qryTrucks.LossAmt) AS SumOfLossAmt,
Sum(qryTrucks.IndemCost) AS SumOfIndemCost,
Sum(qryTrucks.TotalLoss) AS SumOfTotalLoss
FROM qryTrucks
GROUP BY qryTrucks.TagNumber, qryTrucks.Driver
HAVING (((Sum(qryTrucks.LossAmt))>0) AND
((Sum(qryTrucks.TotalLoss))>0));

You could also use
Abs(Sum(qryTrucks.TotalLoss>0)) as CountOfTrucks
 
C

campbellbrian2001

the Abs(Sum.... didn't change my results.

What my data looks like when I run the Datasheet for qryTrucks:
TagNumber Driver TotalLoss
1651434 125 $167.96
1624445 125 $493.11
9423432 125 $0.00
1554367 507 $549.81
4854242 507 $0.00
1546544 507 $494.25
8472145 507 $544.49

my needed query needs to look like this in Datasheet:
Driver CountOfTrucks
125 2
507 3
 
J

John Spencer

So what you want is a count of UNIQUE trucks per driver?

SELECT Driver, Count(TagNumber) as TrucksInvolved
FROM
(SELECT Driver, TagNumber
FROM qryTrucks
GROUP BY Driver, TagNumber
HAVING Sum(LossAmt)>0
AND Sum(TotalLoss)>0) as NumberOfTrucks
 
C

campbellbrian2001

Yes..What this app does is trace the number of accidents per UNIQUE
truck (tag number) by driver over a lifetime. So say Driver A has
operated 7 trucks in the last 30 yrs. and had 3 accidents of which only
2 had monetary claims, the one that had $0.00 claim should not sow up
on my datasheet.

Thanks! I inherited this query which has limitations as to how easily
it can be changed due to countless dependencies.
 
J

John Spencer

So, did the proposed query solve your problem? Or did it error or give you the
wrong results? Or did you try it?

I can't tell from your posting.
 
C

campbellbrian2001

Thanks John, I corrected my syntax to "Count(IIf(qryTrucks.TotalLoss >
0,qryTrucks.TagNumber,Null))" and this works perfectly... again
thanks...
 
Top