Select Query; if statement in one field?

[Susan]

I apologize in advance if this was covered elsewhere, I wasn't quite sure how
to word my search on this.

I have 2 tables with the following fields:
- VendorList ( vendorName, vendorCode, acctRepCode )
- AcctRepList ( repLastName, repFirstName, rep3digitCode, rep2digitCode)

Basically the Account Rep codes used to be 2 digits for a long time, but for
expansion purposes and future purposes we have moved over to using 3 digit
codes. The problem is that, some rep codes have been updated to the 3 digit,
some have been left alone with the 2 digit code.

I'm working in Design mode, so please keep this in mind, as my SQL coding
skills are not that great.

My question is--is there a way to do this:
I wanted to query a VendorList.vendorCode with AcctRepList.repLastName
For AcctRepList.repLastName, I want it to first see if
VendorList.acctRepCode is Null, 2, or 3 digits
if Null, fill field with "Invalid Rep Code"
if 2 digits, try to join with AcctRepList.rep2digitCode
if 3 digits, try to join with AcctRepList.rep3digitCode

All of this--for one field.

Is this possible?


Thanks in advance!
Susan

 

[Ken Snell \(MVP\)]

You'll need a union query that uses tthree queries that join on one or the
other field, or ignore the join. But, you cannot do this in Design view,
though. A Union query can be entered only in SQL view.

Open a new query, select no tables, close the table selection window, and
then click on View icon on toolbar and select SQL View.

This query will get you close:


SELECT VendorList.vendorName, VendorList.vendorCode,
VendorList.acctRepCode, AcctRepList.repLastName,
AcctRepList.repFirstName, AcctRepList.rep3digitCode,
AcctRepList.rep2digitCode
FROM AcctRepList INNER JOIN VendorList
ON AcctRepList.rep2digitCode = VendorList.acctRepCode;

UNION ALL

SELECT VendorList.vendorName, VendorList.vendorCode,
VendorList.acctRepCode, AcctRepList.repLastName,
AcctRepList.repFirstName, AcctRepList.rep3digitCode,
AcctRepList.rep2digitCode
FROM AcctRepList INNER JOIN VendorList
ON AcctRepList.rep3digitCode = VendorList.acctRepCode;

UNION ALL

SELECT VendorList.vendorName, VendorList.vendorCode,
"Invalid Rep Code" AS acctRepCode, NULL,
NULL, NULL,
NULL
FROM AcctRepList
WHERE VendorList.acctRepCode IS NULL;

 

[Ivan Grozney]

Another possibility is to use the IIF function. This can be done in design
view but I prefer to use SQL View...

SELECT DISTINCT VendorList.VendorCode, AccRepList.repLastName,
VendorList.AcctRepCode
FROM VendorList LEFT JOIN AccRepList ON
IIf(IsNull([vendorlist].[acctrepcode]),"Invalid Rep Code",
IIF(len(acctrepcode) = 2,
[vendorlist].[acctrepcode]=[accreplist].[rep2digitcode],
[vendorlist].[acctrepcode]=[accreplist].[rep3digitcode]));


HTH

 

[Ken Snell \(MVP\)]

Sorry, typo in the original post; had wrong table name in the third query's
FROM clause.


SELECT VendorList.vendorName, VendorList.vendorCode,
VendorList.acctRepCode, AcctRepList.repLastName,
AcctRepList.repFirstName, AcctRepList.rep3digitCode,
AcctRepList.rep2digitCode
FROM AcctRepList INNER JOIN VendorList
ON AcctRepList.rep2digitCode = VendorList.acctRepCode;

UNION ALL

SELECT VendorList.vendorName, VendorList.vendorCode,
VendorList.acctRepCode, AcctRepList.repLastName,
AcctRepList.repFirstName, AcctRepList.rep3digitCode,
AcctRepList.rep2digitCode
FROM AcctRepList INNER JOIN VendorList
ON AcctRepList.rep3digitCode = VendorList.acctRepCode;

UNION ALL

SELECT VendorList.vendorName, VendorList.vendorCode,
"Invalid Rep Code" AS acctRepCode, NULL,
NULL, NULL,
NULL
FROM VendorList
WHERE VendorList.acctRepCode IS NULL;

 

[Ken Snell \(MVP\)]

Another possibility is to use the IIF function. This can be done in
design
view but I prefer to use SQL View...

