What's the best way to do this? Forms.

[rsmccli]

Creating new workforce database (many workers, about 10 companies). Boss wants to use SSN as PK, but I think that may be a bad idea. They want to allow for multiple, duplicate SSNs (in case of same worker being hired by different companies), but only one of the duplicate entries will be allowed to have an "active" checkbox set to true at any given time (the workers current company affiliation).

Anyway so my question is this. What would be the easiest way to prevent more than one of these check boxes from being checked? Say there is one guy SSN 555-55-5555 who has worked for 3 companies. So that SSN is in the DB three times and one 'active' box is checked. When someone tries to check a different one, i want it to stop and ask the user "are you really, really sure?". I was thinking in the before update event of the input form I would have access loop through and look for any of the same ssn having a 'true' checkbox but that wouldn't stop someone that is actually going through the tables themselves...

any ideas welcome thks

[mshmyob]

First and foremost - A primary key MUST be unique. It cannot repeat in a table.

What you need are 3 tables.

1. tblWorker - You can use SSN as PK here
2. tblCompany - Create your own PK - CompanyID
3. tblWorkerDetail - PK = SSN AND CompanyID

tblWorker links to tblWorkerDetail through SSN and tblCompany links to tblWorkerDetail through CompanyID - Therefore tblWorkerDetail has a COMPOSITE primary key consisting of SSN and CompanyID

This setup will allow a worker to work for any number of companies and Each company can have many workers.

Creating new workforce database (many workers, about 10 companies). Boss wants to use SSN as PK, but I think that may be a bad idea. They want to allow for multiple, duplicate SSNs (in case of same worker being hired by different companies), but only one of the duplicate entries will be allowed to have an "active" checkbox set to true at any given time (the workers current company affiliation).

Anyway so my question is this. What would be the easiest way to prevent more than one of these check boxes from being checked? Say there is one guy SSN 555-55-5555 who has worked for 3 companies. So that SSN is in the DB three times and one 'active' box is checked. When someone tries to check a different one, i want it to stop and ask the user "are you really, really sure?". I was thinking in the before update event of the input form I would have access loop through and look for any of the same ssn having a 'true' checkbox but that wouldn't stop someone that is actually going through the tables themselves...

any ideas welcome thks

[rsmccli]

Cool; thks for quick reply.

[missinglinq]

Typically in this kind of app the SSN would be the Primary Key of the employee table, where you hold info like the employee's name, address, DOB, etc, and then used in other tables, such as, say, a table to hold positions filled, as a Foreign Key. It could only only appear in the employee table once, but multiple times in other tables.

Welcome to TheScripts!

Linq ;0)>

BTW, your photo looks familiar! Didn't I see you out by the trash cans last week?