Query Counts Wrong numbers

[AC5FF]

I apologize, I had another thread out a while back on this, but couldn't locate it today.

I have a query that counts up the records in a table that pertain to my work areas. While the query runs fine w/out errors, the numbers it returns are completely wrong. I was able to take a sample of data and put it into a samble db to see if anyone out there can help ... (no emoticon for crossed fingers!)

There are three tables: Area, DyessD23, and LRU. (A lot of data has been stripped from these tables, but the required info to run the query is there).
Area: Tells me information about each team
DyessD23: Complete list of open work orders (documents)
LRU: Part information; what team repairs it, etc...

The two queries; Doc List and Work Status. Work Status is what counts up the documents and is what isn't working. The Doc List was built so I could manually count the documents myself. The Doc List query gives me 228 records from the DyessD23 table's 2390 records (5 of which shouldn't be counted). The Work Status query should return a total of 223 documents in the various columns, but instead it returns 501!

Can someone please look at this and tell me where I might be going wrong? I've tried different joins between the tables, but that doesn't work...

Thanks!!

 

[gemma-the-husky]

i presume some of your records are giving cartesian joins

you are trying to select items that match certain criteria

now if your query finds (eg) two matching values in one table, for a particular join in the query, you will get two rows in the final query

so instead of getting 228 rows, some are really duplicates (or more) and therefore you get 500+ rows

 

[John Big Booty]

........

The two queries; Doc List and Work Status. Work Status is what counts up the documents and is what isn't working. The Doc List was built so I could manually count the documents myself. The Doc List query gives me 228 records from the DyessD23 table's 2390 records (5 of which shouldn't be counted). The Work Status query should return a total of 223 documents in the various columns, but instead it returns 501!

.......

Are you able to identify the 5 additional records?

I think the queries are working fine. It's just that they are not returning the result you expected .

 

[AC5FF]

Gemma
I agree - duplicates are being counted - somehow. But how can i prevent that from happening??

John
Yeah, I know what 5 records are not counted. The query is counting everything in location B1B that is either AWM, AWP, 0*P, AWF, DWP, MTM (think that's all of em). There are 5 records that have a blank status, or are a TIN status that also are B1B.

Hmmm

 

[John Big Booty]

Then all you need to do is exclude them from your query? Perhaps something like;

Not Is Null And <>"tin"

 

[John Big Booty]

Probably should have said;

Not Is Null Or <>"tin"

 

[AC5FF]

John
That would work if I were just going to count all the docs grouped together, but I need to seperate the counts...

I looked at this problem from a different angle. Take the DOC LIST query and run a query on it. This query works (I.E. counts the right numbers):

SELECT [Doc List].element, [Doc List].CUR, Count([Doc List].doc) AS CountOfdoc
FROM [Doc List]
GROUP BY [Doc List].element, [Doc List].CUR;

So, I thought I would use the [countofdoc] field in another query. If I group by [element] and sum(iif(cur="AWM",countofdoc,0)) the query returns a total of 228 records (the number in the doc list query).

This just isn't making any sense! Just to count a list of records based on if a field in a table is a certain code??? What could I be overlooking!

Gemma
could you explain cartesian joins? Could that be where a problem is? That honestly doesn't make sense if I want to count just the records in the [Doc List] query......

 

[John Big Booty]

Have you tired writing a query that removes the spurious data and then basing your count query on the results of that query?

Sometimes it is not possible to arrive at a solution in a single bound, but rather in a number of smaller steps.

 

[John Big Booty]

........
Gemma
could you explain cartesian joins? Could that be where a problem is? That honestly doesn't make sense if I want to count just the records in the [Doc List] query......

Google is your friend

 

[AC5FF]

Any other ideas???
The fact that I can count accurately by CUR; but when I try to count combined the numbers go screwy just is boggling my mind!

 

[Brianwarnock]

NSN 1680012355183 appears twice in the LRU table causing any record in the
DyessD23 with this to be counted twice, there are others, should this be so.

Brian