SELECT DISTINCT VendorList.VendorCode, AccRepList.repLastName,
VendorList.AcctRepCode
FROM VendorList LEFT JOIN AccRepList ON
IIf(IsNull([vendorlist].[acctrepcode]),"Invalid Rep Code",
IIF(len(acctrepcode) = 2,
[vendorlist].[acctrepcode]=[accreplist].[rep2digitcode],
[vendorlist].[acctrepcode]=[accreplist].[rep3digitcode]));

ACCESS query design view does not support "non-equi" joins, which is the
type of join represented in your example with the IIf function.

Your suggestion is another way of accomplishing the task, but the query will
run much slower, and it also must be entered/built using SQL view.

 

[Susan]

Thank you so much--this helped a lot, and you were right, it got me real close.
There was one more condition I just thought of checking for.

To display "Invalid Code" your code currently checks to make sure
VendorList.acctRepCode IS NULL. Can you help me determine what else I would
have to add if I wanted to display if the code is NOT null, but does not
match 2digitCode, OR 3digitCode?

 

[Ken Snell \(MVP\)]

You can use a RIGHT JOIN for the first two queries; this way, you'll get
back all VendorList records even if there is not a match on the JOIN. I also
have changed the UNION ALL to UNION so that the query will eliminate
duplicate records.


SELECT VendorList.vendorName, VendorList.vendorCode,
VendorList.acctRepCode, AcctRepList.repLastName,
AcctRepList.repFirstName, AcctRepList.rep3digitCode,
AcctRepList.rep2digitCode
FROM AcctRepList RIGHT JOIN VendorList
ON AcctRepList.rep2digitCode = VendorList.acctRepCode;

UNION

SELECT VendorList.vendorName, VendorList.vendorCode,
VendorList.acctRepCode, AcctRepList.repLastName,
AcctRepList.repFirstName, AcctRepList.rep3digitCode,
AcctRepList.rep2digitCode
FROM AcctRepList RIGHT JOIN VendorList
ON AcctRepList.rep3digitCode = VendorList.acctRepCode;

UNION

SELECT VendorList.vendorName, VendorList.vendorCode,
"Invalid Rep Code" AS acctRepCode, NULL,
NULL, NULL,
NULL
FROM VendorList
WHERE VendorList.acctRepCode IS NULL;

 

[Ken Snell \(MVP\)]

Something that I just thought of.... this RIGHT JOIN solution will bring
back many extraneous records when the 2-digit code is not matching a 3-digit
code, and vice-versa. I doubt that you want all of those records to show,
too.

So try this -- note that this solution will not tell you (in the outputted
records) that the "Invalid Rep Code" is caused by a NULL value in the
VendorList.acctRepCode field or is caused by a nonmatch of the value in the
VendorList.acctRepCode field to either of the 2- or 3-digit code fields in
the AcctRepList table -- also note that this solution assumes that the
various "Rep Code" fields are numeric datatype and not text datatype:


SELECT VendorList.vendorName, VendorList.vendorCode,
VendorList.acctRepCode, AcctRepList.repLastName,
AcctRepList.repFirstName, AcctRepList.rep3digitCode,
AcctRepList.rep2digitCode
FROM AcctRepList INNER JOIN VendorList
ON AcctRepList.rep2digitCode = VendorList.acctRepCode;

UNION

SELECT VendorList.vendorName, VendorList.vendorCode,
VendorList.acctRepCode, AcctRepList.repLastName,
AcctRepList.repFirstName, AcctRepList.rep3digitCode,
AcctRepList.rep2digitCode
FROM AcctRepList INNER JOIN VendorList
ON AcctRepList.rep3digitCode = VendorList.acctRepCode;

UNION

SELECT VendorList.vendorName, VendorList.vendorCode,
"Invalid Rep Code" AS acctRepCode, NULL,
NULL, NULL,
NULL
FROM VendorList
WHERE VendorList.acctRepCode IS NULL;

UNION

SELECT VendorList.vendorName, VendorList.vendorCode,
"Invalid Rep Code" AS acctRepCode, NULL,
NULL, NULL,
NULL
FROM VendorList
WHERE DLookup("rep2digitCode", "AcctRepList", "[rep2digitCode] = " &
VendorList.acctRepCode) IS NULL
AND DLookup("rep2digitCode", "AcctRepList", "[rep3digitCode] = " &
VendorList.acctRepCode) IS NULL



If the three "Rep Code" fields are text, then change the WHERE clause in the
fourth subquery to this:

WHERE DLookup("rep2digitCode", "AcctRepList", "[rep2digitCode] = '" &
VendorList.acctRepCode & "'") IS NULL
AND DLookup("rep2digitCode", "AcctRepList", "[rep3digitCode] = '" &
VendorList.acctRepCode & "'") IS NULL