Query records where a field doesn't match 1

[FoxProProgrammer]

I have three tables. Call them A, B, and C. Table A has a primary key named JobID. Tables B and C are related to A by JobID. How do I write a Query to return all records in B and C that don't match a JobID in A? For example:

Table A has records with JobID = 1, 2, 3, 4, 5

Table B has records with JobID = 2, 5

Table C has records with JobID = 1, 2

The Query should return 3 and 4 since those JobIDs are in table A, but not in table B or C.

Thank you very much.

dz

 

[dhookom]

What do you actually want "all records in B and C that don't match a JobID in A" or "JobIDs are in table A, but not in table B or C"?

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]

 

[FoxProProgrammer]

Well, it's easy to get confused with the semantics - which is why I gave an example. Table A contains Jobs. Table B contains a set of parts that might be used on a job. Table C contains a different set of parts that might be used on a job. I want to know which jobs in table A don't have any parts in table B or C associated with them. So technically, your latter statement is more correct. I want to know the JobIDs in Table A that aren't in Table B or Table C.

I have another table (call it D) that has a Yes/No field. It is also related to table A by the JobID. If the value of the Yes/No field is Yes, there shouldn't be any records in table B or C for that JobID. I think that I can figure out how to add that field to the query, and didn't want to complicate the initial question any more. But since I was posting, I figured I might as well throw it in. )

Thanks,


dz

 

[dhookom]

Try SQL like

SELECT TableA.*
FROM TableA
WHERE (((TableA.JobID) Not In (SELECT JobID FROM TableB  )) AND ((TableA.JobID) Not In (SELECT JobID FROM TableC)));

or

SELECT TableA.*
FROM (TableA LEFT JOIN TableB ON TableA.JobID = TableB.JobID) LEFT JOIN TableC ON TableA.JobID = TableC.JobID
WHERE (((TableB.JobID) Is Null) AND ((TableC.JobID) Is Null));

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]

 

[FoxProProgrammer]

Thanks, Duane! It worked. I added the SQL below to get the last field in the Query and it worked.

And (tbl_jobs.JobID) Not In (SELECT JobID FROM tbl_L_Repair WHERE tbl_L_Repair.NFF = True)

Have a nice day, and gig'em, or is it hook'em?!

dz

 

[dhookom]

Glad to hear you got this working.

gig'em, or is it hook'em

To little of either ;-)

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]

 

[lespaul]

gig'em, or is it hook'em

depends on if you are an Aggie or a Longhorn