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 Replies
ADVERTISEMENT
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
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
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 4 Replies
View Related
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
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
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
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
Aug 28, 2013
I've got a table - "Products" - in my database, with a text field - "Info" - which contains info about products.
Within this field I would like to have footnotes. To do this, I think the best way to do it is by putting numbers inside the text at the location of where I want the footnote to refer to. These numbers will actually be foreign keys to a table called 'Footnotes'.
I can then program the forms and reports to show any numbers as superscripts or whatever.
(Of course, if the user will actually want to insert a number into the text field which is NOT a reference to a footnote, I will have to make a workaround e.g. by making access put a symbol in front of the number, so access will know the number is just part of the text (and I will program the form to not show the symbol in front of the number)).
Is it wrong to have foreign keys within a text field? I think if executed correctly, it should work perfectly.
View 8 Replies
View Related
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
Oct 5, 2015
Can a table have two foreign keys?
View 1 Replies
View Related
Sep 24, 2012
I have two tables tblPatients (pkUMRN) and tblAdmissions (pkAdmissionID, fkUMRNAdmission). pkUMRN is not auto-number, but is an 8-digit alphanumeric code unique to each tblPatients record. I have set up a one-to-many relationship between tblPatients_pkUMRN and tblAdmissions_fkUMRNAdmissions (each patient can have multiple admissions).
Is it possible to allow the entry of a new tblAdmissions_fkUMRNAdmissions to create a new tblPatients_pkUMRN? IE can you create a new primary key from a new foreign key?
Along a similar line, if the primary key already exists, how would you autofill a form with data from tblPatients when the tblAdmissions_fkUMRNAdmissions is entered in to the table?
View 6 Replies
View Related
May 17, 2007
I am creating a bridge table to get rid of redundant data. I am doing it by making a composite of the primary keys from the two tables I am bridging.
The error message when I get to a certain point is "You cannot add or change a record because a related record is required in table tblMachCent." Both the numbers I am using to create this composite key are in the tables necessary, so I am not sure why I am getting this problem. Any suggestions??
View 1 Replies
View Related
Feb 12, 2014
I've built a system with around 20 tables in it. All of these use the standard ID field offered by default by Access as the primary key. A friend with a background in database design and development for large corporates using Oracle has reviewed the database (he is going to do some VBA programming for me) and suggested that we use more appropriate primary keys based on data attributes.
He has read "somewhere" that there are problems with the standard ID fields and that occasionally the fields are renumbered/reindexed and that this can cause problems. There are a fair number of queries and some complex forms which will need to be updated for these changes but it would be a shame to go through all this work if it isn't really.
Should we use the standard Access IDs and is it worth a chunk of effort to change from where we are now?
View 6 Replies
View Related
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
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
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
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
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
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
Jun 14, 2007
Hi,
I have to create a database for work and for my sins I have to use Access. We are running 2007 but I'm having some difficulty in creating foreign keys in my linking table.
I've searched their help guide and looked on the net but I can't seem to see how I physically create foreign keys.
My structure looks like this:
Customer
CustID (autonumber)
Cust Name
Cust contact
Cust email
cust version
no of users
other info
Modules
ModID (autonumber)
Module name
licensed module? (tick box)
MiscSoftware
MiscID (autonumber)
Software
Installed on live
CustMod
ID
CustID
ModID
MiscID
Thanks
View 10 Replies
View Related
May 30, 2012
I want to create a combo box filter - easy enough usually
However - the field I want to use is a foreign key field which, while it displays a meaninful description - it stores the id rather than the text
I want to create a combobox filter, but when I do all I see is the id's - which are meaningless to the user.
Is there a way to get these foreign key filters to display a meaningful text description??
View 5 Replies
View Related
Jul 10, 2014
I have what I think is a normalised database that uses foreign keys quite a bit.
For example if I had a 'contracts' table which refers to 'clients', then there would be a tblClients, where each client has a primary key. The tblContracts would then refer to the relevant client via that clients FK only which would be linked to the client's PK in tblClients via a one-to-many relationship.
If I need any user for any purpose to see contract related information that makes sense to a human being, I simply construct a query with the necessary relationships that will show client information alongside contract information by substituting tblContracts' client FK with required information from tblClients via the appropriate relationships.
I think that is reasonably basic stuff (hopefully correct practice!)
But what about when I come to import a block of new data that needs to go into tblContracts? I'm not going to be given a list of client keys (obviously) I'm going to given their real names.
MS Access has (in theory) all the information it needs (via the relationships) to substitute client IDs (keys) for their real names and thus slot these IDs into tblContracts with the new data as appropriate, but how do I make it do this? (I know it could kick out errors if there are any duplicate client names, but let's put that to one side for a moment).
View 1 Replies
View Related
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
Jan 22, 2013
I am using access 2007
i have a materialsUsed tbl that list all the materials used in a job and the quantity of each.
(ID
Material Code
Quantity
Unit of Measure)
I also have 2 look-up tables - 1 for the material codes and 1 for the units of measure
I have another table, priceperunitofMeasure
id
material Code (FK to material lookup tbl)
Unit of Measure (FK to unit of measure tbl)
Price
In my form Users will select material code from combo box - enter the quantity, and select a unit of measure from a combo box.
For example: they could enter the following :
Paint 2 gallons
Paint 3 tubes
Paint 1 pint
How do I lookup the price per unit of measure for each of these records in the price per unit of meassure table? The price per unit of measure table has 2 foreign keys.
View 4 Replies
View Related
Jan 23, 2005
I am setting up an access frontend to an excel worksheet. I am creating linked tables using the excel input fields. I find when I have created the tables I can't create a primary key as access won't allow modifications to the linked table. Has anyone any suggestions as to how I can set a primary key on these tables. Thankyou
View 3 Replies
View Related
