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]
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?
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.
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?
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
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!
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.
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.
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?
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?
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)
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.
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 think that creating a new primary key is the only solution to this problem, but I would love to have other input, including potential pitfalls I might encounter in this process.
Here is the scenario: I have spent the past year creating a "survey based" database in my "spare" time at work. The database holds detailed information about nutrition questionnaires given to children over the years that they are in our program. I have based the structure on using ClientID as the primary key. ClientID = each individual's social security number.
We have another database that holds all the client's information. This is the database where any updates are done to their information. I export the client information (firstname, lastname, birthdate, socialsecurity, plus a few more) that I need into excel. Then I import that info into my database on a regular basis using an update/append query.
We started using the database in September and all was going well until a few weeks ago when I imported data and found a name that was entered twice. On looking closer, I found that the person had two different ClientID's. How could this happen...? Well, in our other database, if a child doesn't have a social security number yet, or we weren't given it, the program creates a fake social security number AAA-##-####. I knew this from the beginning, but wasn't aware of any problem. However, I was missing a significant piece of information. When the child finally does receive a SS#, we go in and change the number in the program!!!! At first I was told this rarely happens (I had about 6 duplicates the first time), but today I ran another import and found 90 instances of duplicated names!
There are just over 600 records in the database. Many of these have had information that is unique to this database entered in a related table, so I need to be sure I don't lose any of that data. There is data in other tables that I could import again if I have to.
Here are my options as I see them:
1. Leave the primary key alone and delete the duplicated values with every update.
2. Create a new unique autonumber primary key, knowing I will have to delete relationships and rebuild them. I would leave the clientID field in the system as it is the only way we have to determine the difference between two children with the same names and birthdates.
3. Run quietly away hoping they don't notice the problem until I am long gone. ;)
What would you do in these circumstances? If you agree with my assessment that I really should create a new primary key, what steps would you take to minimize problems both during the process and in the future?
And yes, I wish about 12 months ago, I had asked if the social security number was ever updated, but of course no one thought to bring that up when we were discussing the database design! :mad:
I'm pretty new to access and I've been working on a pretty complex project...
Anyways, my first problem I'm having is that I want Microsoft Access to automatically create a key number. This number depends only on the year, and then it would concatonate another number.
Example: 5-3000, then 5-3001, 5-3002... The five corresponds to the year, and the other part is just incrementing by one for each new record.
What would be the best way to do this? I don't necessarily need the dash in there, it could simply read 53000, 53001, 53002, etc.
I need to create an incrementing number to be used as a primary key in a table, based on a few variables. The format will be YYMM0000. It represents the number of units received each month.
I can come up with the formula to create that string no problem, but I need to create some code to check the table MAIN for the field TRACKER_ID to find the last number and increment it by 1.
I am trying to create four tables: Company, Contact, Activities, and Opportunities.
I want them to relate hierarchically. A Company can have many contacts, contacts can have multiple Activities and Opportunities. But you can't have contacts without a company and you can't have Activities and Opportunities without having a contact. I want all PK's in all tables to link to one another, that you cannot create one without the other.
How I can do this in Access 2010?
YYMM00000-000000-A0000 CompanyID-ContactID-ActivityID or YYMM00000-000000-O0000 CompanyID-ContactID-OpportunityID
I have two tables linked to each other in one to many relationship. Instead of auto number, the date and shift (Text) is being used as the primary keys (Composite Primary Key). Here is the tables structures,
The tables Payouts and Bills has one to many relationship. One payout row can have many bills. The problem is that I want to start the Autonumber in bills table everyday from 1. As date and shift are different for every day so even if i start bills from 1 everyday, it wont make same primary key. I can do it manually but I want to make it automatically.
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.
I am creating a database for creating quotations. The quotation number is generated using the date, for example the first quote today would be quote number "05202015-1" because it is the first one today. The next quote today would be quote number "05202015-2" and so on. Is there a way to make access automatically generate these quote numbers based on the date?
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!
