Vlook up function for access Is this possible?

[Mast0645]

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.
=VLOOKUP(BRIXVALUE,'Brix Table'!$A$1:$F$752,6)*QUANTITY
(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.

Any suggestions?

I saw in other Vlook up query questions people just use a inner join but i know i am not just grabbing a single value I am some how doing a calculation. So really unsure how to make this work. I am doing an internship this concept is out of my reach for my access skills. Using Access 2007

 

[Mile-O]

Welcome to Access, which is not Excel. Something about the way you describe this project/issue suggests that you are approaching table design in Access the same way you may set up an Excel spreadsheet. This is not the approach to take.

There is no VLOOKUP() function in Access. There is domain aggregate function called DLookup(). It has three parameters. The field you want to look up; the table you are looking up; and the criteria you want to apply.

Also, for performing calculations on your data you would use a query on your table. You can't do calculations in a database table. As the name applies, tables are purely for data.

 

[Brianwarnock]

i am not just grabbing a single value

You are matching on a single value, which is what the join does, domain functions are heavy compared to using a join.

Your query will join the tables on Brix and then you will simply do the calculation on the quantity from tablea and the fsgal from the Brix table.

Brian

 

[gemma-the-husky]

also bear in mind that the right way to do this may not need a lookup at all.
you may be able to create a query that joins your tables tighter on appropriate values, and thereby returns the information you need.

as Mile-O said - Access is not Excel. Excel thinking is generally often not the right way to use Access.

 

[thmsjlmnt3953]

Looking at your problem id say you will be able to do this just with related tables and a query to calculate out the result. If you upload your db file i will have a look and make changes as needed and guide you through changes for future reference