Action Queries . . . i think

[Student Database Disaster!]

So i am attempting to create a complex query and i can't figure it out. The
database i am working with has participants. The records in our database
refer to students. For each record in our database there is information on
both the student's home and work addresses and which they prefer us to use.
I would like to create a query that would show me a list of participants
(filtered by the course they are in, i know how to do this part) and only
their preferred addresses. Not one or the other or both, just their
preferred one. Is this possible? Is this something where i need to use a
calculated column or field? Please help!

 

[Michel Walsh]

SELECT ..., iif( preferred='home', homeAddress, workAddress), ...
FROM ...


Note that I assume the field stating which address to us is called
preferred, and hold a string, either 'home', either something else (so, work
address).



Hoping it may help,
Vanderghast, Access MVP


"Student Database Disaster!" <Student Database
[email protected]> wrote in message
news:[email protected]...

 

[Student Database Disaster!]

First THANKS!

Second, yes your assumptions were correct. The field is PREFERRED_ADDRESS,
and it holds either 'business' or 'home'. The address fields are formated
for business address as W_ADDRESS, W_CITY. . . . and for home H_ADDRESS,
H_CITY. And the addresses are held in the 'Student' table.

Third, i am kinda a newbie at this whole SQL stuff so can you help me a
little more? Here is what i have, but i know it's not quite right yet. I
know it has missing operators but i don't exactly know what that means or
where to put them.

SELECT H_ADDRESS H_CITY H_STATE H_ZIP W_ADDRESS W_CITY W_STATE W_ZIP,
iif(PREFERRED_ADDRESS='home',SELECT H_ADDRESS H_CITY H_STATE H_ZIP,
W_ADDRESS W_CITY W_STATE W_ZIP),FROM STUDENT
STUDENT_COURSE.DATE_OF_REGISTRATION
FROM STUDENT INNER JOIN STUDENT_COURSE ON STUDENT.STUDENT_ID =
STUDENT_COURSE.STUDENT_ID
WHERE ((STUDENT_COURSE.OFFERING_ID)=[Offering ID]) AND ((STUDENT_COURSE.[CD
SENT]) Is Null));

 

[John W. Vinson]

Second, yes your assumptions were correct. The field is PREFERRED_ADDRESS,
and it holds either 'business' or 'home'. The address fields are formated
for business address as W_ADDRESS, W_CITY. . . . and for home H_ADDRESS,
H_CITY. And the addresses are held in the 'Student' table.

Third, i am kinda a newbie at this whole SQL stuff so can you help me a
little more? Here is what i have, but i know it's not quite right yet. I
know it has missing operators but i don't exactly know what that means or
where to put them.

You only need (and should only use!) one SELECT statement. The IIF function
returns a value, so you can just use the IIF statement to determine which
field you want to see:

SELECT
IIF(PREFERRED_ADDRESS= "home", H_ADDRESS, W_ADDRESS) AS ADDRESS,
IIF(PREFERRED_ADDRESS="'home", H_CITY, W_CITY) AS CITY,
IIF(PREFERRED_ADDRESS="'home", H_STATE, W_STATE) AS STATE,
IIF(PREFERRED_ADDRESS="'home", H_ZIP, W_ZIP) AS ZIP
STUDENT_COURSE.DATE_OF_REGISTRATION
FROM STUDENT INNER JOIN STUDENT_COURSE
ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID
WHERE ((STUDENT_COURSE.OFFERING_ID)=[Offering ID]) AND ((STUDENT_COURSE.[CD
SENT]) Is Null));

 

[Student Database Disaster!]

Thanks so much!

Second, yes your assumptions were correct. The field is PREFERRED_ADDRESS,
and it holds either 'business' or 'home'. The address fields are formated
for business address as W_ADDRESS, W_CITY. . . . and for home H_ADDRESS,
H_CITY. And the addresses are held in the 'Student' table.

Third, i am kinda a newbie at this whole SQL stuff so can you help me a
little more? Here is what i have, but i know it's not quite right yet. I
know it has missing operators but i don't exactly know what that means or
where to put them.

You only need (and should only use!) one SELECT statement. The IIF function
returns a value, so you can just use the IIF statement to determine which
field you want to see:

SELECT
IIF(PREFERRED_ADDRESS= "home", H_ADDRESS, W_ADDRESS) AS ADDRESS,
IIF(PREFERRED_ADDRESS="'home", H_CITY, W_CITY) AS CITY,
IIF(PREFERRED_ADDRESS="'home", H_STATE, W_STATE) AS STATE,
IIF(PREFERRED_ADDRESS="'home", H_ZIP, W_ZIP) AS ZIP
STUDENT_COURSE.DATE_OF_REGISTRATION
FROM STUDENT INNER JOIN STUDENT_COURSE
ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID
WHERE ((STUDENT_COURSE.OFFERING_ID)=[Offering ID]) AND ((STUDENT_COURSE.[CD
SENT]) Is Null));