Forms :: Primary Key As Multiple Foreign Keys In Single Table

Mar 26, 2013

I'm developing a simple sporting records db. I have a 'Competitor' table listing competitors as follows: CompetitorPK, Name, Division, Club. I have a 'Contest' table listing contests as follows: ContestPK, Competitor1_FK, Competitor2_FK, Winner_FK, Score etc. My question is have I modelled tables correctly i.e. CompetitorPK will appear in three columns of Contest table. How do I define this relationship? What alternative is there to what I have done.I intend to use forms to populate both tables (independantly obviously).

View Replies


ADVERTISEMENT

Tables :: One Primary Key To Multiple Foreign Keys In The Same Table?

Aug 29, 2013

Right now, I have 4 related tables. There's a table with companies, one with people involved with companies, a table linking the two by having foreign keys of the company number and people names, and a table that indicates directors and their alternates.

Since there's a one-to-many relationship for companies/people to company-peopleID (A person can run multiple companies, a company has multiple directors, it's easier this way), a person's name can appear multiple times, as can a company, within that table.

In a company, a director may or may not have 1 and only 1 alternate director to him/herself. So, I thought the easiest way was to put an autonumber in the Company-personID table and have a table (alternates) that had two fields, "alternate" and "director", both using that autonumber to link them. However, it appears as though I can't link the same primary key twice to two foreign keys of the same table.

[URL]

View 3 Replies View Related

One Primary Key + Multiple Foreign Keys...help Creating Form With Option

May 4, 2006

I need some help creating a simple datebase to store my documents and other knowledge objects. There are two types of

knowledge objects I need to store: Documents and Questions/Answers. For this reason I have created three tables: Index,

Question, and Document.

The problems I am encountering is that the ID field in the Index Table is the Primary and relates to the ID field in the

other two tables. So basically when I am entering information it can either be a question or document. I want to create a

for so that when its a question that I am entering it records the information in the question table and when its a document

I am entering it records the information in the document table.

I'm a beginner at using access and don't know VB or SQL. Any help would be appreciated.


Table Structure

Index Table
ID (Primary Key)
Link
Type
Source
Reference

Question Table
ID (Foreign Key)
Question
Answer

Document Table
ID (Foreign Key)
Title
Summary
Notes

View 1 Replies View Related

One Primary Key In One Table Linked To Two Foreign Keys In Two Different Tables!!!

Sep 22, 2007

Hi there,

I am trying to model my database to create relationships between different tables in my database. During the process of doing so I ended up having one primary key in table A linked with to foreign keys in two different tables ( table B & table C) and both of those two table are junction tables to break out many to many between many other tables

