Lookup tables in Access

[rosana_alam]

Hi,
I am trying to replicate an excel spreadsheet into Ms Access so that we can run the database quickly. Can someone tell me how i can do a vlookup in Access?

 

[JamesMcS]

Hi, welcome to the forum!

Databases don't work in quite the same way as a spreadsheet. If you're looking for a single value, you have to navgate to the correct record first and then get the value out of the field you want. That's a very vague description but if you can describe your project in more detail we can give yoyou some more info.

 

[JamesMcS]

Although - there is a function called DLookup which sort of works in the same way - have a butcher's in the help file at the syntax and see if that does what you need.

 

[rosana_alam]

Hi James,

thanks for the start! to give more details. i have a list of data almost 40,000 lines downloaded from SAP. this list is a report on ytd depreciation on assets. the dep formula is diff from diff types of assets. in excel every month we need to manually calculate the dep amounts per asset to ensure there are no errors in SAP.
I think if i can replicate the calculations in Access the whole time will become much faster. currently it takes us almost a day just to update and do the checks in this excel file.
for starters i have two tables.
table a) shows 2 columns: dep key and dep method (both text)
table b) has dep key but the dep method is not there. so i want to run a query which could vlookup the dep method using dep key as the factor.

Hope you can help!!!

Many thanks

Regards,

Rosana

 

[stopher]

James is right that Dlookup is probably the closest thing to Vlookup. However, this isn't the way to go in a database. Queries do all the hard work for you in Access.

So:

• Create a new query
• Add both tables to the query
• Create a relationship between the two tables in the query by dragging the key from one table to the key on the other (a line will be created between the two)
• Choose the fields you want from each table
• Run the query

Here's a brief example of the join.

hth
Chris

 

[spikepl]

(OT , and in no way denigrating your initiative to streamline a process, for which there evidently is a need) :

Thanks for an interesting example of corporate IT : "every month we need to manually calculate the dep amounts per asset to ensure there are no errors in SAP"