Primary Keys And Auto Number
Feb 8, 2008
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?
View Replies
ADVERTISEMENT
Jul 13, 2006
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
View 1 Replies
View Related
Feb 12, 2008
Hello ALL,
I want to know how to reset (to start again from 1) my primary key field. As an example i have the "Customers" table with the field "customerID" as PK now i entered many values (20) then i deleted them but i want to start again and the "customerID" value starts with 21 i understand that but i want a fresh DB (i was testing things) and i want to start from 1 can i? how? i even deleted the relation between this table and the other table but no success!
Some help please?
THX in advance
View 4 Replies
View Related
Oct 6, 2004
Hello,
I am a beginner and know a little about access. I am trying to create a customized Primary Key with an auto number. I want it to look like this: VP001 ,next record, VP002 and so forth. When I enter a new record and want it to do this automatically. It could be VP1, then VP2, I don't need the extra zeros but it would be nice. I don't know how to do this. If someone could please help walk me through this. Remember I am a beginner with databases and need help.
I would appreciate it
Thanks
Biz
View 2 Replies
View Related
Feb 13, 2008
Hello ALL,
I want to know how to reset (to start again from 1) my primary key field. As an example i have the "Customers" table with the field "customerID" as PK now i entered many values (20) then i deleted them but i want to start again and the "customerID" value starts with 21 i understand that but i want a fresh DB (i was testing things) and i want to start from 1 can i? how? i even deleted the relation between this table and the other table but no success!
Some help please?
THX in advance
View 2 Replies
View Related
Nov 19, 2011
Can I change the primary key currently set to my products description to an auto number? I'm trying to make things right, also does the foreign key get an auto number?
View 2 Replies
View Related
Jun 19, 2013
When I first was making my database I used all auto numbers for PK's. But then I read an article or two saying that you really don't need to do that if you don't have to (actually, the article said "don't use autonumber if you want anyone to understand your database). I have a few tables (most employee experience type tables) where I just use the word as the PK and there is no auto number in the table at all.I continue to build my database?
View 14 Replies
View Related
Jun 23, 2013
Is there a way I can format AutoNumber for a primary key, for example, have the first ID start as C001 then for the next entry Access will automatically go to C002, and so on and so forth?
View 14 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 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
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
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
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
Oct 6, 2007
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 Related
May 3, 2005
I 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?
View 3 Replies
View Related
Jul 9, 2006
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
View 1 Replies
View Related
Mar 21, 2013
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?
View 2 Replies
View Related
Jun 6, 2007
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 Related
Feb 14, 2005
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.
View 2 Replies
View Related
Mar 21, 2006
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?
View 6 Replies
View Related
Jul 11, 2013
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?
View 6 Replies
View Related
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 1 Replies
View Related
