MS Access - Calculated Field

[nikunjpg]

I want a calculated field in the MS Access Main table instead of having to run a query everytime. Can I have field in MS Acces table which contains a formula (vlookup) that is automatically calculated everytime I open the Table?
Please help

[GolferGuy]

It really depends on what the calculation is. Within an Access table, you have define a field as being a lookup field. For example, if there is a customer number field in the Invoice table, and you want that field to always show the CustomerName, rather than the CustomerNumber, then that field can be defined as a lookup field, using the contents of the field to obtain the CustomerName from the Customer table.
In design view of the table, put the cursor in the "CustomerNumber" field, then press F6 to put the cursor in the Field Properties portion of the window (at the bottom). Click on the "Lookup" tab, change the Display Control to "Combo Box", then use the Row Source to input the query to obtain the CustomerName from the Customer table.
You will need to change this example from the Customer stuff to fix whatever your actual field and table names are.
HTH,
Vic

[nikunjpg]

Thanks a tonne for the help, I need to insert the Dlookup formula which is similar to the vlookup formula could you please help me out with that too as I'm getting an error when I insert the value, range and column # in the Dlookup formula

[GolferGuy]

I need to see what you have done so far, what error message(s) you get, and then REALLY need a narative of what you actually want from this dlookup.

[nikunjpg]

I have two tables one of them has the values listed against numbers
1101 Jim
1102 Pat
1103 John
In the second table (which is the main table) I want that whenever 1101 is entered "Jim" should appear in the new column (which is a calculated field). In case of MS Excel I would use the following formula
VLOOKUP(A1,Sheet2!$A$1:$B$200,2,FALSE)
presuming that the data is in sheet 2 and value required in sheet 1 the lookup value has been entered in cell A1, range is Sheet2!$A$1:$B$200, value from 2nd column has to appear.

[GolferGuy]

Access will not do a calculated field in the table. To get what you want, you will have to use a form. If you can get by with just seeing "Jim" in the field where "1101" actually is, Access can do that. That is what I explained in my first answer to your question. If you want to see "1101" in one column, and "Jim" in the next column, that would be a form, or a query, but NOT a table. PLUS, any work around to put this data into the table would be very bad database design. If this is done with a query, then the person's name (Jim) would also be able to be updated. Here is the SQL of a test query I just created to show how this would be done in a query. This is nothing like what it looks like in Excel, and the sooner you stop trying to "think" in Excel, the sooner Access will start making sense.

SELECT tblData.DeptID, tblDept.DeptName,
     tblData.EmSrcID, tblData.DateEntered
FROM tblData INNER JOIN 
      tblDept ON tblData.DeptID = tblDept.DeptId;

Hope this helps,