Help with WHERE part of query

[UHNSTrust]

I have the following WHERE part of a query

WHERE (((CLIENT_ASS_TAB_DETAILS.TAB_NO)="14") AND ((CLIENT_ASS_TAB_DETAILS.TAB_SCORE) Is Null Or (CLIENT_ASS_TAB_DETAILS.TAB_SCORE)<>"1") AND ((CLIENT_ASSESSMENT_HEADER.ASS_COMPLETE)<>"2"))

What I want to include in the query is all records where TAB_NO "7", "11" and "14" all have a TAB_SCORE which is either Null or <>"1" and an ASS_COMPLETE value which <>"2".

Can anybody help with how this should be compiled? Have tried lots of combinations but either get multiple rows comin gback or nothing at all!!

Thanks in advance

Jonathan

 

[dhookom]

Are all three of these fields actually text or are they numeric?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[UHNSTrust]

Hi Duane,

Sorry but I should have put my brain into gear before posting. The table design I have is

UserID TAB_NO TAB_SCORE ASS_COMPLETE
user1 1 3 0
user1 2 0
****to****
user1 41 2 0

Each set of records has 41 tabs (i.e. 41 records) with a score for each tab. The UserID and ASS_COMPLETE are the same for all 41 records. I want to return the UserID where the records that has TAB_NO "7", "11" and "14" (all text values - inherited table structure) and associated TAB_SCORE values of Null or <>"1".

Hope this makes sense.

Thanks, Jonathan

 

[dhookom]

Try

SELECT DISTINCT UserID
FROM [CLIENT_ASS_TAB_DETAILS]
WHERE TAB_NO IN ("7", "11", "14") AND Nz(TAB_SCORE,"0")<>"1" AND ASS_COMPLETE <>"2"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]