Multiple Table PK's In a Single Form?

[NewGuy1]

Hi gang.
This may be 101 to many but my skill set doesnt exactly relate to Db design so need some help from the professionals.

Ive been tasked with creating an Access 2010 Db which contains a listing of all employees in our corporation - detailing HR & IT information per employee.

See attached snapshot of relationships.
To ensure data entry uniformity, I included 6 lookup tables (W, X, 2Y, 2Z) with drop down menu's. The data entry clerk will be entering data into 3 tables (A, B, C). The remaining 2 ticket tables may not be used so for now please ignore.

Table 'B' & 'C' connect to Table 'A' in an 1:1 relationship.
Referential Integrity is being enforced w/ cascade updates/deletes for both relationships.
Therefore, I realize these 3 tables could be combined into one but each deals with different subject matter so decided to separate.

When this goes into production, there will be no need to enter 2+ records in table 'B' or 'C' per employee so a 1:1 would seem to work fine.

QUESTION - Is there a better way to link these 3 tables together or should I just combine them into 1?


What leads me to ask that question is ...
2 problems crop up when testing a designed form:

1>
To this single form, Im adding objects bound to table 'A', 'B', and 'C'. I was hoping to use one form to add/modify/remove records in three tables.

In the form, if I create a record and only input data into the objects bound to table 'A' ... its ok, record saves.
If I input data into objects bound to table 'A' and either/both of the other 2 tables, I get an error "Index or PK cannot be a Null value".

Ok - I get that the PK in tables 'B' and 'C' is still null because I didnt create a bound object in form to the PK's in those two tables (ie ... I dont want the clerk to have to enter employee number three times, once per table).


Is there a way to replicate the value put into the PK in table 'A' by the clerk over to PK's in table 'B' & 'C' anytime BEFORE the clerk saves the record in the form?

Note: This question is irrelavant if there is a better way to relationship 'A', 'B', and 'C' together.


and 2>
The only way to begin with that I could enable record entry into the bound objects to table 'C' was to change the form property 'RecordSet Type' from Dynaset to Dynaset (Inconsistent Updates).

I just dont like the sound of that.


Many thanks ahead of time

 

[way2bord]

A few things:

tbl _b and _c could probably be normalized down to a single table and much fewer fields. Field "Domain" through Field "Kronos" should probably be 2 fields: (Descriptor / Value)

Domain Security Group appears to be sub-lvled in your relationship. How did you do this and what does this imply?


Table relationships 1:1 is fine; though it may minimally impact processing times, the design usually more closely mirrors the business functions. As you're discovering, there are a few drawbacks:
easy fix: add some pseudocode...


CommandButton_OnClick...

set rs = currentdb.OpenRecordset("tbl_a...")
set rss = currentdb.OpenRecordset("tbl_b...")

rs.Add
rs("fieldname") = me.textbox
EmployeeNum = rs("Employee Number")
...
rs.Update

rss.Add
rs("Employee Number") = EmployeeNum
Rss.Update

rs.Close
rss.close

set rs = nothing
set Rss = nothing

 

[Pat Hartman]

You separated the data into three tables because you though the attributes were separate. Then why try to inupt them on a single form? If a single form makes sense, then a single table makes sense. If you stick with the three tables, then use a tabbed form with the "main" table on the main form and separate subforms for the other two sets of data. Also, don't forget that you will need to use left joins for queries that include these tables because the second two will be sparse. That means they won't contain records unless there is data for them to store. If you decide to force the creation of unnecessary rows in the ancillary tables, then that is another clue that they shouldn't have been created in the first place.