Calculated field - another table

[paddydive]

Hi,

I have 2 Tables Emp_add and State.

Emp_add Table contains details of employee address
State table contains City and its respective State

I am creating a Master Table which has details off all employees which comes from Employee Table.

what I am trying to achieve is in my Master table there should be a calculated field named as State which will pickup the correct state name from "State" table based on the City.

Sorry I am new to Access but in terms of Excel, it is just like Vlookup if we change the city the state will update automatically.

Is there any way we can achieve this...

 

[Joe4]

You would do it in the table. There should be no dependencies between fields in properly created normalized database.
You would do it in a query, doing a left join between these two tables on the City field.

However, I would strongly caution against doing this. The reason being is different states can have some of the same city names. Here is the US, a ton of States have city names like Springfield or Washington.
What you typically see if the complete address exist in one table (and not a link to another table like you are describing).
What you are describing would only work well if City name is a Unique field, which it really isn't guaranteed to be.

 

[paddydive]

The reason being is different states can have some of the same city names. Here is the US, a ton of States have city names like Springfield or Washington.
What you typically see if the complete address exist in one table (and not a link to another table like you are describing).
What you are describing would only work well if City name is a Unique field, which it really isn't guaranteed to be.

Thanks Joe4 for quick reply.

Yes I do have unique City name ... and

what I am really trying to achieve is to create a calculate field which will automatically update the State based on the City.

 

[Joe4]

what I am really trying to achieve is to create a calculate field which will automatically update the State based on the City.

Calculations should always be done on the Query level, not on the Table level.

Note that while newer version of Access allow calculated fields at the Table level, they should generally be avoided, for the following reasons:
- they have some pretty strict limitations (I don't believe you can involve other tables in these calculations, only fields from the current table)
- this kind of violates the rules of normalization, and no other database program allows for this (so if you ever needed to convert the Access database to something else like SQL, it wouldn't work)

So even if you wanted to do this at the Table level, I do not believe you can for the first reason I specified.
In your query, you would just join this table with your State table, using a Left Join on the City field.

 

[paddydive]

Thanks Joe4,

What would you suggest, if I have to do below in Access

1) Import a list from excel to Access in a Table (Let's say Table1)

2) Create a Master table (TableMaster) which has all the records from the imported excel.

3) In addition [TableMaster] has many other columns which are calculated based on the already existing (imported) column
For Example : [NewStartDt] is depend and calculated as " If [ActStartDt]<>"",[ActStartDt],[Start]"

...... [ActStartDt] and [Start] column are already there in import


4) There are other column which lookups data from another table

 

[Joe4]

All calculations and "lookups" should be done at the Query level.
Calculations are done in "calculated fields" in a Query.
"Lookups" to other Tables are done by joining the Tables together in a Query.

Note that you can use Queries as the data source for all Forms, Reports, and Exports (just as easily as you use Tables for those things).