Query returns wrong data?

[AC5FF]

I am not sure what is happening here. I am trying to query a table (DyessD23) to count up how many documents are in a certain status (cur). For the most part the query works - but returns numbers that are WAY off. If I go through the table and count myself I come up with numbers nearly 1/2 of what the query returns.

here is the SQL for the query:

SELECT Area.Element, Sum(IIf([dyessd23]![cur]="INW" Or [DyessD23]![CUR]="AWM" Or [DyessD23]![CUR]="FWP" Or [DyessD23]![CUR]="MTM",1,0)) AS [Dys AWM], Sum(IIf([dyessd23]![cur]="0*P" Or [DyessD23]![CUR]="AWP",1,0)) AS [Dys AWP], Sum(IIf(DyessD23!CUR="DWP",1,0)) AS [Dys DWP]
FROM Area INNER JOIN (lru INNER JOIN DyessD23 ON lru.NSN = DyessD23.NSN) ON Area.ID = lru.Team
WHERE (((DyessD23.LOC)="B1B"))
GROUP BY Area.Element;

Results from this query are:
364 77 0 (totals)

Since this isn't working - I thought I would pull all the documents - using the same basic query setup (tables/joins) and instead of counting the documents, just list them. This is the code I used for that:

SELECT DyessD23.doc, lru.abbr, DyessD23.cur
FROM (lru INNER JOIN Area ON lru.Team = Area.ID) INNER JOIN DyessD23 ON lru.NSN = DyessD23.NSN
WHERE (((DyessD23.loc)="b1b"))
GROUP BY DyessD23.doc, lru.abbr, DyessD23.cur;

Total number of records listed with this query is 212. So, I would expect the first query to total this, but instead I get 441. Again, going through the whole DyessD23 table I come up with 212 manually.

Any ideas on what could be causing the 'sum(iif(xxx ' lines to be getting so many hits?

Thanks

 

[PHV]

And what is the Total number of records listed if you remove the GROUP BY clause in the second query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AC5FF]

What the?!?!??!?!?!??!
Okay - removed the GroupBy on the second query. (Didn't think I could do that with the 'Totals' enabled...
At any rate, when I do that I get 474 records!!!
When I look at the query results - most lines are listed 3 times! Some twice. wierd.....

I'm totally out of ideas now!!

 

[AC5FF]

Okay -
So I went to the original query and tried to do the same thing. When I do I get an error saying 'You tried to exicute a query where "Element" is not part of an aggregate function'

???