How to copy field_a to field_b in the same table?

[halnurse]

I am trying to create a field in a table that is equal to another field in the same table. The data type from field_a is calculated and I need the data type of field_b to be a number. The data in field_a is already a number.

I am trying to reproduce the data in the second field in order to make a relationship with another table in my database and I cannot make a relationship with a calculated field.

I am relatively new to access. Please let me know if there is a way to make this happen or if there is an easier way. Thanks!

[CJ_London]

this demonstrates one of the issues of using calculated fields, another is it can't be indexed. So to get round the problem, you need a separate field and all sorts of additional functionality to maintain it. Would be better to calculate at the time of input and just store the value - or calculate as and when required in a query or on a form since the calculation can only be based on the values in that record

I don't use calculated fields however to add the calculated value to a new field you would

1. Add the new field_b in table design as the right sort of number (integer/long/double)
2. then use something like - hopefully will work with a calculated field

UPDATE myTable
SET field_b=field_a

however you can link two tables together based on a calculation, it does not need a predefined relationship. This one can only be created in the sql window and is not viewable in the query grid:

SELECT *
FROM TableA INNER JOIN Table B on TableA.fld1 & tableA.fld2=TableB.fldz

alternatively create two queries, both of which can be viewed in the query grid

Qry1:- SELECT *, fld1 & fld2 AS Link FROM TableA

Qry2: SELECT * FROM Qry1 INNER JOIN TableB ON Qry1.Link=TableB.fldz

[halnurse]

Thank you Ajax for your quick response and for your help. I have decide to stay away from the calculated field.
Thanks again!