DLookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have to create a lookup that will come back with the following Form & Color values:

Subj CRS Form Color

ENG 1 Pink
ENG 265 4 Pink
ENG 266 4 Pink
WR 2 Yellow
WR 320 3 Yellow
WR 321 3 Yellow
FLR 1 Lavender

All English courses use pink paper. All English courses except 265 & 266 use form #1. English 265 & 266 use form 4. All Writing courses use yellow paper. All Writing courses except for 320 & 321 use form 2. WR 320 & 321 use form 3. All FLR use form 1 & Lavender paper.

CRS numbers for each of ENG, WR, & FLR can run from 100 to 699. I'd prefer to not have to create a table with 1800 records in it (600 for each possible course number for each subject area). How can I get a query to give me the color & form number for the courses that show up in my "course" query without filling in all the course numbers?
 
Hi Donna,

I think there are many ways that you could approach this,
but most or all would involve checking your lookup table
to see if a given Subj/CRS combination exists in the
table - if so use the corresponding Form # and Color, if
not then lookup the default values.

For the default, you may want to consider using a CRS
number that is out of the valid range that you will ever
use. It will probably be less troublesome to look up
this record if it has an entry of something like 9999
rather than null.

There are all combinations of iifs/Dlookups/Subqueries
that could accomplish what you want. One possible way
would be to use DLookup inside an Nz() Function to return
the CRS ID if it exists in combination with the given
subject in the lookup table, otherwise to return the
default value CRS in cases where the DLookup returns
Null, such as the following:

Nz(Dlookup("[CRS]","YourLookupTable", "Subj = '" &
YourQuerySubjField & "' AND CRS = " &
YourQueryCRSField),9999)

If you put this nested in the Dlookup for say the FormNo,
it would look something like this:

Dlookup("[Form]","YourLookupTable", "Subj = '" &
YourQuerySubjField & "' AND CRS = " & Nz(Dlookup
("[CRS]","YourLookupTable", "Subj = '" &
YourQuerySubjField & "' AND CRS = " &
YourQueryCRSField),9999))

This is all untested, so I'm sure there is a decent
chance I have a typo in the above, but hopefully you will
get the idea.

Other ways of doing the same thing would be to use iif()
function statements to check to see if a DLookup on the
Subj/CRS returns a non-null value, if so use that DLookup
value, otherwise use the default CRS # in the DLookup.

You could also break the calculation into chunks by
having a column just showing the result of the DLookup
for the Sub/CRS (or the result of that nested in the Nz()
function), then having other columns referencing that
result in the calc for the appropriate Form No and Color.

Similarly, you could use subqueries in place of the
DLookups, but if you aren't familiar with them the
DLookup will probably be easier to work with (although
not necessarily as the concatenation of the condition
gives many people problems).

Hope this helps. Post back if you have further questions.

-Ted Allen
-----Original Message-----
I have to create a lookup that will come back with the following Form & Color values:

Subj CRS Form Color

ENG 1 Pink
ENG 265 4 Pink
ENG 266 4 Pink
WR 2 Yellow
WR 320 3 Yellow
WR 321 3 Yellow
FLR 1 Lavender

All English courses use pink paper. All English courses
Click to expand...
except 265 & 266 use form #1. English 265 & 266 use form
4. All Writing courses use yellow paper. All Writing
courses except for 320 & 321 use form 2. WR 320 & 321
use form 3. All FLR use form 1 & Lavender paper.
CRS numbers for each of ENG, WR, & FLR can run from 100
Click to expand...
to 699. I'd prefer to not have to create a table with
1800 records in it (600 for each possible course number
for each subject area). How can I get a query to give me
the color & form number for the courses that show up in
my "course" query without filling in all the course
numbers?
 
Back
Top