Foreign Key And Primary Key
Apr 12, 2008it wuld be helpful if any of u can tell me how to link tables to query using primary key and foreign key or web page where i can solve my problem.
View Replies
ADVERTISEMENT
Foreign Key/Primary Key
Jun 6, 2005Hi All, Me Again :eek:
I have another issue that has stumped me:
On the mainform [frm1930Census] I have a couple of subforms [sbf1930CenHeader & sbf1930CenDep]
The primary key [FTMID] has passed from the mainform to the 1st subform [sbf1930CenHeader] correctly. Using the one to many relationship attached by the [FTMID] field.
The problem is the primary key [1930CenHdrID] from the subform[sbf1930CenHeader] should then travel to [sbf1930CenDep] which it does not. The relationship between the two subforms are one to many focusing on the [1930CenHdrID] field/
I am using the following code:
Private Sub Ctl1930CenHdrID_BeforeUpdate(Cancel As Integer)
Me.1930CenHdrID = Forms![frm1930Census]![sbf1930CenHeader].[Form]![1930CenHdrID]
End Sub
Any help will be most appreciated.
~VanEpps
Tables :: Create Primary Key From Foreign Key?
Sep 24, 2012I 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?
Foreign Key To A Table With Multiple Primary Keyfields???
May 30, 2005I`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
One Primary Key In One Table Linked To Two Foreign Keys In Two Different Tables!!!
Sep 22, 2007Hi 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
Tables :: One Primary Key To Multiple Foreign Keys In The Same Table?
Aug 29, 2013Right 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]
One Primary Key + Multiple Foreign Keys...help Creating Form With Option
May 4, 2006I 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
Forms :: Primary Key As Multiple Foreign Keys In Single Table
Mar 26, 2013I'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 RelatedForms :: Populating Form2 Foreign Key Control With Primary Key From Form1
Sep 11, 2014I 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.
Possible For Primary Key Of One Table To Act As Foreign Key In Another Table For More Than One Columns?
Nov 7, 2012Is 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 RelatedForeign Key
Apr 3, 2008Ive got problem with defining foreign key
i explain what table ive and what i want
between my Student ,Session there is many to many relation that student session connect them together
student: Format student session: Format
Student ID Autonumber Student ID Autonumber
student name Session ID Autonumber
student address
student dob.....
....
Session: format
Session ID Autonumber
dance days
dance time
no of student
the student id is primery key and session id is primery key ,student session is compound key of this 2 but it wont work it keep saying u can only have one auto number in one table #
what should i do ?
What Is A Foreign Key ?
Mar 19, 2007Hi all,
my question is what is a foreign key and how is it used ?
Foreign Key
Jun 8, 2007Hey all, i'm new here! I've spent hours upon hours trying to solve this problem, but it's got me nowhere, so I think it's time to ask for help! I'm not exactly an Access beginner (I generally know what i'm doing) but this has me stumped!
Ok, basically my DB has several tables.
Staff (main table)
Qualifications
Institutions
The problem is - i'm trying to set it up so the fields "qualificationName" and "institutionName" in the staff table extract the data from the fields with the same names in the qualifications/institutions tables.
I've tried linking the fields together as foreign keys, but when I go to set referential integrity, I get the error "No unique index found for the referenced field of the primary table"
staffID in each table is linked properly, but that's as far as it will let me go!
Any help would be really appreciated!
Foreign Key
Apr 2, 2008hi
i am new to access 2003 ,i would like to know how do i tell access which attribute is foreign key of another primery key ,for example
if i have two table
1.student
2.order
i want to have student id as foriegn key in order how would i do that ?
thanks
Primary Key No Longer Primary Key!
Sep 15, 2006I have created a very simple Access database with a CompaniesTbl, ContactsTbl and CallsTbl. The database is used to record telesales contact with customers. The tables are set up like this:
The CompaniesTbl has an AutoNumber Primary Key field called CompanyID.
The ContactsTbl has an AutoNumber Primary Key field called ContactID and a foreign key called CompanyID.
The CallsTbl has an AutoNumber Primary Key field called CallID and foreign key called ContactID.
The relationships are set up in the relationships window and referential integrity imposed.
Twice now, while editing a contact record, an error has occured. Task Manager has had to be used to get out of it. The error is either a 'run-time error with a message that does not mean anything' or it says 'unrecognized database format' (!?).
If you open the database again the ContactID field in the Contacts Table is no longer a Primary Key field. If you look in the relationships window - the relationship between the Companies and Contacts tables no longer exists.
Any ideas much appreciated.
Need To Allow Foreign Key Duplicates
Nov 28, 2005hi,
i have a form with a sub-form on it. each has is bound to a table. the main form's table has a one to many relationship with the sub-form table, and the sub-form table has the PK of the main table as a FK.
every time i try to add a second record to the sub form, it gives me an error saying i cant have duplicate foreign keys.
i cant figure out what to do. i am using access 2003 and microsoft sql server version 8. i need to enable duplicate foreign keys so that each record of the main table can have more than one record in the sub table.
Please help!
Foreign Keys
Aug 25, 2006Hi,
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
Foreign Key Fields
Oct 23, 2004I have a table with Client info, I then have child tables with info on what workshops the client has attended or if the client recieved services. The idea is to use "ClientName" as the primary key in the main "Client Info" Table and then use this same field as the Foreign key in the smaller child tables. Everything seems to work out except if I want to "Enfore Referential Integrity" in the relationship. When I try to do that I get this error message:
Relationships must be on the same number of fields with the same data types
Is this going to screw me up later?
Thanks
mark
How To Inseart Foreign Key
Apr 28, 2007i am creating a database and i had created two tables one is holidays and second is departure date.. in the holidays table the primary key is holiday code.
and in the departure date table the primary key is Departure Key and there is also field is Holiday Code. now i had create two tables and then i had insert both the table in one but the problem is this when i am changing the primary key's data of holidays table but the rest field is not changing.. i think its need to assign foreign key but i don't know how to do that can u plz tell me how to insert the foreign key.
i doing my assignment and i am stuck here plz reply me soon so that i can move further..
Append To Foreign Key?
Aug 28, 2006I have three tables as below, linked by a many-to-many relationship.
Contact Table
ContactID (PK-Autonumber)
Contact details...
Mailing Table
MailID (PK-Autonumber)
ContactID (FK)
NewsletterID (FK)
Details...
ynMailed
Newsletter Table
NewsletterID
Details...
My Problem: I have acquired 200 customers in one month, and I have to MANUALLY assign each bulk mailing recipient. This will not do. I've experimented with Queries to do the work for me, but keep tripping over a foreign key violation.
My Question: How can I create many records in the Mailing table with a constant Newsletter ID and whichever contacts my Select query returns? How do I populate the SECOND foreign key (the constant Newsletter ID) so I don't get the key violation?
I thought about doing a Make Table Query based on my Select Query, then an Update query to create the second foreign key, then an Append query with all the values in place. Is there an easier way?
I feel like this should be really easy, but two weeks of tinkering have gotten me nowhere. A nudge in the right direction would be greatly appreciated.
Simple Foreign Key Problem
Apr 16, 2006Hi, I'm trying to create a basic database to track customers and to log conversatons/activity with the company.
I've two tables. One to hold the company info (e.g. comp_id, comp_name, comp_address, etc. etc.) and another to hold the conversations/history (e.g. hist_id, hist_date, hist_detail and comp_id which is the foreign key).
The problem I'm having (and it's probably basic) is that I've created two forms. One to display the companies and one to show the history of a particular company. I've added a button on the companies form which opens the history form (based on the selected record) where the two comp_id's match however, if I try to add a new conversation/history the comp_id foreign key isn't updated with the current comp_id and therefore isn't shown the next time I view the history of that company.
The tables are joined on the comp_ids as a one to many relationship.
Apologies if I haven't explained very well. Still finding my way with access.
Two Foreign Keys In One Table.
Apr 20, 2006I'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.
Foreign Key Stupid Question
Dec 15, 2004I have done multiple searches with no luck- sorry to ask such a basic question, but I want to know the correct way of inserting a foreign key into a table.....I was told to Insert - Lookup field - in my main table - is that correct?
Thank you :confused:
Redundant Foreign Keys?
Jul 8, 2005More 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?
Foreign Keys And Tables
Sep 25, 2006hi
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
Cascade-To-Null Foreign Key
Oct 19, 2007Hello,
I'm using Allen Brownes very helpful cascade-to-null relations (http://allenbrowne.com/ser-64.html) code in my database project, but there comes problems whit a foreign key and null values, cause foreign key shouldn't be null. Is there anyone else using this method with relational tables? What I should do to make this work?
All hints and tips are welcome and approciated, thanks on advantage. =)
- IpeXeuS