Looking for alternative to Look-up tables fields.

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

re: Looking for alternative to Look-up tables fields.

"Simeon Nevel" <snevel@sonic.net> wrote in message
news:Xyglb.34048$dk4.908140@typhoon.sonic.net...[color=blue]
> 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.[/color]

Here is where you have gone wrong. One should never use tables for data
entry so simplifying this is never an issue. All interfaces to the data
should be with Forms and Reports. Tables are for structure only. Once you
move to doing your data entry with a form then all of these issues you have
go away. You can then use ComboBoxes on your forms to provide the lookup
functionality.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com