Now for some reason!!! I feel that there is something wrong with my logic mapping and modeling of the relationships between tables due the fact of having one primary key linked to two foreign keys in two different tables :(

Is my intuition is right? should such case be considered as indicative of wrong modeling of relationships between tables in a single database
And if so what is the disadvantages of that link (talking down the road) when the database if fully populated? :confused:

By the way I am new member and new to database, so please take it easy on me :o

Many thanks

View 2 Replies View Related

Foreign Key To A Table With Multiple Primary Keyfields???

May 30, 2005

I`m designing a database in MS Access '97.

A lot of the tables I`m using have multiple primary key fields.

How is it posible to create one relationship between 2 fields in table A and 2 primary key fields in table B and force integrity?

thx in advance

View 4 Replies View Related

Tables :: Multiple Parents Table Linked To Child Table - Primary Keys

May 28, 2015

I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...

*Child_ID
Parent_First_Name
*Primary/Secondary/Other

View 8 Replies View Related

Two Foreign Keys In One Table.

Apr 20, 2006

I'm not new to access, and I understand normalization at pretty much all its levels, but right now I'm curious about a situation that I have just come across. The first time I've been in something like this, so I thought I would ask.

This is the setup.

I have employees. They are apart of a shift and a budget. Shifts and budgets are completely independent of each other.

The database needs to keep track of shifts and budgets over time. Therefore, 1 to many relationship to shift table and budget table.

But, the database also needs to keep track of attendance. And the user wants attendance tracked by Shift and Budget.

Shift and budget are completely independent of each other.

Currently, I have the relationships set up like this.

Employee (1) ---(Many) Budget (1)------(Many) Attendance
Employee (1) ---(Many) Shift (1)--------(Many) Attendance

When a new attendance record needs to be added, both the key to the budget and shift are added to the attendance table. The key chosen is dependant onthe Employee chosen, and whether the budget and shift are the CURRENT budget and shift that the employee is apart of.

I thought of running a query showing budget and shift by date descending, so that the latest budget / shift would be displayed, and thus the most current (SUPPOSEDLY) But, if a user put in a different date, or screwed up on the date, then the incorrect shift and/or budget would be displayed.

Date stamping an entry was an option, but there needed to also be a user entered date as well, to specify WHEN a user began working in that specific budget / shift. Therefore two date entries would be required. Duplicate entries in most cases.

I therefore decided to go with a true/false yes/no checkbox. Where the current budget or shift would be checked, and all non current ones would be unchecked (false).

Currently, this is how the systems works. And it works well. But it is dependent on some form code I created to set the yes/no checkbox to true/false depending on the situation.

I DON"T like doing this. Am I missing a way to do this "correctly" where by Access would do this "automagically" instead of via my trick.

The ONLY issue really, is that when a new attendance incident occurs, the user needs to put in the incident to the approbriate shift / budget. And if the current shift / budget could automatically be displayed without user intervention (IE user has to pick the shift / budget from drop down box after looking up info etc etc) since the current information SHOULD be known.

I've never done a table setup where two foreign keys are the many side of the relationship in a single table.

It is currently working fine, and seems to do well, but I wanted to make sure with others who might have had this experience. And also, any "advice" / "cautions" for this kind of situation so I don't step into it deep and have to fix it later.

View 14 Replies View Related

Table With Two Foreign Keys?

Oct 5, 2015

Can a table have two foreign keys?

View 1 Replies View Related

Multiple Primary Keys

Feb 28, 2005

I made a database that was working fine. It was only after I had finished the database, and data was being entered, that I was informed that the field I designated as the primary key would not work. Origanally, I used 'OffCit' as the single primary key. Now they tell me that one 'OffCit' can be entered several times for different reasons. To solve this, I thought about using autonumbers but have been told, and seen, that this may not be a good idea. So, I am trying to use multiple primary keys. So far I have had no luck. I have attached a screen shot of the tables and relationships. I have 'OffCit', 'AreaofLaw', and 'EffectiveDate' as the primary keys. The same 'OffCit' and 'AreaofLaw' will be used several times in different records. That is the reason for the third primary key. I am wondering if I have set this up right or if there is something I overlooked. Perhaps someone has an idea that may work better. Thanks for any input.

View 5 Replies View Related

Should I Use Multiple Primary Keys?

Sep 13, 2005

I have the following table:
YieldID |ProductID | Year | Month| Yield

I want to make sure that it is not possible to insert the same date (so, year and month together) twice for each productID. But at the other hand, it should be possible to insert the same date but then for different productIDs. How can I make sure that Access does this? Can I use (multiple) primary keys to enforce this?
Thank you,
Stacey

View 7 Replies View Related

Multiple Primary Keys?

May 21, 2006

Hi

I have tried searching the forums, but couldn't locate any relevant information. I have an assignment at the moment and am meant to have 8 tables in my data base, two of which are 'User' and 'Hardware' which have the following fields:

USER:
UserCode (Primary Key)
UserFirstName
UserLastName
DepartmentCode

HARDWARE:
HardwareCode (Primary Key)
HardwareName
VendorCode
PurchaseDate

Now I am meant to have another table called 'Hardware Config' consisting of the Primary Key fields from the two tables above, and they are both meant to be Primary Keys in this table.

I have been able to make the 'Hardware Config' table, but I don't know how to make more than one Primary Key... any ideas?

Thanks in advance - dragonfly :o

View 13 Replies View Related

Help With Multiple Primary Keys

Oct 6, 2007

I have a table that has the following fields: Symbol, DeliveryMonth, DDate, Close. This table has information for a large number of different commodities. I am hoping to run a query against the table and find breakouts. I am working on the actual query but my question is this: If I have only one table do I need to have a primary key? There will be no relationships established so what is the need for a primary key unless you plan to link tables together? If it is always a good idea to have a primary key then I will have to have the Symbol, Delievery Month and the DDate together be the key since without all three there would be duplicates. Each symbol has many different deliveryMonths, each DeliveryMonth has many symbols, and each date has many Symbols. SO it takes all three together to get a unique value. Will having a primary key in this scenario help me with writing queries? I appreciate all of your wisdom in explaining how this table should be organized. Thanks:)

