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!
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
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.
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).
I created 2 table that are linked to a link table so to get a many to many relationship.I have a table for locations and a table for parts.One location can have many parts and One part can belong to many locations, that is way the many to many relationship.
how to get values from the parts table to the Location table using the linked Primary Key in the foreign key linked field.The values also have to be interactive, if I change the value in the part table it needs to update in the location table.
What can I put in the field of the location table to get a specific value from the parts table?
I tried setting the field to calculated field and put the Dlookup function but that didn't work. The lookup wizard seems to be the solution, but I can only create combobox or list box and I need to select the value manually. I just need a value to be put automatically by looking at the foreign key.
If I put the Dlookup function on the default value, those it update if a value is changed in the part table?
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
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?
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...
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.
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.
ok , i have a primary table that have a field of "job", then i create another table that have a field of "job" too , then how can i do this task?
Whatever i type a data into the primary table("Job" field) i want it to duplicate the data into the another table("Job" field) . is it possible to do this? can anyone guild me to do ?
I have three tables that contain different columns but linked by a primary column call Name. I want to create a table where all these different columns in the three tables join to form a master table which can be updated regularly either through the master table or the smaller tables. The master table also has the primary column as Name.
If I update the master table with records, it should update the respective linked table and vice versa. I also want to link these tables to my SharePoint site.
Note: except the Name column, none of these tables have any other columns in common How do I go about this?
Trying to use a form based on a table to choose a recordset of values, and pick the ID of that recordset to include in another table as a foreign key.
I am not sure how to even search for what I'm trying to do, though I've tried all the variants I can think of anyway.
The idea being that there are a set of values that are associated with each other, and generally found in conjunction with another set of data. So Table 1 contains data such as this, though this is greatly simplified from the numerics that are actually stored in these fields:
FooID Field 1 Field 2 Field 3 etc, etc... 1 x y z 2 d y z 3 x y q 4 x r P . . .
This data is then associated with the information in table 2, and rather than repeat fields 1-whatever in table 2 I want to use the FooID in table 2 to store the link to all of the subsequent field data. Normalization as I understand it in other words.
I cannot figure out how to pull the table 1 data into a datasheet form and allow someone to choose one of the lines of data, store the FooID into an unbound text box, or some other method, and then run an update query on table 2 to update a field with the FooID for the 12-72 records that are in that table.
Would this make more sense as a combo box in a single form? I've been trying to do this with a datasheet, but I think I'm too limited in appending a check box to an individual recordset this way, or at least I've not managed to get it to work so far anyway.
I used to import excel data into access successfully, many times but now I have to import excel data into an existing Access table with foreign key fields, which makes me problems.
Its just doesn't work...and Im sure the forien key fields are the prob cause, the other fields are going well ...
I have two tables tblCList and tblCode. The primary key of tblClist is an autogenerated number which is the foreign key to the tblCode.
The tblCList has another column names sClist.
whenever I import values from Excel into the tblCode, I need Access to look up the text values from the sClist in the tblClist and insert the appropriate number corresponding to the entry in tblCode.
I am accessing a SQL database using Access but its not letting me view the table in design so I can set a primary or letting me change the table names.
I am able to do this from my home PC but not at work. Is there something in options or ODBC settings on the work the PC that I need to change.
I'm still new in ADP development and need some advices and helps from you who has already expert on it. Fyi, I used Ms. Access 2007 and SQL Express 2005. I found a problem when working with my project. Here is the problems :
I'm using form wizard to create master/detail form but why I can't insert record in the detail section (the navigation button also became disable) ? The detail table doesn't have primary key only have foreign key that related to the master table. Should a table must have a primary key if we use ADP ? As attached is my relationship diagram.
I did some BAD planning before I started creating my database and I set a text field ("field1") of a table ("table1") as primary key, then I linked this field to a field in another table ("table2", one-to-many relationship). Now I wan't to change some values in table1 in "field1" defined as primary, but I can't (Access refuses to do so...).
Because I have a lot of data entered allready I would like to know if there is any way for me to change values in "table1"/"field1" and that all the linked fields in other tables ("table2") would change correspondingly??????
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
