Re: Select Query; if statement in one field?

Tech-Archive recommends: Fix windows errors by optimizing your registry

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;

--

Ken Snell
<MS ACCESS MVP>



"Susan" <Susan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D0B65864-10B3-4A86-AE32-6689F2D5055C@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Select Query; if statement in one field?
    ... FROM AcctRepList INNER JOIN VendorList ... UNION ALL ... A Union query can be entered only in SQL view. ... VendorList.acctRepCode is Null, 2, or 3 digits ...
    (microsoft.public.access.queries)
  • Re: Help with count query
    ... rather than having to normalize with a union query? ... the union query would perform faster. ... Some columns will have digits from 1 to 5, ...
    (microsoft.public.access.queries)
  • Re: Select Query; if statement in one field?
    ... FROM AcctRepList INNER JOIN VendorList ... UNION ALL ... A Union query can be entered only in SQL view. ... VendorList.acctRepCode is Null, 2, or 3 digits ...
    (microsoft.public.access.queries)
  • Re: Transpose Rows to Columns in Access Query
    ... survey as an interim tool to gather succession data before we implement ERP. ... This was my first time writing a code for a union query and I believe this ... If it's JUST as you describe a Normalizing Union query will do the job. ... matter) temporary table, which I'll call tblTemp. ...
    (microsoft.public.access.queries)
  • Re: Query Question
    ... I suggested a UNION query using the function as the solution. ... Public Function fGetSection(StrIn, iSection as Integer) ... fGetSection = Null ...
    (microsoft.public.access.queries)