Compound Query

[JimS]

I feel like an idiot. I know i've done this before, but can't get it into my
head...

I need to find all records in a transaction table which have a discipline ID
that's not found in the Vendor's table of permitted disciplines (In other
words, a plumber puts in hours on an electrical job...it's a data entry
error.)

I thought I could do it with a compound select statement something like the
following:

SELECT VendorID, WorkerID, WorkDate, DisciplineID
FROM tblTransactions
WHERE tblTransactions.DisciplineID NOT IN (Select DisciplineID FROM
xrefVendorDiscipline WHERE xrefVendorDiscipline = tblTransactions.VendorID)

I get a syntax error on the above. Can't find the correct syntax. Any ideas?

 

[Jeff Boyce]

Jim

It appears you are trying to find that NOT IN portion where a table
(xrefVendorDiscipline) is equal to a field (tblTransactions.VendorID).

Are you missing a field to compare on from the first table?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KARL DEWEY]

xrefVendorDiscipline needs a field name associated with it just prior to the
equal sign.

Or try this --
SELECT tblTransactions.VendorID, tblTransactions.WorkerID,
tblTransactions.WorkDate, tblTransactions.DisciplineID
FROM tblTransactions LEFT JOIN xrefVendorDiscipline ON
tblTransactions.DisciplineID = xrefVendorDiscipline.DisciplineID
WHERE xrefVendorDiscipline.DisciplineID Is Null;

 

[JimS]

Karl, Your sql did work, but I don't think it accomplishes the objective... I
need to find records in the Transaction table which have a discipline not
found in the Vendor-Discipline crossreference FOR THAT VENDOR. So, Vendor
Ajax is authorized to provide plumbing and piping services, but not
electrical services or design services.

The cross-reference table has a zero or more (vendorID/disciplineID)
combinations for each vendorID.

The transaction table has a vendorID and a disciplineID. I need to know when
that transaction combination is invalid.

Jim

 

[John Spencer MVP]

SELECT T.VendorID, T.WorkerID, T.WorkDate, T.DisciplineID
FROM tblTransactions as T LEFT JOIN xRefVendorDiscipline as D
ON T.VendorID = D.VendorID
AND T.DisciplineID = D.DisciplineID
WHERE D.VendorID is Null

I think you could make your version work with the following modification.

SELECT VendorID, WorkerID, WorkDate, DisciplineID
FROM tblTransactions
WHERE tblTransactions.DisciplineID NOT IN (Select DisciplineID
FROM xrefVendorDiscipline
WHERE xrefVendorDiscipline.VendorID = tblTransactions.VendorID)



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

Try this --
SELECT tblTransactions.VendorID, tblTransactions.WorkerID,
tblTransactions.WorkDate, tblTransactions.DisciplineID
FROM tblTransactions LEFT JOIN xrefVendorDiscipline ON
tblTransactions.DisciplineID = xrefVendorDiscipline.DisciplineID AND
tblTransactions.VendorID = xrefVendorDiscipline.VendorID
WHERE xrefVendorDiscipline.DisciplineID Is Null;