View 1 Replies View Related

Multiple Primary Keys

Jul 9, 2006

Looking at a database that someone wlse has created I see that numerous tables have mulitple primary keys. How is this possible? If i try and allocate a primary key it will only let me do it for one field.

I think what I actually want to do is create composite keys but if I set the key then put it to duplicates allowed it says that the primary key is not allowed. Am I mixing up indexes and keys???
How do you assign a composite key

View 1 Replies View Related

Multiple Primary Keys

Mar 21, 2013

I have a table where each record is unique if at least one of three variables is different. The three variables in my case are each integers and are: YearRouteSegment.

Now each of these individual variables allows (and has) duplicates, but if the thing is working right, no pair of records in this table should have all three of these variables with identical values. Is this table a candidate for using multiple primary keys?

View 2 Replies View Related

Multiple PRIMARY KEYS By Code

Feb 14, 2005

I can create multiple primary keys for a table MANUALLY (by highlighting several field then pressing the key icon)

BUT I cant recreate it by code.

HELP.



Set NewFld = tdfnew.CreateField("TaskID", dbLong)
Set idxNew = tdfnew.CreateIndex("TaskIDIndex")
idxNew.Fields.Append idxNew.CreateField("TaskID")
idxNew.Primary = True
tdfnew.Indexes.Append idxNew
tdfnew.Fields.Append NewFld
tdfnew.Fields.Refresh

Set NewFld = tdfnew.CreateField("SubzoneID", dbLong)
Set idxNew = tdfnew.CreateIndex("SubzoneIDAIndex")
idxNew.Fields.Append idxNew.CreateField("SubzoneID")
idxNew.Primary = True
tdfnew.Indexes.Append idxNew
tdfnew.Fields.Append NewFld
tdfnew.Fields.Refresh


I am getting an error, there is already a primary key.

View 2 Replies View Related

Forms :: How Data Is Passed Using Foreign Keys - Cannot Display Proper Information

Jan 25, 2015

I'm trying to understand how data is passed using foreign keys.

I'm using Allen Browne's 'Don't use Yes/No fields to store preferences' at: [URL] ....

I've also downloaded his sample DB, RelationBasics, to use as a guide.

Attached is my version of the Student / Sport DB as described on the webpage.

I use 2010 at home (saved as 2003 version) and 2003 at work (JPNSE OS). Both result in the same thing.

The problem I'm having is I cannot get the actual sports to display in the combo-box, only the Sport_ID number.

I've tried building both forms with and without actual data in the TBL_Student & TBL_Sports tables, but no mater what the result is the same.

View 2 Replies View Related

Tables :: Two Primary Keys Link To Another Table?

Aug 18, 2014

So, I have two Primary Keys in my table, how then do I link another tables to it?

tblAdmissions
PatientID Number Primary Key
Visits Number Primary Key

tblRoomPhoneNumbers
PhoneID AutoNumber Primary Key

View 3 Replies View Related

Table Allowing Duplicate Primary Keys

Jun 13, 2013

I've set up a table with 2 primary keys but it is allowing me to input the same combinations twice!

View 4 Replies View Related

Forms :: Populating Form2 Foreign Key Control With Primary Key From Form1

Sep 11, 2014

I am trying to get the Primary Key value from one form to auto-populate the foreign key value of a second form

I have tried the following code in both the second form's Load Event and the Foreign Key Control Got Focus Event but nothing doing...

Me.[NHS Number] = Forms![frm_Patient_Data_Entry]![NHS Number]

I am new to VBA so not sure of the referencing technique but have looked at the MVPS Form 'referencing' sheet to no avail.

I keep getting an error message (2450) MS Access cannot find the referenced form "frm_Patient_Data_Entry"

I have checked spelling throughout countless times so there must be something wrong with my referencing I guess.

View 2 Replies View Related

Possible For Primary Key Of One Table To Act As Foreign Key In Another Table For More Than One Columns?

Nov 7, 2012

Is it possible for the primary key of one table to act as the foreign key in another table for more than one columns? What I'm trying to do is create a table for a Committee which will have 1 student and 5 professors! So Can I import the faculty ID for each of the 5 faculty members?When I try creating the second relationship, access automatically creates a new Faculty table for the relationship!

View 6 Replies View Related

Tables :: Setting Primary Keys To More Than One Field In Table / But They Cannot Have Null Values

Nov 30, 2012

