How do I reassign a new PK for may tables.. they're already related and there about 2,000 records on the Main Table and like 6000 records on the related table.... I used a wrong PK and I want to create a new (long Integer Auto-Number) but I don't want to lose what I've done already.
View RepliesHow would I go about changing the primary key to a different field in a table? :o Thanks for any help!
View 2 Replies View RelatedHello,
Firstly, I am very much self-taught on access, so there are several gaping holes in my knowledge, and my database structure is probably not wonderful! I have been learning as I go along, so in the initial stages I have built in several problems which I am now discovering!
My db is designed to track suggestions - i have a table where the idea, progress etc is stored [tblIdeasBank]. This contains an 'originator' (person's name) which is linked to a second table, where the names are stored [tblPeople].
Originally I set up tblPeople with the Primary Key as FullName (e.g. Joe Bloggs) (guaranteed to be unique with the small number of people that would be involved), and a second field called InitialLastName (e.g. J Bloggs). It is this second field that is stored in the tblIdeasBank. Data verification came from the user having to select a name from a drop down box, so there is no actual 'relationship' between the tables. (Didn't realise how useful they were at the time!)
The db has been so successful :confused: that it is now going to be rolled out across the company, which gives me two problems. To populate all the names, I need to import them from Excel, where I will have FirstName and LastName. I have now realised that it would be more sensible to just store these, and create InitialLastName where needed. Also, I may well have duplicate names, so I need to create a unique ID, and a proper relationship. (Yes, I know I should have done that in the first place).
So, I have:
tblPeople:
.FullName (Primary Key)
.InitialLastName
tblIdeasBank:
.Originator (stored as InitialLastName)
And I would like:
tblPeople:
.PersonID (Primary Key)
.FirstName
.LastName
tblIdeasBank:
.IdeaID (Primary Key)
.Originator (stored as PersonID)
With a One-Many relationship from PersonID to Originator
Any ideas how I would go about doing this and changing the Originator for each idea from InitialLastName to the relevant PersonID number, without corrupting the data?
I have searched through Google and various groups, but cannot find a useful answer, so any pointers gratefully appreciated!
Thanks,
Nick
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 RelatedI am new to access 2010. I need to change the primary key in a table ("stations", Primary key is "station number"), because future entries will result in double entries for this primary key.
I have another table ("species&stations") which has records of Species for each "station number".
So I created a new Primary key for the "stations" table, now called "species id", being a bit better in defining a unique station (as it contains the year and the species number, i.e. "451_2010")
Now the table "species&stations" references/(is in relationship with) the old primary key, identifying a station number for each record.
How do I get it to recognise the new primary key from the "stations" table, and keeping the records (over 1000...) with all there info.
Approach so far: I tried to create a new field in "species&stations", which should look up the new primary key from "stations" and then autofill the field. i did not manage this though
I attached a screenshot to make the relationships more clear!
I created a database that I want to move to a server for others to access. When they open it, it says invalid path. It's looking for my desktop. How do I get this whole database moved to a shared location on a server?
View 8 Replies View RelatedCurrently we have a web based order taking/inventory system. Every night they export a .db to an FTP server and every morning I download it (I automated this part, it replaces the older file). I link to this database (using SQLlite3 to ODBC driver) from an Access database for specifically this purpose.
When I originally created it I had to go through every table and create a unique identifier. Unfortunately I chose poorly in some cases. I can't figure out how to get those primary keys changed to the correct ones. I've tried recreating the ODBC connection, tried a new database and new ODBC connection, tried killing the connection waiting a day (for the new .db to download) and recreating the connection and trying it in a new database.I know access doesn't allow you the change the primary key in a linked table.
I have created a very simple Access database with a CompaniesTbl, ContactsTbl and CallsTbl. The database is used to record telesales contact with customers. The tables are set up like this:
The CompaniesTbl has an AutoNumber Primary Key field called CompanyID.
The ContactsTbl has an AutoNumber Primary Key field called ContactID and a foreign key called CompanyID.
The CallsTbl has an AutoNumber Primary Key field called CallID and foreign key called ContactID.
The relationships are set up in the relationships window and referential integrity imposed.
Twice now, while editing a contact record, an error has occured. Task Manager has had to be used to get out of it. The error is either a 'run-time error with a message that does not mean anything' or it says 'unrecognized database format' (!?).
If you open the database again the ContactID field in the Contacts Table is no longer a Primary Key field. If you look in the relationships window - the relationship between the Companies and Contacts tables no longer exists.
Any ideas much appreciated.
I am trying to change the button color on a subform if a related form data changes.Main form is products with a continuous subform with serial numbers of products i.e, serial number, location, price and a button to add addtional issues if there are any for this particular serial number (this will open up another form related to the serial number so I can add an issues if there are any).The reason I would like the button to be a different color is so I can quickly see if there are any additional notes been added to the serial number. Just in case you may ask why not add the field to the continuos form is that the issues and be quite lengthy and there may be lots of serial numbers on the form
2346 location warehouse price 29.99 (button - green)
2347 location shop price 29.99 (button - red)
I have not done much work in later version of Access. Now I found if I change a design in one form and similar forms (names are different) which are linked to the same tables got changed as well without openning them up and making changes. Is this something new with Access 2003?
Thank you very much for help.
Hi,
May I know how do I go about getting the last value of the primary key that I get inserted? I need this PK to link the parent and child tables together.
Thanks for any valuable help!
Hello
I have a problem with a primary key. A table called relaties has a column relatienumber with date type: number, which is also the primary key. When a button is pressed, a function searches info from the table relaties.
The problem is the following: I figured out that when i remove the primary key constraint from the column relatienumber and save the table. the function doesn't work anymore, also when i remake the primary key for this column. How is this possible, because i changed nothing futher.
This is important because i want to change to access db to mssql db and in mssql the function also doesn't work.
It has something to do with this primary key constraint, i think :)
Who has the info???
Is it important that primary key would be a number for performance purposes?
How do I set 2 different fields as one primary key?
How do I set 2 different fields as two separate primary keys?
when defining tables, i normally set an autonumber as a primary key. However, i am working on a particular table, and i would like that no record will have identical values, in three particular fields
aaa bbb 123
aaa ccc 234
ddd fff 555
aaa bbb 123 <- i want to prevent this since a record with those three values already exists
Shall i set these three fields together as a primary key, or can i leave the autonumber as primary key and prevent such duplication in any manner ?
Thank You
Can a PK be a calculated field?
I want to be able create a PK that consists of three strings being added together. The first two would be collected from cascading combo boxes. The third field is a simple text field.
I would then run DLookup to make sure the final string is unique.
I have reasons for not using an autonumber field (there are compatibility issues with existing tables from a much older dbase database).
I have a database that I got up and running but one problem. I have as the primary key a social security number, but I can't seem to be able to add additional information to the same person. All I can seem to do is just replace or edit the one record. Is there a way that I can have more then one order for an individual instead of taking the primary key off of social security numbers?
Do I need a seperate form to enter information because I am currently workin with one form where I can enter and edit the information but no duplicates so lets say if my social was 099-999-9999 I can't add an additional order it seem unl.ess I make a whole new record and make a dummy number...
I have a tblState, every state can have multiple Counties, and every County can have multiple Municipalities
This should be a One to Many to Many, right????
However, several states might have the same County name
In order to resolve this, should I add 2 primary Keys in the tblCounty????
If so, I already tried and couldn’t configure it.
I have attached a sample DB
You can open the relationship view for a better explanation
I appreciate any kind of support
Thank you
Joe
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 RelatedHi all,
Ive written a program in .net and am currently using an access db as the backend.
In the db I had 200 test records which i deleted to get the db ready for deployment. When i add a new record now the primary key on my "booking" table carries on from the old test number so 201.
Can I resest this so it starts back at #1 as once the db gets updated, the program i wrote then takes the data and updates the accounting system and uses the primary key as the invoice number and so I would like to start at 1.
Thanks in advance
Scott
hey guys. i have a field by the name of ISSUE. i am using this afterupdate event procedure
which is as follow:
Private Sub ISSUE_AfterUpdate()
If Not IsNull(ISSUE) Then
ISSUE = Format(ISSUE, "000000")
End If
End Sub
if a user enter like (23) in my ISSUE field my procedure will run and it will replace
the number by (000023) to make it a six digit. i wanted to create a primary key so then a user cannot enter the same number again
or cannot duplicate a record but i cannot because primary key cannot take a null value. Can you all tell me anything to add in my procedure so that once the number is entered like (23)
another user cannot enter (23) again in the record.
Thanks
Hi
I have a little problem. I have a database with two tables: Nachweis and Entschuldigungen. In the first one u can add the records of what u have done for every day, the second one is for the days when u were absent.
I need to let the user to add in the second table only records which are NOT included in the first one. So if i have records for 10.10.2006 in the first table, i can't add records for 10.10.2006 in the second one. I wanna do it with help of the Compound Primary Key. But i don't even know where i should start.
Any help will be appreciated
Thanks in advance
Could anyone tell me about the pros and cons of Primary Key and Indexing?
View 3 Replies View Relatedit wuld be helpful if any of u can tell me how to link tables to query using primary key and foreign key or web page where i can solve my problem.
View 1 Replies View Related:eek: I have an order database via Access 2000, the backend became corrupted. I ran a a software program to recover the data. It recovered the data, but the autonumber was changed to just number and it removed primary key setting to the same field. I created a duplicate table, then ran an append query from the old table to the new one. The autonumber works, but I get the following error when I define the Primary Key and try to save...
"The changes you requested to the table were not successful, because they would create duplicate values in the index, primary key or relationship. " I cannot find any duplicates, and there is no relationships. Any suggestions? THANKS in advance for your help. Cathi
Hi, I have current yards table with Trailer number as the primary key and which cannot have duplicates. When this trailer leaves the yard we send this record to History table. Now if a trailer comes again and leaves History table is not accepting as it is duplicating. So can anyone help in designing table so that it accepts duplicate trailer numbers only in the history table.
Thank You,
Naveen
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