By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,189 Members | 1,028 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,189 IT Pros & Developers. It's quick & easy.

Row Source dependant on a field from the current row

P: n/a
Hi,

I have a table called Appointment where I store a persons appointment.
One of the fields is called Hospital and points to a table listing a
number of hospitals. A second field is called Department and points to
a list of departments.

Lets say I have 3 hospitals - Stockholm, Gothenburg and Malmo. And
lets say I have 3 Departments - Emergency, Non Emergency, Not
Applicable.

I'd like my table to only allow Emergency and Non Emergency to be
selected when Stockholm is the chosen hospital, and Not Applicable the
only available option available when the other two hospitals are
chosen.

So, at the moment in my table, I have the following row source for the
Department field in my Appointments table -

SELECT DISTINCTROW tblDepartment.* FROM tblDepartment ORDER BY
tblDepartment.ID;

But how can I change this so that it also checks the Hospital value
for the current row?

Thanks for your help,

Barry.

May 9 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
The relational way to do that would be to create a link table
'aHospitalDepartment' that joins the base table 'aHospital' with the
allowed values in the base table 'aDepartment'.

The link table needs to store at least the PrimaryKeys for each
allowed hospital and department, and should also store any data
related to that particular Hospital/Department combination, such as
'HoursOfOperation' or other dependent fields.

<pseudocode>

CREATE TABLE aHospitalDepartment{hdHospitalID as Long (PK),
hdDepartmentID as Long (PK), hdHours as Text(50)}

</pseudocode>

This assuming you are using an autonumber or Long value as the Primary
Key for the aHospital and aDepartment base tables.

You would then want to add a row in this link table for each valid
Hospital / Department combination:

Stockholm|Emergency|24 hours
Stockholm|Non-Emergency|7 am to 7 pm
Gothenburg|Not Applicable|
Malmo|Not Applicable|

in actuality, given the table stucture above, you would be storing
Long values representing each of the hospitals and departments so the
actual data stored would be:

1|1|24 hours
1|2|7 am to 7 pm
2|3|
3|3|

assuming Hospital.Stockholm = 1 and Department.Emergency = 1 etc...

Then alter your query to join the hospital to Department using the
link table. It's easy to do in the query builder but the result SQL
will look something like:

SELECT aHospital.hoName, aHospital.hoAddress,
aHospitalDepartment.hdHours, aDepartment.dpName,
aDepartment.dpRoomNumber FROM aHospital INNER JOIN (aDepartment INNER
JOIN aHospitalDepartment ON aHospitalDepartment.hdDepartmentID =
aDepartment.hoID) ON aHospital.hoID =
aHospitalDepartment.hdDepartmentD
ORDER BY aHospital.hoName;

note the nested joins.

The result set on executing this query will contain one row for each
valid Hospital/Department combination in the link table.

Ron, King of Chi
On May 9, 2:24 am, b...@yahoo.com wrote:
Hi,

I have a table called Appointment where I store a persons appointment.
One of the fields is called Hospital and points to a table listing a
number of hospitals. A second field is called Department and points to
a list of departments.

Lets say I have 3 hospitals - Stockholm, Gothenburg and Malmo. And
lets say I have 3 Departments - Emergency, Non Emergency, Not
Applicable.

I'd like my table to only allow Emergency and Non Emergency to be
selected when Stockholm is the chosen hospital, and Not Applicable the
only available option available when the other two hospitals are
chosen.

So, at the moment in my table, I have the following row source for the
Department field in my Appointments table -

SELECT DISTINCTROW tblDepartment.* FROM tblDepartment ORDER BY
tblDepartment.ID;

But how can I change this so that it also checks the Hospital value
for the current row?

Thanks for your help,

Barry.

May 9 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.