Using query in a field

[zigza]

Hi,

I am new to MS Access so pardon me if my question looks so basic.

I am creating a table (Example : A) consisting 20 fields. One of the field has to pick value from another table's (B) field, if the value of the unique fields in both the tables are the same (Typical vlookup in excel).

Example:

Table A

Emp ID Emp Name
110 Rich
112 Dan

Table B
Emp ID Emp Grade
110 A
112 B

I want to bring Emp Grade in Table A as a field

Emp ID Emp Name Emp Grade

And whenever I change the Emp ID the grade has to get change dynamically.

I have created simple query by relating the two tables, but I am not sure how to bring it in a field of A.

Please help

 

[Minty]

You shouldn't store the data twice, either store it in Table A or Table B, not both. You can link table A to table B and query on that whenever you need it.

 

[zigza]

But that doesnt serve my purpose. I need to bring the data from another table. Typically how you do vlookup.

 

[Uncle Gizmo]

You are thinking in terms of Excel. Access is NOT a glorified spreadsheet, it is a database. You have to think differently, otherwise you might as well stay with excel. Minty has given you good advice.

Sent from my SM-G925F using Tapatalk

 

[zigza]

Thanks for your guidance. Apparently we deal with plenty of rows, hard formulas etc. Excel is not responding properly so we thought of shifting from Excel to Access. I understand that every tool will have their own limitations but just wondering if something can be done to do all those excel calculations here in the access.

 

[zigza]

Just a question, is it possible to use a query as a field? For example whatever I change in a field in a table, it should dynamically run the query by relating a table and populate the output in a field of the current table?

 

[stopher]

Just a question, is it possible to use a query as a field? For example whatever I change in a field in a table, it should dynamically run the query by relating a table and populate the output in a field of the current table?

As Minty said, you don't typically store the same data in two tables. The key is to use queries to show the data in the way you want to use it. So in your example you would create a query that shows the required columns form both tables in the same query. You would use this query wherever you need to e.g. in a report.

 

[Minty]

Think back to your Vlookup. That isn't actually storing the value you want it is calculating it and displaying it.

That is how you need to think of queries. And you can create calculated fields with those queries, so whenever they are run you are getting the correct result, not relying on another process to update a table value that possibly could be changed by another method.

 

[zigza]

Thank you guys, I found the way. I just used update command using inner join and it behaves like a vlookup

 

[zigza]

One more quesiton, sometimes while calculating a formula access is throwing "File sharing lock count exceeded. Increase MAXLOCKSPERFILE registry entry" error messsage. Any help?

 

[Uncle Gizmo]

See this Thread:-
Getting file sharing lock count exceeded error

 

[Minty]

If you are updating your whole main table every time you change one value in the underlying table you are not using this like a database.