I have some employee data to import into Access but this data does not have employee ID numbers. I would like to assign employeeID numbers to each person then set the employeeID field as the primary key.
Here is what I had in mind. I would create a table that defines each employee with an EmployeeID number. Then in the table containing the imported data, the first field would be called EmployeeID. After importing the Excel data into this table, initially this EmployeeID column would be blank because the data in the Excel file doesn't have an employeeID. But the employeeID field would have a VLOOKUP formula that would look at each person's name and cross reference that with the employeeID table in order to obtain the employee ID for each person on each row. Then the VLOOKUP would return the employeeID number. Furthermore, I would like to have this employeeID field set as the primary key.
Can you do a VLOOKUP in Access? If so, can the employeeID field still be used as the primary key field, even though its values would be determined by a VLOOKUP?
When I enter data, Album ID increases every time I jump to the next field. For example, when I enter: AlbumTitle, Song, Rating, Album ID is already at 4 and this is just the first record.
My table called Table1 has a primary key field (called RefID) with AutoNumber format. My problem is I want duplicate value of RefID field to another field called DupID. I want DupID to be changed, but I don't want any change on RefID field whenever I make changes.
hi ! i'm very "fresh" in access , and i want to learn how to build something like vlookup . i have 2 tables : car , general
i want to get a query that give me :
id number plate color 1 111111 blue 2 222222 black 3 999999 not found
i want to see all the id's and all th number plate and the color's. in id=3 there is no color in the table , and i want to see this line in my query with the words "not found" at the color column
I've created a DB to track costs of subcontractors hauling loads for a warehouse that services a number of stores. I've created a stores table and a hauliers table. Hauliers will charge different rates to other hauliers for delivering the same stores. How do I key in a haulier ref and a store number and get the database to return the cost of this trip. It would be easy in Excel with a VLookup, but how do I do it in Access.
Can I create a query with hauliers down the row headings and stores along the column headings from the existing tables?
I have the SQL statement for the VLookup (Dlookup), but I don't know where to enter the statement. Please tell me exactly where I need to enter the statement. In the SQL Specific menu? Union? Pass Through? Data Definition? Or some place else?
UPDATE Table1 INNER JOIN Table2 ON Table1.material = Table2.Material SET Table2.Serial = [Table1]![serial];
Also, do I need the ";" at the end of the statement?
Access question Does anyone know how to do a simple query which will change a number in the data from one measuring scheme to another like centimeter to inches I don't want a math formula but something like a vlookup in excel?
or something like that? in ACCESS? Edit/Delete Message
Dear All, I have looked at many of the threads on Vlookup on the net but I have not solved my problem:
I have two Tables; Table 1 Contains a translation/link from a local product categories to international standard categories, Country - Local Category - International Category Germany - AA - XX Germany - AB - XH UK - FF - XX UK - HG - XX USA - FG - XH Note: local categories are not uniek
Table 2 Contains sales values by Local Categories Country - Local Category - Sales Value Germany - AA - 20 Germany - AA - 10 USA - FG - 25 UK - HG - 15
What I now want is to create a query which will provide the following result: Country - International Category - Sales Value Germany - XX - 30 USA - XH - 25 UK - XX - 15 Because Local categories are not uniek in the link table a normal query link returns multiple times the sales value and I want the query to return one (linked to the first it sees in the link table, like the vlookup from Excell)
I have an invoice table and a project table. A 1 to Many relationship from Project to Invoice respectively.
Invoice Table: InvoiceID ProjectID InvoiceAmount Status (Paid, Not Paid)
I am trying to create a report that lists Total Invoiced and Total Paid by Project.
I've been trying to do it in 2 separate queries (Total Sum of invoice and Total Sum of invoice where Status is Paid) and linking them but that causes multiple records.
Is there a way to do it in one query using a constraint on the second "Total Sum of Invoices" (i.e. where status is paid)?
I need to use dlookup in a query to populate a new field "number" with values obtained using a current field "letter". I have a (lookup) table as follows: Col1 = A, B, C, D and Col2 = 1, 2, 3, 4 which of course gives the number code corresponding to each letter.
In Excel, I simply use the formula: vlookup("letter", "lookuptableref", 2, false) and it populates my new field number with the correct values.
Can anyone help me with the equivalent in Access using dlookup?
I am copying and pasting the results of an Access query to Excel for the purposes of using that data as the source for a VLOOKUP. However, it seems that no matter what I do the cells that were pasted from Access do not correspond to the same values that are in the lookup table in Excel. For example, I want to find the value ABC from column A of the pasted Access data to find the corresponding ABC in the Excel array. It doesn't consider it a match even though both cells are formatted the same way. I've even gone so far to test it by putting a logical comparison of the specific cells in another cell and it claims they are not the same even though the values are exactly the same. The only thing that seems to work is if I manually type over the value pasted from Access with the same value that is already in the cell. Then the VLOOKUP works. However, I am not about to go typing all these values manually. That's why I used an Access query. How do I get Excel to recognize that these values indeed match?
Hello, I have a basic question about choosing the primary key for a table. Now I am not really concerned on the particular field(s) chosen to be the primary key, but rather the potential states, if you will, that the key can be in.
Say I choose a multiple field primary key for a contact table of, say, LastName, FirstName, and PhoneNum.
I thought during an Access db project I was updating awhile ago using Access 97, it didn't require EVERY field of the multiple primary key to be filled in, as long as there was enough to make a unique key.
I am wondering if this is still the case for Access2000 primarily. From what I have read it appears that this is not the case or at least it is strongly not recommended. For my example would it be to have FirstName and LastName filled in but not PhoneNum. Then, if a new Contact were added with the same LastName and FirstName but a PhoneNum was also added, the PrimaryKey would essentially be unique still.
I know this definitely isn't the best example but it still states my point.
If anybody has any wisdom on the subject I'd greatly appreciate it!
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 need a help. I have one table that has a field called "Initial ZIP CODE", another field called "Final ZIP CODE", and a field called "AREA".
I have a data base that will give me the specific "ZIP CODE", that will be between the "Initial ZIP CODE" and "Final ZIP CODE", and I want to get the "AREA".
I am an intermediate level Access user and I am trying to have one of my tables lookup data in another table and populate the corresponding data. For example, TABLE 1 and TABLE 2 both have a common field, MATERIAL field. TABLE 1 contains a SERIAL CODE field which is all the serial codes and TABLE 2 does not. I am trying to copy over the serial codes for 1000s of lines of data from TABLE 1 to TABLE 2 via the corresponding MATERIAL field. How exactly can I do this? Please be specific and do not leave any steps out if I need to enter functions, etc. Please tell me exactly where the process needs to be implemented.
I need want to use the vlookup function in Access 2010 when creating a query. I am doing this as an expression in design view of my query.
I want the field in which I am creating this question, to first, ask what state I am looking for (I have put this in the criteria box of the field "[State]"), then search another table (I have it created already, it is a list of the states) for that state and plug the cell with the name of that state into this new query cell.
I am working on recreating an excel file in access. My excel files uses the Vlook up function to get data from a table for calculations.I am calculating Fruit Solids.
(BRIX VALUE references a cell, and quantity references a cell just used the names instead of cell references).The Brix table has 751 entries. The headers for the following tables are "Brix, Gravity, Weight, GFL, LBS, FS/Gal".So the look up table takes the BRIX value, finds it in the first column then takes the value in the 6th column(FS/Gal) then multiples that by the Quantity inserted in the other table. I didnt create the Vlook up access form so I am trying to get an understanding and recreate it in access.
In access i made a table called BRIX to store the Brix table information like the one in excel.Then i have fields in a different table where you enter the quantity, and brix value of the product. I now need to make the Vlook Up function in excel work for my access data.
Is there a way mimic the vlookup function of Excel in Access using SQL? What I am trying to do is create a table with planning values and based on the field title to grab a particular value in the two-column table.
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.
I have to tables. One named wire and the other named Input. As my title says there is not unique ID for the tables to relate.. the only thing to relate is one ID where is not unique for all the rows
In the Wire Table I have 3 rows using the same ID, lets say 123. In the Input Table I have 1 row using the same ID as above, i.e. 123
I need to select a couple of fields from Wire Table and 1 field from Input Table The only relationship I have is the ID 123.... since the ID in Wire Table is repeated in 3 rows.... I would like to use the same value from the Input Table to be the same for the 3 rows in the other table.. Below is an example:
Wire Table:
ID Group 123 A 123 B 123 C
Input Table ID Description 123 Analog
My wanted result would be
Group Description A Analog B Analog C Analog
I tried using the Left join or Join function, but somehow the resulting table has many duplicates of the rows...such as:
Group Description A Analog A Analog A Analog A Analog A Analog B Analog B Analog B Analog B Analog ...
Any Idea on whats going on and how can i solve this problem????
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.
Is there any way to fill cells in Access tables with the results of using vLookup in an Excel worksheet? I'm trying to vLookup the quantity of products sold in the worksheet and make that number available on an Access table and form. Can the two aps jibe like this?
I am creating a staffing database and currently have two tables:
Table 1 - Staff - which contains staff information ie. First Name, Last Name, Other Names
Table 2 - Staff Work History - which displays the shifts worked by each staff member.
In Table 2 - I have "Staff Name" which is populated using VLOOKUP and searching for the Staff Name from Table 1 - First Name, Last Name, Other Name.
I want Table 2 column "Staff Name" to display all parts of the name - not just display then when on the dropdown when you select the staff person. Currently only the First Name is displayed once you have chosen the staff person.
I have a database with 200.000 records, how I Add Primary Key Field. I'm getting error "File sharing count exceeded..", tried to increase "MaxLocksPerFile" registry , but without success.
