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?
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 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
I 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 RelatedLooking 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 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.
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 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
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).
View 4 Replies View RelatedI 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
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 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'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
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 (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?
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 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?
Hi there.
I am creating a database and i have the layout I want. although I am having problems as i am using autonumber as the primary key in each of the tables. Is there are way to make this work as the data to be entered into this database does not have a natural key until they are 4 months into the process.
i have attached my relationships diagram.
if it is not a good idea to use an auto number as the primary key does anyone have any suggestions as to what i should use?
why Access requires primary keys to be automatically generated so that there are no redundancies.We have a specific agreement with another organization that involves different billing system in which they send us purchase orders, each with a unique invoice number, that includes orders for one or more events.In my events table I have included a field to manually enter the PO number for this organization when it's needed.
I would like to have another table for the PO's with a one-to-many relationship with the events table (since each PO may cover multiple events). Is it possible to use this manually entered PO number field as a primary key to establish the relationship?
I am developing a DB that is composed of approximately 50 tables. The primary key in every table is automatically generated byAccess. I understand that the primary key for each record in the table has to be unique. My question is: Is the primary key uniqueness applicable across the entire DB?
For example, table A has a auto gen key #25 in it. Therefore, no key in table A can be #25. Now I make an entry in table B (A:B, is 1:many). Can the primary key in table B be #25? OR, once #25 is used and continues to exist, it cannot be used in *any* table?
I have made several small examples and the keys across all the tables are unique. I don't know if this is guranteed across all the db tables, or just in my particular case?
Thanks
John
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??
Hi guys
I am trying despereatly to fulfill this function with a macro
any help will be appreciated
I know this may be easy but the help files isnt helpful- so what else is new ;)
Database- C:sports.mbd
table - tblbasketball
current primary key - ID
new primary key needed- ID, EMPNO
The following codes dont work :(
ALTER TABLE tblbasketball
DROP CONSTRAINT PK_tblbasketball PRIMARY KEY
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