Access 2010..One organization that we work with provides us with a block of numbers for each of the two types of contract products we order from them; we do order non-contract stuff from them also.The block of numbers are the same (i.e. 20000 to 30000 this year) for each of the two products. This means that each product can have the number 20000, for example. We call this the Tracking Number. If it is one of these products, we need to select the Contract Number.

For all other one off orders we have with them, we assign our own Tracking Number starting with 00001. This Tracking Number cannot duplicate unless it is one of the aforementioned two products.Both the Tracking Number and Contract Number are in the same table. The user selects the Contract Number from a form (connected to the Contract Number table that has all the details on the contract) and the Contract Number is populated in the same table that has the Tracking Number.Each order must have a Tracking Number (no null)..Not all orders need a Contract Number (null okay).The Tracking Number and Contract Number combination cannot duplicate.I tried setting the primary keys to more than one field in the table, but they cannot have null values.

If not... I have been working on Plan B.... an AfterUpdate on the form (either the form or a field... don't know yet) that looks at a query that only has results if there are duplicate values.

View 1 Replies View Related

Foreign Keys

Aug 25, 2006

Hi,

While designing a database, I came up with 3 different foreign keys in one table. I am afraid this is not right!

Is it possible to have this number of FK in one table or does that suggest a design flaw?

Any help will be very much appreciated.
B

View 7 Replies View Related

Redundant Foreign Keys?

Jul 8, 2005

More on the lot materials management program.

I have a series of related table:

Builder
Subdivision
Lot Number
Lot Option
Lot Materials Details

The [Lot Materials Details] table is populated by appending data from the [House Type Details] table tree as follows:

Builder
House Type
Elevation
Options Per Elevation
House Type Materials Details

The filtered Option Table in both branches determine which [House Type Materials Details] records get appended to the [Lot Materials Details] table.

The question is:
Is there any advantage, other than simpler queries later on, to adding the primary key from the [House Type Materials Details] table to the [Lot Materials Details] table.

It’s redundant because the [Lot Options Table] contains the [Options Per Elevation] primary key.

Any thoughts?

View 2 Replies View Related

Foreign Keys And Tables

Sep 25, 2006

hi
i need to create a table, i've figured out to make LastName the primary key. But im not so sure about the foreign key, and whether it is necessary to put it into the table in order for all my tables to relate? Sorry if this doesn't make much sense.
Also, to clarify.. 3NF primary keys in one table cannot be used in another table??
thanks

View 2 Replies View Related

Two Foreign Keys In One Tbl Link To Same Tbl

Aug 5, 2007

Wow, I've found some good stuff here already... Thanks.

PROBLEM:
I have a table(we'll call it shipping) that has two columns FK'ed to another table (locations). My problem is I have no idea how to pull the information from the locations table into the shipping table.

DESCRIPTION:
My shipping table has a source and a destination column. Now, I have a fixed location list that applies to both columns and I'd like to avoid having a source tbl and a destination tbl... I'm an Access Neophyte to say the least (for now at least) but I have managed to put together a querie that pulls out most of the information that I need (name, phone number, yadda, yadda, yadda) but I can't seem to correctly link the FK's for the sources to the locations table as well as the FK's for the destinations to the locations table. When I run my querie I get an error or my Source(destination) column will display the correct information but the Destination(source) column will display ALL of the locations in the location table. Here's what I mean:

Source destination
detroit toledo
detroit cleveland
detroit San Diego
detroit London
detroit Paris
detroit detroit
Paris toledo
Paris cleveland
Paris San Diego
Paris London
Paris Paris
Paris detroit

.
.
.

The first and second source was detroit and Paris but the Destinations list all of the locations I have in the table.

How can I link two FK's in one table to another table in a querie?

Thanks.

View 5 Replies View Related

Multiple Forms/single Table?

Dec 4, 2006

Hello friends,I have a pretty noobish question. I was wondering if it was possible to have several (3 in my case) different forms that apply data to a single table? The main table has three different types of data: 1. Client info 2. Services A 3.Services B. Each type of data has about 7/50/12 different fields of data in each set so that trying to include all this data on a single form is not very user friendly due to clutter.I have three forms that input data into the table, however the record navigation command buttons that I have on the forms do not apply the data to the correct record. For instance, when I input the Client info for client #2, and click the "open services A form" command button, the form opens on client #1 Services A instead of to a new record. Is this something I have done wrong, or is it due more to MS Access limitations? Thanks

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved