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

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

Feb 20 '08 #1

Subscribe Post Reply

1298

mshmyob

904

Expert 512MB

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

Feb 20 '08 #2

rsmccli

Cool; thks for quick reply.

Feb 20 '08 #3

missinglinq

3,532

Expert 2GB

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?

Feb 20 '08 #4

Similar topics

Looping Through Forms: Best Practice

by: David | last post by:

Hello. I am looking for advice on what is "best practice" regarding looping through a form to check its checkboxes and associated data fields. Here is what I am trying to do (Here is the page...

Javascript

What are the benefits and drawbacks of web form and windows form at the layer of Data Access?

by: Hamed | last post by:

Hello I have employed as a developer in a software company that its team uses FoxPro / VB 6.0 / VC++ 6.0 as the developing tools and newly is going to migrate to VS.NET. There is a project...

.NET Framework

What are best resources for converting Access 2003 forms and reportsw/ significant VBA code to web enabled data access pages

by: Bob Alston | last post by:

Looking for the best resources for converting an existing Access 2003 database to the web. Have an application that has many forms and reports, with sub-forms and sub-reports and liberal use of...

Microsoft Access / VBA

Is it possible to cancel events tied to fields depending on what mode form is opened in?

by: allyn44 | last post by:

HI--what I am trying to do is 2 things: 1. Open a form in either data entry mode or edit mode depending on what task the user is performing 2. Cancel events tied to fields on the form if I am in...

Microsoft Access / VBA

What are some ADO & ASP.NET best practices

by: Rashad Rivera | last post by:

What is the best practice for querying, inserting and deleting from a SQL 2000 database from high traffic ASP.NET web forms? Is it to have the SqlConnection & SqlCommand object initialized and...

ASP.NET

Best Practice for Checking if user is logged into application

by: Mike Moore | last post by:

Does anyone have suggestions on the best way to check if a user is logged into asp.net web application? We are not using forms authentication. We are authenticating our users against active...

ASP.NET

What is the best way to display data from a previous form

by: dbuchanan | last post by:

Hello, Windows forms & database What are the considerations when choosing how to display reference information from the calling form? What is recommended? Table1 is the parent of Table2. ...

Visual Basic .NET

Scope of objects and best practices

by: Rob | last post by:

Rather new to vb.net... I am wondering if the following is possible and sounds reasonable... Can connection objects and datasets be defined once (at program load) and be available for the life...

Visual Basic .NET

MenuItems point to two different forms, what is best approach

by: kbutterly | last post by:

All, I have a menu which contains Category as the master and Product as the child. When I click on a Category in the menu, I want one formView control, fvpc, to show, and then when I click on...

ASP.NET

What's the best Timer to use

by: DaTurk | last post by:

Hi, I'm creating an application that will need to use a timer to do a static method call every second. My question, is what would be the best timer to use? I know there are several, but I'd...

C# / C Sharp

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General