How to construct a Query including CountOf

[kirkm]

I'm trying to output fields in my table where the Count Of text field "LC" is 1, and Number field "WO" is not 1.

What I have doesn't work.

SELECT [tbl1990-2010].LC, Count([tbl1990-2010].LC) AS CountOfLC, [tbl1990-2010].WO
FROM [tbl1990-2010]
GROUP BY [tbl1990-2010].LC, [tbl1990-2010].WO
HAVING (((Count([tbl1990-2010].LC))=1) AND (([tbl1990-2010].WO)<>1));

It's including instances where there are >1 "LC" field.

Thanks for any help.

 

[isladogs]

The query works fine for me as in attached test db
Suggest you post your data if you get different to this

 

[kirkm]

Hi Colin, your query is the same as mine yes ?
In your table there is no field LC where count = 1. There's 2 xyz, and 3 of abc and 3 of def. It should output nothing . But if there was one xyz and it's WO field isn't 1, it should output that.
I suspect I'm not explaining this too well... but has it made sense?

 

[isladogs]

Hi Colin, your query is the same as mine yes ?
In your table there is no field LC where count = 1. There's 2 xyz, and 3 of abc and 3 of def. It should output nothing . But if there was one xyz and it's WO field isn't 1, it should output that.
I suspect I'm not explaining this too well... but has it made sense?

yes - same query & giving the correct results

In screenshot below there are 8 table records with only 3 shown in the query

Record 3 (RED) - not shown as WO=1
Records 1 & 6 (BLACK) not shown as identical so count = 2
Records4 & 7 (BLUE) not shown as identical so count = 2

That leaves records 2,5,8 which are all unique & have WO<>1

If you want records where there's only one LC value and only where WO<>1 then you want this instead

SELECT [tbl1990-2010].LC, Count([tbl1990-2010].LC) AS CountOfLC
FROM [tbl1990-2010]
WHERE ((([tbl1990-2010].WO)<>1))
GROUP BY [tbl1990-2010].LC
HAVING (((Count([tbl1990-2010].LC))=1));

That does give no records with my example data
Can you see what I've done there?

 

[kirkm]

> If you want records where there's only one LC value and only where WO<>1 then you want this instead

Yes that's it exactly . But now I can't see WO and if checking Show gets a complex error msg about adding a field twice. But that shows multiple LCs.

>Can you see what I've done there?

Probably not entirely. This is not plain sailing for me!

 

[isladogs]

In that case, this has to be done in 2 steps:

1. Find the duplicate LC records. Save it as qryDupeLC

SELECT [tbl1990-2010].ID, [tbl1990-2010].LC, [tbl1990-2010].WO
FROM [tbl1990-2010]
WHERE ((([tbl1990-2010].LC) In (SELECT [LC] FROM [tbl1990-2010] As Tmp GROUP BY [LC] HAVING Count(*)>1 )))
ORDER BY [tbl1990-2010].ID, [tbl1990-2010].LC;

2. Do an unmatched query using the table & the dupes query to get records not duplicated ... and then filter where WO<>1

SELECT [tbl1990-2010].ID, [tbl1990-2010].LC, [tbl1990-2010].WO
FROM qryDupeLC RIGHT JOIN [tbl1990-2010] ON qryDupeLC.ID = [tbl1990-2010].ID
WHERE ((([tbl1990-2010].WO)<>1) AND ((qryDupeLC.ID) Is Null));

I've added some extra records to my table to test this out as shown below:

Hopefully this is exactly what you want!

 

[Pat Hartman]

I can't tell what you are actually looking for. It would have been better if you had posted a sample output to go along with your input.

Colin made one guess which seems logical although I think it should include a row for abc.

 

[isladogs]

I can't tell what you are actually looking for. It would have been better if you had posted a sample output to go along with your input.

Colin made one guess which seems logical although I think it should include a row for abc.

LOL.
Actually that was my third attempt.
In my first reply, abc3 was included in the output but that wasn't what the OP wanted.

 

[Pat Hartman]

I only looked at the final solution so I didn't see that. It would have been a much simpler problem with an accurate picture of the output desired.

 

[isladogs]

I only looked at the final solution so I didn't see that. It would have been a much simpler problem with an accurate picture of the output desired.

Yes I agree. I'm glad it wasn't just me who found the required criteria unclear.