Looking for alternative to Look-up tables fields.

[Simeon Nevel]

As a personal exercise, I'm trying to create a call tracking DB for
my own use at work.

Here's an abbreviated version of the database layout:

Company:
CompanyID - Primary Key (Text Length 4)
CompanyName
OtherData

Contacts:
CompanyID - Part 1 of Primary Key, is also a foreign key
ContactID - Part 2 of Primary Key (AutoNumber)
ContactName
OtherData

Incident:
CompanyID - Part 1 of Primary Key, is also foreign Key
IncidentID - Part 2 of Primary Key (AutoNumber)
ContactID - Foreign Key
OtherData

All of the obvious relations have been defined.

I'm looking for a way to simplify data entry into the Incident table.

I can easily make Incident.CompanyName a look-up table using the wizard.
That way I can select from a list of CompanyName(s) and get the
CompanyID entered into the Incident record.

In order to enter Incident.ContactID, I'd like to be able to select
from a drop-down list of ContactName such that all the ContactName(s)
"belong" to the Incident.CompanyID entered for that record.

I've spent the best part of 3 hours trying to fool the wizard into
doing what I want without sucess. I've even tried to manipulate
the SQL underlying the look-up field without sucess.

I realize that look-up fields are frowned upon for very good
reasons.

If someone can point me in the direction of a solution to my
Incident.ContactID
look-up problem or a different approach (without look-up fields)
I'd *really* appreciate it.

Using Access 2002 and I'm 1/2 way thru my first class in Access.
I've taken 2 classes in VB so I'm familiar (somewhat) with how to
program in VB/VBA.

adTHANKSvance!

Simeon

 

[John Vinson]

I'm looking for a way to simplify data entry into the Incident table.

Use a Form. Table datasheets are really very limited, and good
primarily for debugging. As you have seen, you can't really use two
subdatasheets.

Create a Form based on the Company table, with a Subform based on
Contacts and a separate Subform based on Incidents. Make the CompanyID
the master/child link field for both; the rest of the data will fill
in and maintain the link.