Assistance with SQL Statements & conditional select

[Valiant]

I am currently developing a database to record and monitor audit
recommendations. I am in the process of developing the table for the
creation and setup of new audits. Unfortunately, the company I work for has
quite a complex structure, so it makes it quite difficult for users to assign
audit actions:

(i) to the correct Group company;
(ii) the correct business stream within that company; &
(iii) the correct department within the relevant business stream.

I have set up four tables as follows:

TblAudit

AuditID
EntityID*
BusinessStreamID*
DepartmentID*
AuditYearID
AuditTypeID
AuditTitle
StartDate
EndDate
ReportDate
AuditeeID
ManagerID
AuditorID

TblEntity

EntityID
CompanyName

TblBusinessStream

BusinessStreamID
BusinessStreamName
EntityID

TblDepartment

DepartmentID
DepartmentName
BusinessStreamID

The bottom three tables are related to the TblAudit by virtue of the ID
columns.

What I wanted to do was to have a drop-down for each of the asterixed
columns in the TblAudit table above.

For example: for EntityID, the following SQL Statement proved adequate
(Lookup, Combo Box, Table Query etc.):

SELECT TblEntity.* FROM TblEntity ORDER BY TblEntity.CompanyName;

This provided a drop-down list of all the Entity names sorted alphabetically
and then upon selection, inserted the correct value in the EntityID column.
Great

However, what I wanted for the BusinessStreamID and DepartmentID was a
similar approach, but limiting the selection determinate upon the selection
of Entity (i.e. if selecting Company A, then only the names of the business
streams for Company A will be shown in the next column, and similarly for
Department, determinate upon the selection of the Business Stream name).

I have had no success in developing a SQL statement which will satisfy this
approach. At present, I am trying to do this directly in the table – ideally
I would like to stick with this approach, but hopefully someone can provide
an easier way when I come to design the form or provide me with the correct
SQL Statement?

Thanks in advance for any assistance!!

 

[Tom Wickerath]

Hi Valiant,

Your 2nd to the last paragraph indicates that you want what is known as
synchronized combo boxes. However, this cannot be done directly in the table,
since tables provide no events to respond to. Try the following examples:

Limit content of combo/list boxes
http://www.mvps.org/access/forms/frm0028.htm

How to synchronize two combo boxes on a form
http://support.microsoft.com/?id=289670


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I am currently developing a database to record and monitor audit
recommendations. I am in the process of developing the table for the
creation and setup of new audits. Unfortunately, the company I work for has
quite a complex structure, so it makes it quite difficult for users to assign
audit actions:

(i) to the correct Group company;
(ii) the correct business stream within that company; &
(iii) the correct department within the relevant business stream.

I have set up four tables as follows:

TblAudit

AuditID
EntityID*
BusinessStreamID*
DepartmentID*
AuditYearID
AuditTypeID
AuditTitle
StartDate
EndDate
ReportDate
AuditeeID
ManagerID
AuditorID

TblEntity

EntityID
CompanyName

TblBusinessStream

BusinessStreamID
BusinessStreamName
EntityID

TblDepartment

DepartmentID
DepartmentName
BusinessStreamID

The bottom three tables are related to the TblAudit by virtue of the ID
columns.

What I wanted to do was to have a drop-down for each of the asterixed
columns in the TblAudit table above.

For example: for EntityID, the following SQL Statement proved adequate
(Lookup, Combo Box, Table Query etc.):

SELECT TblEntity.* FROM TblEntity ORDER BY TblEntity.CompanyName;

This provided a drop-down list of all the Entity names sorted alphabetically
and then upon selection, inserted the correct value in the EntityID column.
Great

However, what I wanted for the BusinessStreamID and DepartmentID was a
similar approach, but limiting the selection determinate upon the selection
of Entity (i.e. if selecting Company A, then only the names of the business
streams for Company A will be shown in the next column, and similarly for
Department, determinate upon the selection of the Business Stream name).

I have had no success in developing a SQL statement which will satisfy this
approach. At present, I am trying to do this directly in the table – ideally
I would like to stick with this approach, but hopefully someone can provide
an easier way when I come to design the form or provide me with the correct
SQL Statement?

Thanks in advance for any assistance!!