Adding AutoNumber As Primary ID To Existing Table
Jan 31, 2012
I have a simple database that I inherited, that contains basic customer contact info. We want to start adding more functionality to the database, so I've done alot of research to learn about how to begin normalizing my data.The existing table does not contain a useful primary key, since we may have multiple individuals from the same company, companies with very similar names, etc.
I would like to add an AutoNumbered field to use as a CustomerID/primary key, but I can't seem to find a way to do so. Here is what I've tried so far:
I have tried to add a field to the table, and make it an AutoNumber data type. When I do so, I can error message saying that I cannot make a field AutoNumber if any other field in the table already has data entered in it.I have tried to create a new table containing only an AutoNumber PrimaryID field, and then import data from the existing table, but that just creates a 3rd table.
View Replies
ADVERTISEMENT
Mar 30, 2007
I have an existing contact information database which consists of a number of tables, the main table has a primary key which is currently set to NUMBER.
There is a relationship between it and 2 other tables via that key (ContactID).
I would now like to change the primary key (ContactID) to AUTONUMBER, so that any new records added, have a number assigned automatically. I also need any newly created primary key number to be used in the child tables created as a result.
Is this possible ? (without renumbering my existing tables/records)
many thanks
View 6 Replies
View Related
Apr 28, 2008
I have a table with more than 700,000 records. There are no unique fields or unique combination of fields. I would like to add an AutoNumber field but when I try I get the message:
File sharing lock count exceeded. Increase MaxLocksPerFile registry entry.
If I answer yes I get an error that the new field was not added. Is there any way to add a key foield to an existing database?
View 3 Replies
View Related
Jul 9, 2015
I was adding an autonumber field to an existing table and I assumed the numbering would follow the order of the primary key but that doesn't seem to be the case.
View 9 Replies
View Related
Jun 25, 2014
I have a table that has a primary key that does an autonumber in increments. Then I made some queries that will append records by date and then also another query that will delete what was appended. Then I created a reverse sequence of this process in case I change my mind and would like to put back the records I archived, however, when I do this, the primary key is now thrown off and will still revert to the last primary key that was appended before the archive ever took place. In other words it still thinks that the records I appended before I did the reverse, are still there. So now it will not let me save the record because it is a duplicate.
Is there a way to set the autonumber for the primary key to where I want it to begin?
View 14 Replies
View Related
Mar 27, 2007
Please bear with me, first post, trying to get to grips with Access for work!
We (a primary school) had a "bespoke" database set up using MS Access by someone who has long left the area and now we need to make changes and I, as the most computer literate person on site, have been volunteered to make these changes.
My level is beginner (for now) and my first job is to add some new options to a "Titles" table, four columns, six records. Column1 = ID#, Column2 = Male Titles, Column3 = Female Titles, whilst Column4 seems to hold items from Columns 2 & 3.
If I add a futher option, I get the error "Can't perform cascading operation: Entry must be one of the Titles as featured in the drop-down box or in the Titles table.
Now, as far as I can tell, I HAVE added the new option to the Titles table, and they DO show in the drop down box in the main spreadsheet display, BUT the error comes back as soon as I try to confirm the new title.
Any ideas please?
View 9 Replies
View Related
May 19, 2014
What I have is a database that I have done some tweaking on and in the meantime the original db has been in use which has added around 200 or so more records in the table.
What I would like to do is to just update the db that I have been working on with the older db table(the one who has the additional 200 records).
EX. DB A(Old DB, Newer Table) DB B(New DB, Older Table)
I want to put DB A table into DB B
Is this a simple fix? Or do I need to write some sort of query to update the records in the old table? I've tried to export the excel file and then import but it puts it in unrelated objects and then my switchboard or nothing works.
View 9 Replies
View Related
Sep 13, 2012
I have 3 tables
tblProductInfo
- ProductID
- ProductItemNumber
- JDEDescription
tblFacility
- FacilityID
- FacilityDescription
tblProductFacilityMM
- ProductToFacilityID
- ProductIDFK (combined with FacilityIDFK to make a PK)
- FacilityIDFK
As I'm writing this out, I am realizing that tlbProductFacilityMM.producttoFacilityID is probably not necessary, but that I don't expect that to have much significance to the issue. So I've setup a query between the two tables:
Code:
SELECT tblProductInfo.ProductID, tblProductInfo.ItemNumber, tblProductInfo.JDEDescription, tblProductFacilityMM.FacilityIDFK, tblFacility.FacilityDescription
FROM tblFacility INNER JOIN (tblProductInfo INNER JOIN tblProductFacilityMM ON tblProductInfo.ProductID = tblProductFacilityMM.ProductIDFK) ON tblFacility.FacilityID = tblProductFacilityMM.FacilityIDFK;
And used it to create my subform which is simply a drop down box for tblProductFacilityMM.FacilityIDFK. My main form is one that has already been in use for 6 months or so, it is based off the tblproductinfo table and needs to have the option to select multiple Facilities for each ProductID. I inserted the subform, but when I try to select a facility I get an error that reads:
Quote: Cannot Join Records; Join key of tblProductFacilityMM not in recordset.
View 2 Replies
View Related
Jun 3, 2005
Hi,
I am fairly new to doing DBA, and I am having trouble adding a new column to a table that is existing. Is there any simple way to do this or does it have to be done throught code? And if so how.
Thanks
zorter8
View 1 Replies
View Related
Apr 17, 2012
How to add a prefix to an existing description in a project table, generated from the project number. Here's a sample of my data:
PROJECT NUMBER DESCRIPTION
01200000 Completed Projects
01601530 Steele Sub
I would like the Descriptions to read:
01200000 MO-20 Completed Projects
01601530 MO-60 Steele Sub
The state, "MO," comes from the first two digits "01" and the coop, "60," comes from the second two digits. I have a table of a thousand or so projects in an Access database that I need to amend the descriptions of to include these prefixes. Is there a simple way to do this in Access or in Excel without writing code?
View 1 Replies
View Related
Jun 8, 2014
I'm trying to add a combo box field to an existing table and form. I added the new field to my existing table and set it up as an combo box added my row sources. Then added the new field to an existing form and now am getting a error stating "The record source "Table name and added field here" specified on this form or report does not exist. When I just make the new field a text box instead of combo box it works just fine.
View 2 Replies
View Related
Oct 20, 2006
Hi, I have 7 tables in my database and 1 form corresponding to all the fields in these tables (linked by a query by recordsource). My problem is that the form will only show a record if ALL 7 tables have manually had the primary key entered (not good when i have information that needs to be added at different times). How can I make it such that if I create a new record on the form that all primary key fields will be updated, and this record will be present every time i open my the form??
Cheers
Tania :o
View 2 Replies
View Related
Aug 17, 2005
My shiny new database is ready to go - now I have deleted all my trial data and want to re-set the autonumber primary key of my main table to start at "1" (I thought I could delete it and re-insert it but it won't let me). Suggestions?
View 1 Replies
View Related
Jul 10, 2005
Just a general question...
When you make a primary key is it better to use and Autonumber or a Custom one? I have for the most part used auto...easy, convienient and well I have never had a problem yet (been pretty serious on Access for about 1 year). But I just read that ref integ doesn't work on Autonumbers? which would also mean cascading update & deleting correct?
I have looked at Northwind Access sample and all except "Customer" they use Auto. Well then to make a custom Primary field wouldn't I have to do that at the form level? like "CustomPrimKey = Left([CustFirstName],2 & Left([CustLastName],2) & [PriKeySetAtAutoNum]" or something like that to get what I want. I imagine you would automate this as opposed to letting the operator manually enter them.
And then as in the Northwind sample how do you know which tables need the custom numbering and which ones don't.
As I have learned on my own some of the "basics" elude me until they come back to bite me.
Thanks
View 10 Replies
View Related
May 4, 2007
Any ideas on tagging my primary key (currently autonum as a data type) with an "M" on the front of it. I guess i wanted to keep it as autonumber so i wouldn't have to key in a value
Thought maybe i could create a new field and do an "M" & [ID] in the default value where [ID] is the autonum primary key field but I guess I'm not doing it right - get an error trying to save saying it can't find the field [ID]
thanks for any suggestions!
View 5 Replies
View Related
Jun 25, 2007
Hi!
I am a new member of this forum and new to access.
I need to make a data base and I would to have my primary Key as an autonumber. I would like to use ID numbers that have already been assigned but they have letters in the beginning, they are GKAD0001 etc. so at the moment I just have them as text that wont allow duplicates, but really I would like them as and autonumber so when new data is added it automatically assigns the next number.
Is this possible?
Thankyou very much for your help.
View 2 Replies
View Related
Feb 1, 2008
Hi,
I need to create a table with 2 sets of different information both referring to job types. the problem is that i need the autonumber which created the job number to scale together. so both tables have a primary key of "job number" and i want the autonumber to only ever use 1 number in both tables.
e.g.
job type 1 - autonumbers - 1,2,3,4,6,7,9,10
job type 2 - autonumbers - 5,8,11,12
is there anyway of achieving this?
Any help greatly appreciated
View 5 Replies
View Related
Apr 15, 2005
I am new to Access, and have spent the last few weeks reading everything I could get my hands on about creating databases. I am working on a new database for my office and I need some help please.
I am trying to create normalized tables, so I have broken down my tables into the smallest possible field groups and linked the tables via primary and foreign keys using autonumbers and the primary key in parent tables. My question is how do I have my users interface in forms with actual data instead of the ID numbers but have the ID numbers inserted in the field. Here is a sample of my address table to demonstrate my question:
tblAddresses
AddressID Autonumber PK
StaffID Number (FK to tblStaff)
AddressTypeID Number (FK to tblAddressType)
Address1
Address2
CityID Number (FK to tblCities)
StateID Number (FK to tblStates)
ZipID Number (FK to tblZips)
Obviously all of my tables are not shown here but this should show what I am looking for.
So, how can I let the user input/select the actual data from the forms in combo and text boxes but actually input the ID numbers into the underlying tables. If a user is inserting a new record and selects state I want them to be able to input/select CA not the StateID 1.
I don't know why I am having such a hard time with this, I seem to understand the other concepts but this one has me stumped. I found a few ways to do it, but I don't think they are right and I want to learn this the correct way. Any help is greatly appreciated, thanks!
View 6 Replies
View Related
Mar 13, 2006
I have three tables.
EAch has a field called ID that is an Autonumber that is the primary key.
When I enter data via a form each record has a different ID across the three tables.
Does this mean they are not linking up correctly?
I have attached my databaset if that helps.
Thanks.
View 1 Replies
View Related
May 9, 2007
Hi,
The database I've inherited has a primary key autonumber set up which is randomly generating numbers. It also has another field which has yet to be used, of a clients reference number.
I thought it would make sense to use the reference number field as the primary key, as it seems redundant otherwise and it would give us another field to perform searches on (if we record a clients reference number on their paper file, then finding them on the database would be much quicker).
Because of the relationships in the database, I didn't want to remove the existing primary key as I'm afraid it would mess up the whole thing. I was wondering if there is a way of having the autonumber which is generated in the primary key to be automatically copied to the clients reference number field? This would leave the primary key intact but give us this extra level of information to search on.
Thanks.
View 1 Replies
View Related
Sep 30, 2005
Hi there,
strange query... I have a testing database which I have filled full of test data. I now want to create an empty copy of this database, so I copied this, removed all records, however the tables where I have a field named ID, which is the Primary Key field and is Autonumber set to increment, I cant seem to get this to set back to 1. I tried deleting the ID field completely, closing DB, and adding it again, yet it STILL remembers the next number up from the last record I had created previously??
Can anyone offer any help as to how I can get this set back to 1, as now I am finished testing I want to essentially start all table records fresh.
Many thanks for your help as always,
View 5 Replies
View Related
Mar 4, 2013
I've just begun using microsoft access and would like to create a primary key on a table of data that has been sorted alphabetically. However, when I try to create this key (designview -> auto number-> increment) it autonumbers for the column the way it was before i sorted it. Is there a known way of doing this?
View 7 Replies
View Related
Jun 5, 2015
How can I insert an 'ID' field into an existing table at first field as primary key using AutoNumber? The table will then be populated.
View 9 Replies
View Related
Jul 14, 2014
The above image is of a table which I need to update according to a new data on daily basis. As you can see when I added two records at the last , the AutoNumber primary key of the table jumped by thousands . I have used the following query to update the table
Code:
db.Execute "INSERT INTO D_Counterparty (CPTY_ENTITY_ID,CPTY_DESC)"_ & "SELECT Ctpy_Entity_Id,Ctpy_Entity_Legal_Nm"_ & "FROM NewCU LEFT JOIN D_Counterparty ON D_Counterparty.CPTY_ENTITY_ID=NewCU.Ctpy_Entity_Id"
I never changed the AutoNumber from incremental to random and I have also tried re-seeding it but had no success. Also when I inserted the sample values using a sample table ,the AutoNumber was working fine . Here's that query
Code:
db.Execute "INSERT INTO D_Counterparty (CPTY_ENTITY_ID,CPTY_DESC,) SELECT a,b, FROM sample"
I did not use join in this query ...
View 2 Replies
View Related
Oct 6, 2005
I am a new MS Access user. I am having difficulty adding columns to a pre-existing database. Is there a simple way to do this?
View 4 Replies
View Related
Jan 24, 2007
Hi.
I have a table with a NAME field and a GENDER field.
How would I code a query so that if the gender is MALE than MR. would be added to the front of the existing data in the NAME field? MRS. if gender is FEMALE?
i.e., the result would be MR. SMITH, MR. JONES, MRS. CARSON...
Russ
View 4 Replies
View Related
