Query problem

  • Thread starter Thread starter campbellbrian2001
  • Start date Start date
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,
 
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));
 
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
 
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
 
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
 
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.
 
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.
 
Thanks John, I corrected my syntax to "Count(IIf(qryTrucks.TotalLoss >
0,qryTrucks.TagNumber,Null))" and this works perfectly... again
thanks...
 
Back
Top