Hi,

I am trying to figure out how I can use the "If Then Else Statements" to conditionally manipulate data in a database.

Here is the scenario: I have a database of 6 groups of expert panels each group represented by a table in an access database. Each of the panels has a chair, vice and secretary.
1. I want to be able to update the tables and determine whether one is still a chair, vice or secretary (this bit is ok I can do this) the problem comes when I want to display details of the three people with positions on a contact us page.

Simply put I seek your help with the following logic:

Select from "panel table in mydatabase" order by position asc
if position="chair" then display
if position="Vice chair" then display
if position ="secretary" then display
else
"No office bearers appointed this year."

others might want to correct this <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle> or make other suggestions . . .

I'm not sure that you can do what you are trying to do all in an sql type query, it might be a case of designing your tables to suit

table design - you have people, they can belong to panels each person can belong to zero, one or many panels (I assume that) and within each panel they can have zero or one office, to me that suggests a table of people, table of offices with unique id numbers for each, table of panels with a unique id for each.

Now you can have a table of memberships linking people with the panels to which they belong and their offices.

Table person
name, details, person unique id

table memberships
membership id no, person unique id, panel id, office id in this panel

table offices
office name, unique id

set this table up so the office ids are &gt; 0 and in ascending order 1=chair, 2=vice, 3=sec
this also allows you to change the office names and add others easily

your query might then look something like this . . .

SELECT list of required fields FROM TABLE person INNER JOIN memberships ON person unique id INNER JOIN offices ON office id WHERE panel id = name of required panel AND office id in this panel &gt; 0 ORDER BY office id ASC

you might not need that last bit about office id &gt;0 it just depends on what you want to select.

Then if you get zero rows returned you have no officers this year.

Hope that helps and doesn't confuse, I'll be interested to see what others have to say too.

my pseudocode might be wonky but the idea is ok.