Pulling data from one form to another

[tjones]

I have a table/form [tblStdAdv]\ [frmStdAdv] that contains all advisers a student had during their program. that form pulls from refAdv which has:
Type
1- initial
2- current
3- previous

and then an Adviser field, per record

I need to pull the adviser onto the student's main page [tblStdInfo]\[frmStdInfo] either "current" (if they have had changes) or "initial" (if they went through without an adviser change) .

I would like to pull the info into the main page so that I only have to change, or add to, the one form\table and the main form stays up to date.

i tried using a query and was able to pull either "2" or "1" or "both" but could not make it look for "2" Current first and if not found then use "1" Initial.

 

[jdraw]

Can you post a copy of the database? (enough data to show the problem and remove anything confidential/private first)

 

[CJ_London]

Please provide details of your tables (table name, field names and types) plus the sql of your query that did not work