Table Allowing Duplicate Primary Keys
Jun 13, 2013I've set up a table with 2 primary keys but it is allowing me to input the same combinations twice!
View Replies
ADVERTISEMENT
Tables :: Two Primary Keys Link To Another Table?
Aug 18, 2014So, 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
Tables :: Multiple Parents Table Linked To Child Table - Primary Keys
May 28, 2015I 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
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]
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 RelatedTables :: Setting Primary Keys To More Than One Field In Table / But They Cannot Have Null Values
Nov 30, 2012Access 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.
Finding Duplicate Values From The Table Having No Primary Key
May 18, 2005Hi All,
I have one table where ID field is autogenerated, and another field orderno which is not primary key. Now it has 2 duplicate values in it so when I try to make field Orderno as primary key, it is not allowing me to do so.
Can any one help me how to find the duplicate values using query.
Thanks in advance,
Jigs
Primary Keys
Nov 28, 2005I 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 RelatedPrimary Keys
Jun 26, 2007I 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?
Allowing Duplicate Data In A Query
Jun 13, 2007I have a database that allows multiple entrys of the same information. I also have a report based on a query that pulls out said data.
I just need my query to pull out every record regardless if it is indentical to a previous one. I found under queries > design > properties there are two options unique values and unique records. According to a source online, these are what control allowing duplicates through, but I have swapped them around with no luck. Currently they are both set to "no".
Is there some other setting I have to switch up in order to allow the duplicates to come through?
Thanks
Changing Primary Keys
May 1, 2005Hello 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?
Composite Primary Keys
Feb 25, 2005Is 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!
Multiple Primary Keys
Feb 28, 2005I 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 RelatedComposite Primary Keys
May 5, 2005I'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
Should I Use Multiple Primary Keys?
Sep 13, 2005I 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
Multiple Primary Keys?
May 21, 2006Hi
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
Assign Two Primary Keys
Jul 31, 2007SOLVED: 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 RelatedHelp With Multiple Primary Keys
Oct 6, 2007I 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 RelatedLook Up Lists And Primary Keys
May 3, 2005I have a table (call it Table1) with a primary key that is a number and one other text field which is what the user would recognize the field by.
Another table (table 2) looks up to table 1. And yet another (table 3) looks up to table 2. I want the text name to show in the lookup list of Table 3 - NOT the number.
Right now, when I look at the sql results in the lookup field for table 3, the text appears rather than the number. But when I open table 3 and try to use the lookup list, the number appears.
Any ideas how to fix that? Do you need more details and sql?
Multiple Primary Keys
Jul 9, 2006Looking 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
Multiple Primary Keys
Mar 21, 2013I 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?
Need Help With Allowing Duplicate Selection For Transferring Listboxes
Jul 1, 2006I found this sample (http://home.earthlink.net/~mgf00/SelectDemo.zip) which allows you to move your selections from one listbox to another with four buttons (<<, <, > and >>) which is just perfect.
Well, almost, actually.
This is already set up to protect against duplicates and that's a good thing. However in my case, my uppers want me to allow duplicates. The code is beyond my ability and my attempts only caused more problems.
The code requires a unique index be generated when the selection is moved between the listbox. Duplicating the index will cause error, and my attempt to circumvent that has either failed totally or is inconsistent (e.g. works if it comes up with a unique value, but will fail if it happens to duplicate a key).
Can anyone show me how I can modify the code to allow for duplicates? The selections then will be looped into a many side table.
Lost Relationships & Primary Keys
Jun 6, 2007I'm having lots of erratic problems with our Access database. Back end tables on the server, front ends on workstations. Running Windows 2000 server, Access 2003 SP2 on Windows XP SP2. I'm not the network administrator so don't have much detail on network. For instance, the back end is 'losing' relationships between tables, with the primary key on the 'one' end of a 'many' relationship disappearing - the relationship link between the tables also disappears. Other errors are:- erratic problems with indexes which are sometimes fixed with a compact / repair, sometimes not- data on forms disappearing- data on forms appearing in the wrong field- queries that ask for parameter input twice when only programmed to so once- sorting orders suddenly going wrong way round - inability delete a record via a form, caused Access to completely crash with Microsofts 'sorry unrecoverable error' message. To fix the query problem, I created a blank query and imported the old one into it, problem solved. But what caused the old query to act weird in the first place?To fix the inability to delete a record problem, I created a whole new blank database and imported everything into it. But what caused the database to repeatedly crash? The suddenly missing relationships I have no cure for. I'm at the end of my tether - can't figure out any pattern to this. Any suggestions? Network errors? Read / write on server? Disk space / fragmentation? Fundamental problem with the database?Any help much appreciated.Sorry if this post should have been in the tables forum.
View 6 Replies View RelatedMultiple PRIMARY KEYS By Code
Feb 14, 2005I 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.
Calculated Primary Keys And Datatypes
Mar 21, 2006I have a table with primary key, application ID ( this is autonumber type).
In a new table i want the primary key, copy number, to take a value that consists of the application ID and add an autonumber to it.
e.g
Their are two copies of Application X which has an application ID of 19.
so the first copy will have a copy number of 19+1= 19 1
and the second copy will have a copy number of 19+2 = 19 2
is this possible and if so how or are there any other options?