One Form to add new records to three tables?

[SnailFever]

This should be easy, but I'm stumped. Iv'e got an employee table with Social Security Number (SSN) as the primary key. Two other tables (Classes & Certifications) are simple one-to-one relationships with the Employee table, both with SSN as the primary key. Suppose we get a new employee. How can I build a single form that adds new records to all three? Do I have to manually type the SSN into each of the tables?

 

[BNPMike]

No you just create a query joining each table. Access will let you update all three together. (This type of view is updatable in Access) mike.stephens@bnpparibas.com

 

[JoeMiller]

The question I have is why are you using a one-to-one relationship for the related info and not just one table with all the fields needed? Joe Miller
joe.miller@flotech.net

 

[khood]

SnailFever, do you mean one-to-many? An employee can have multiple classes and certifications, right?

If it is one-to-many, then it is probably best to use either a tabbed interface or subforms to relate the many side. During data entry, if there are usually very few classes or certs. another option is to open a form (via command button) & pass the SSN to the separate form.

Ken

 

[Guest_imported]

Yes, it really is a one-to-one relationship. It ought to be a large flat table, but it's not. Two reasons: First, this thing has grown so wide that it has too many fields for Access to handle in one table. (Imagine a field for every module for every one of many training courses - all as check-boxes & date fields. Not the most efficient design, but it's inherited.) Second, keeping a separate table for each piece makes it modular. The database is about to be shipped to other units, and some don't want all the pieces.

BNPMike, the Query join idea sounds good, and I'll try it. Unfortunately, this problem must not occur often and the texts I'm using don't have much on using query joins to add records, just to update. I'll let you know how it works. Thanks.

 

[JoeMiller]

Queries have the same 255 field limit as tables, so you're going to have trouble if you need to combine these together for presentation on one form. I highly suggest that you look into restructuring the database because it will be a headache in its current incarnation.
Joe Miller
joe.miller@flotech.net

 

[BNPMike]

Using a query to add records is one of the basic tools in your toolbag. It is certainly covered in the Microsoft Manual (yes they used to do them in the old days). If your text doesn't cover it, chuck it away.

You will not have a problem with numbers of fields because you are never going to present 255 fields at once to a user (please say you're not- what's he done to upset you?). All you need as a minimum are the key and foreign key fields. mike.stephens@bnpparibas.com