Bridge Tables W/composite Primary Keys
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 Replies
ADVERTISEMENT
Feb 25, 2005
Is it possible that a composite primary from table A to be a foreign key in table B?
I have two tables: A and B. The A has some personal details such as first name, surname, phone number, etc.
Since no personal ID is given in my project, I am thinking to use first name + surname as the primary key of table A. But when I wanted to make the relationship between A and B (in B I need to use these 2 name fields as the composite primary keys, too) and ticked the option “enforce referential integrity”, an error message prompted out said it could not find the only primary key in table A.
What should I do with this? Or it is impossible to have a composite primary key to be foreign keys (but also primary) in another table?
It will be great if someone could answer this!
Thanks!
View 4 Replies
View Related
May 5, 2005
I've noticed that the great and the good all seem to recommend not using composite PKs. Does that even go for join tables in the middle of a many-to-many relationship? Not sure I see what's to be gained by adding an ID and making that the PK.
Any comments?
Dave
View 14 Replies
View Related
Oct 3, 2007
Hi,
I am trying to join 2 tables with composite keys - each of them have the same 4 columns as part of its key - date/person/problem/date sent.
Table 1 has 5 columns (4 key columns + 1), Table 2 has the same 5 columns plus 1 additional column (Comments).
I am trying to match the Comments column in Table 2 to Table 1 and show the output in another table.
Currently, I've related the 4 columns in each table to each other, each relationship defined as including all records from Table 1 and only the records from Table 2 where the fields are equal.
In the fields, I have 5 Columns from Table 1 and the last column (Comments) from Table 2. When I run the query, I get all the records from Table 1, but not all of the Comments that match from Table 2 are shown.
Any Ideas on how to improve this link?
Thanks!!!
View 7 Replies
View Related
Jun 2, 2013
Composite Primary Key in Ms Access 2010. I shall try and explain my tables in detail. So my first table is "Client Database" where the field "Buyer" is primary key. This is connected to my next table "Project Database" which has fields such as Work Order #, PO #, PO Date , Buyer etc where Work Order # is the primary key. Finally "Projects Database" is connected to Order information. Now the problem is until now, I always had 1 PO # for each Work Order #. However now I am facing a situation where my client has clubbed multiple PO #'s for the same Work Order #. I need to trigger the Primary Key Violation only if both the Work Order # and the PO # are the same.
View 8 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
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
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
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
Dec 5, 2005
I have recently started a new project, on one of my tables i have a composite key for my primary key. However when i create a relationship using one of the fields in the primary key i can't "enforce referential integrity". Every time i try i get the message "No unique index found for the referenced field of the primary field"
can any one help
Mindy
View 2 Replies
View Related
Oct 6, 2007
Many to Many
I have a basic question, I know for a fact that all many to many relationships have to be broken into two one to many relationships using a junction table. However does that mean it is (a must )to have the two foreign key of the parent tables have to be the (primary composite key) of the junction table.
For example, let’s say I have 5 tables that I need to establish many to many relationships between them. Is it correct to establish a junction table with all 5 foreign keys of the parent tables as composite primary key in the junction table to!
Hope the above making sense!!! :confused:
Many thanks :)
View 3 Replies
View Related
May 13, 2005
Can Access deal with Composite Primary Keys? or will it only let you assign a single attribute key?
If it does allow you to create composite primary keys, how do you go about doing it?
View 1 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
May 12, 2005
I need some help with developing a certain aspect of a db. I need to track the equipment list of clinics. The equipment list doesn't really change (it has 5 possible values though each clinic may only have one to all 5 of them). The db is also to track who the manufacturer is.
I was originally going to do a three piece composite key consistenting of the clinic id, the equipment id and the manufacturer id with quantity as the non pk field. Or the other possiblility that I can see is just using an autonumber as the pk with clinic id, equipment id, and manufacturer id as fk's with qty as the non-key field. So what do you think? If you can put in explanation why your suggestion would work, that would be appreciated as I'd like to increase my knowledge base on this stuff.
If I haven't made my problem clear, I'm quite willing to provide more info.
Thanks,
scratch
View 5 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
Sep 9, 2005
I have a rather interesting situation involving creating a primary key. Basically I have two tables.
Table 1
Date, Customer, Part Number, Work Order, Qty tested, qty failed, tech name
Table 2
Defect, reference, quantity(defect), defect cause, part number, work order
The ideal relationship I want to create is that for every Table 1 there are multiple table 2. Regular one to many relationship. The problem is that in the first table it is possible and probable that there will be duplicate records because of the process. The test parts by individual carts, but often they're exactly the same parts with same work orders and same everything else. How should I go about doing this. I don't want to just create an autonumber for each record if possible. Any suggestions?
View 1 Replies
View Related
May 29, 2012
I am trying to set a primary key on two fields. the problem is that the one field is null for some records. They will eventually fill them in. However, because it is null now it will nowt let me set it as a primary key. I tried setting the field, in the properties, with a default value; but it would not take it.
View 2 Replies
View Related
May 16, 2005
I am trying to run a query that links to tables, orders and invoice together. I am pulling fields from each table, with a relationship between Order# which is found in both tables. The only problem is, order# is a composite key in the order table and strictly a foreign key in the invoice table, referenced through a combo box drop down. When i attempt to create a query, it will not run. (Also will i find a similar problem with my combo box reference?) Any ideas on how to create a query would be appreciated.
View 6 Replies
View Related
Sep 23, 2014
I have a question concerning the automatically sequential numbering of a primary key as part of a composite primary key.
Tbl_treatment:
ID=numeric field (also in tbl_pt and in tbl_tumor)
Tumornr=numeric field (also in tbl_tumor)
Treatmentnr=numeric field
[ID] and [tumornr] are fixed and i would like to automatically number [treatmentnr] per [ID] AND [tumornr] in a way that the output will look like this:
1001 1 1
1001 1 2
1001 1 3
1001 2 1
1001 2 2
1001 3 1, etcetera.
However, when i use the SQL-formula below my output looks like this. It seems as if the function is not properly taking the composite primary key of [ID] AND [tumornr] into account or not finding the true max value:
1001 1 1
1001 1 2
1001 1 3
1001 2 2
1001 2 3
1001 3 3, etcetera.
If (DCount("[treatmentnr]", "Tbl_treatment", "[ID] = " & Me.ID & "" & "AND [tumornr] = " & Me.tumornr & "")) = 0 Then Me.treatmentnr = fRowNum(False)
Else Me.treatmentnr = (DMax("[treatmentnr]", "Tbl_treatment", "[ID] = " & Me.ID & "" & "AND [tumornr] = " & Me.tumornr & "")) + 1
View 3 Replies
View Related
Nov 28, 2005
I was wondering if anyone would be able to help me? I have two tables which can be linked together as they both contain a field called Company Name. However, in one of the tables I want Company Name to become my primary key but this field contains duplicates. How can Isolve this so that I can still link my two tables together?
View 2 Replies
View Related
Jun 26, 2007
I have two tables :
schema with :
schema-id, Autonumber, primary key
name, text
and a table regel :
regel-id, Autonumber
class a, number (lookup from a table)
class b, number (lookup from a table)
schema-id, Long number
they are linked 1:n. A schema can have many regel's.
I want to make a form of schema and have that form contain a subform with regel's in it. The subform can be shown as a datagrid.
Now here's what I do.. I start the form wizard and select both tables. The wizard takes me to creating what I want and it works too. I have a main form schema and a subform with regel's.
Now I have a problem :
The regel fields (class a and class b) can have only three possible values. And I don't want that the combination of these two fields can be the same.
I'v thought about making these two fields the primary keys of regel, but that doesn't work. It works for one record of schema, but when I navigate to a next record of schema, I can't make all combinations of class a and class b over again. It's like I used the combinations in the preceding record of scheme. Then I tried making three fields the primary key, namely class a and class b and regel-id. Didn't work either. What I need is a primary key on the combination of schema-id, class a and class b. But I don't know how to do that.. after all, schema and regel are seperate tables.
I want to be able to make for every schema record all possible combinations for regel records.
How do I do that?
View 7 Replies
View Related
May 1, 2005
Hello all,
I am re-working an old database that 'grew up' the wrong way. It is a medical patient database and has 4 main tables. Primary key of main data table is Medical Record number (MR#), a user entered field (I know...). This links to 3 other tables: Diagnoses (indexed by MR# as well, 1-to-1 relationship with main table), Consults (indexed by a compound key of MR# and date, 1 to many relationship), and Procedures (indexed as well by MR# and date; 1 to many as well).
Here is my plan, and I want to know if I'm going to go about it the right way:
I will first delete the relationships between these tables.
I will then give the main table an autonumber field called PatientIndex, and each other table their own autonumber (DiagnosisIndex, ConsultIndex, ProcedureIndex). I will also give each of the other 'daughter' tables a PatientIndex field as well. I will populate the PatientIndex fields of the daughter records with a VBA routine that matches their MR# with the MR# in the main table, and then inserts the corresponding PatientIndex.
Now that each table has no relationships, and has all the right fields, I suspect Access will allow me to change the Keys for each table, and create new relationships, and it should all work cleanly. The goal is to have each table indexed by an autonumber field that is unrelated to user entry, and to link them by the Primary key.
Does this sound like the right approach? As to why do this if it works OK, it's a matter of maintenance. It's simply too buggy as it stands, and it's torturing me. Will my plan work without crashing the db?
View 5 Replies
View Related
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
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
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
Jul 31, 2007
SOLVED: Hows it goin?I need to assign a second primary key to a table involving three fields (Student Number, Subject Code and Average). I have to make Student Number and Subject code the primary keys. Any help would be appriciated.
View 6 Replies
View Related
