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.
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
Hi there,
was wondering if there is way to link tables in Microsoft Access, if the primary key is not matching 100%, for example lets assume that we have two table, with fruit type as their content.
Table1
FruitType
ORANGE
APPLE
PEAR
Table 2
FruitType
ORAN
PPLE
EAR
as you can see the content in the second table is somewhat incomplete or corrupted "Orange" is missing an "E", "Apple" is missing an "A", and "Pear" is missing a "P".
Thanking in advance
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 RelatedJust 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
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!
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.
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!
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.
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,
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?
View 7 Replies View RelatedI 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
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 RelatedI 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.
At the moment i have an exisiting database that was not created by me. It consits of about 7 tables and a main table. Each table has a Primary Key that is a joining field. The Main form is built with subforms and when i enter a number in the primary key filed of the main table it enters that number in all the other primary keys in the other subforms.
i have now tried to create another subform to put into the main form but i cannot get this "link" between the primary fields to work.
can someone please help.
I am trying to link Primary Key (autonumber) to a double field in another table as a FK.
I get this error.
Relationship must be on the same number of fields with the same data types
What is the problem? There is no problem with the data itself...it should work. But is it a problem with the autonumber?
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 ...
i have 4 supplier tables with identical field names but different databases in sql.I want to have them all in one table and only want the information for reference i do not want to edit any of the data.I need to create a new primary key number for the complete table. The data will need to be refreshed as the data comes from MMS Sage looking at company PLsuppliers.
I have tried a linked SQL union view- but this has no primary key.I need to keep the Primary key consistent as the data saved in other tables will use this field.The overall goal is creating a Purchase order system and this list will be my complete supplier list.
I have 2 identical database in terms of structure but it differs in data.
Basically I would like to import data from subDatabase to mainDatabase and ensuring that there are no duplicate records.
I have used the "link to datasource method" through importing the tables to have the "updating" function.
However, this method also means that the records in mainDatabase are also imported over to subDatabase which I do not want.
Is there a method to ensure that the records are shared/update one way only? (i.e. import from subDatabase to mainDatabase and not main to sub?)
Currently trying to build a database for customer management and order placement/tracking. Want to set a couple of rules so that if I for instance click yes of billing and shipping address the same that the database will automatically fill the shipping address with the data I inputted for the billing address in the same table.
The other issue I can see I'll run into is, I want to be able to select one of the company ID's (made up of a three letter abbreviation of the full company name) in the product ordering table and it will automatically fill in the rest of the customer data (phone, email, address etc) data into that form.
I am doing a project where we are collecting home owner data and information on all the dogs in the household. The data for homeowner has an autonumber primary key because no field is unique or has been consistently collected. I am struggling to link the data for the dogs to the owners because an autonumber primary key will not work since not all homes have dogs. I need to have this set up so that people who are not tech savvy can look up each homeowner (or dog) and get the dog (or home owner) information. And to make things even more fun we need up to 15 potential dogs per household each of whom will have 18 different pieces of data collected.
It looks a little like this (and you can see my not matching ID issue):
Homeowner table
ID First Name Last Name...........Total Dogs
1 Max Maximus 5
2 Min Minimus 0
3 Mus Musculus 1
Dogs 1-5 table
ID Date Dog 1 Name Dog 2 Name .......Dog 5 Name
1 (Max's) 4/11/14 Bobby Billy Betty
2 (Mus's) 4/11/14 Jojo
Min will have no dog records at all, just home information.how to link the dog's to the homeowners .
I have been asked to design a database to replace our old excel sheet to log one of our customers server builds. It a four noded system and i require a seperate log for each node serial models of parts etc, qa checks.
I intitial started by just using one table but forgot the limit was 255 rows, i need about 600 - 700. So my plan is now to transfer to indivial table for each node. Then, a general table named Main for all my other details.
Although this seems simple, i now have the annoying task of being able to link them all together and produce a single PDF file to send to our customer whilst only entering our system serial number once to produce this.
My basic understanding will be to create a System Serial Number field in each table a link it via relationships, but if i produce a report with everything i need would it input my details for all four nodes? If so how would i set this up?
I will be splitting the database once completed and then making a ACCDE file for my techs to use.
I have mapped everything out and built it in Access (but haven't entered any data yet). My question relates to structure. The data is based on artifacts, each piece is recorded with an automatically generated primary key in the main table. There are a number of features (type, decoration etc.) that are documented in related tables (included in/linked to the main table with foreign keys). Aside from these (the features I will query on), each piece has a number of characteristics to be recorded (size, colour etc.). Because of form/table size, I have broken the information into three tables (a main table with ID details, a second with Sizing and Dimensions, and a third with Materials etc.).
My question is: Can the main ID primary key from the main ID table also be used as the primary key in the 2 other basic information tables, with these 2 linked to the main table in one-to-one relationships (so it's an auto-number in the main table and an integer in the other two). This to me would be much easier to navigate than to have one long giant table with all these basic details.
I have created a main data entry form with a "navigation form". 5 tabs/pages. Tab/Page 1 = main ID. 2 and 3 are the two broken apart tables mentioned above. 4 and 5 are forms based on related tables that have their own subforms included. Both 4 and 5 are based on tables in which the main ID primary key is entered as a foreign key (in one-to-many relationships with the main table). Since this was done with a navigation form (instead of adding each of the 4 as subforms), will it still allow me to enter all relevant details for one piece into each tab/page, and save it all together automatically (whenever I enter a new piece into the main ID form)? Will the primary key be automatically generated throughout, or do I have to enter it manually in each tab/page?
My Access 2010 skill level is intermediate.I have a table with 4 fields.
EmployeeID (Primary Key)
LastName
FirstName
SupervisorID
The problem I have is that in the Supervisor field, it identifies the supervisor by his/her EmployeeID. I would like to create a query or merge tables so that the SupervisorID can look for a matching value in the EmployeeID field and return the last name/first name.
I am importing data from excel and I want to put a primary key on a column but it doesnt let me saying "primary key cant contain a Null value", coz I have a "NO" or "O" value. But i dont understand why is it a problem as I have specified the field as text not number.Also is it necessary for the table to have a key ?
View 14 Replies View Related