lookup table based on two criteria

  • Thread starter Thread starter starlingseven via AccessMonster.com
  • Start date Start date
S

starlingseven via AccessMonster.com

Hello:

I don't know if this is possible, but I am trying to create a lookup table
using two criteria - so it would be "if a value in column A equals a value in
column 1 in the lookup table and if a value in column B equals a
corresponding value in column 2 of the lookup table, then spit out the
corresponding value in column 3". anyone know if this is possible?

thanks,

mya
 
It's possible. But, can you tell us the situation where you'll be doing
this -- in a query? using a DLookup function? Some other setup?
 
Ken:

my ultimate goal is to have a query that compares a table of student IDs,
their starting year and quarter ("year-quarter") & their demoes and a table
of student IDs with all the subsequent year-quarters the students have
attended. What i need to have a query spit is the number and percentage of
students who have attended one quarter, two quarters... up to nine quarters -
and ultimately separated out by different demographics - and I'm thinking the
best way of doing this is with a comparison of the starting and ending year-
quarters - i'm thinking from a separate lookup table that can look up the
starting year-quarter and ending year-quarter, to spit out a value that
corresponds to the two values? I tried looking up how to do this in MS
Access "help", which as usual wasn't very helpful -

Thanks,

mya
 
I don't see any benefit to a lookup table for your concept. I believe that
all you want to achieve can be done using just queries/reports.

Your concept assumes that a student attends consecutive quarters -- is that
always going to be true?

Assuming that it's not, your concept probably would be better done using a
query that selects students who've attended just one quarter, or just two
quarters, etc. and then displays data for those students.

You can use the Count aggregate function ina totals query to find out how
many quarters a student has attended, and then to use the result from the
Count function as the criterion in a HAVING clause to select the desired
number of quarters.

I don't know what level of experience you may have with ACCESS and queries,
but here is a generic example of how you can show quarter/year attended
records for a student if you wanted the results for students who'd attended
four quarters:


SELECT S.StudentID, Q.QuarterNumber, Q.YearNumber
FROM Students AS A INNER JOIN QuartersAttended AS Q
ON S.StudentID = Q.StudentID
WHERE S.StudentID IN
(SELECT SS.StudentID
FROM QuartersAttended AS SS
GROUP BY SS.StudentID
HAVING Count(*) = 4);

--

Ken Snell
<MS ACCESS MVP>
 
Back
Top