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
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
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]
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
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.
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??
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?
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 RelatedI'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 RelatedI 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 RelatedI 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?
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?
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!
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 RelatedI'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
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
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
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 RelatedI 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 RelatedI 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?
Looking 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
I 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?
Hi Forum,
I'm building a db to track computer and user problems for work, where I double as a Client Support Administrator (IT Guy).
I have these tables: (simplified list)
tblComputers
tblPeople
tblCompAssignment
tblProblems
I'm at a loss as to how to link the 'Problems' table. This is where I'll record trouble tickets reported by users.
Incoming trouble tickets could relate to the Computer or to the User's Network Login.
I thought I might have a field in the problems table for UserName and also one for ComputerID, but this seems problematic. Anyone got any ideas?
I'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 RelatedI 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.
I 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?