Hi All,
Sorry, I don't know if people normally post normalization questions here or not, but a search didn't provide anything. Anyway, I have the following:
As you can see, the underlined fields are primary keys and the fields in the {} are the repeating structures. In the current system, we've had a few problems where error counts were reducted in error - so we'd like to create an audit trail in the new system. As you can see, this is what I came up with for the 1NF. The 2NF is the exact same (by my "calculations" anyway) and the 3NF just eliminates the Percentage because it can be calculated using AuditTotal and ErrorCount
I've created the employees table and the audits table (the second table) and created the relationships in access. Obviously, each record in the audits table can have multiple corresponding records in the Updates table. But I can't represent this in the relationships in access. When I drag the EmployeeID and AuditDate fields from one table to the other, the Relationship Type says Indeterminate. If I click OK it says No unique index found in the referenced field of the primary table
How do I get around this, do I have to create an AuditID instead and use that in the Updates table? And if so, won't that mean that EmployeeID and AuditDate would no longer be required as the primary key for the Audits table? I don't really want to do that, because then there's the possibility of users entering the same EmployeeID and AuditDate twice in error. At least if they were the primary key, they couldn't do that.
Sorry about such a long post, thanks in advance for any help
Martin
Sorry, I don't know if people normally post normalization questions here or not, but a search didn't provide anything. Anyway, I have the following:
As you can see, the underlined fields are primary keys and the fields in the {} are the repeating structures. In the current system, we've had a few problems where error counts were reducted in error - so we'd like to create an audit trail in the new system. As you can see, this is what I came up with for the 1NF. The 2NF is the exact same (by my "calculations" anyway) and the 3NF just eliminates the Percentage because it can be calculated using AuditTotal and ErrorCount
I've created the employees table and the audits table (the second table) and created the relationships in access. Obviously, each record in the audits table can have multiple corresponding records in the Updates table. But I can't represent this in the relationships in access. When I drag the EmployeeID and AuditDate fields from one table to the other, the Relationship Type says Indeterminate. If I click OK it says No unique index found in the referenced field of the primary table
How do I get around this, do I have to create an AuditID instead and use that in the Updates table? And if so, won't that mean that EmployeeID and AuditDate would no longer be required as the primary key for the Audits table? I don't really want to do that, because then there's the possibility of users entering the same EmployeeID and AuditDate twice in error. At least if they were the primary key, they couldn't do that.
Sorry about such a long post, thanks in advance for any help
Martin