Access: how to insert a calculated field from one table into another

0
votes

I have a calculated field in table A.

Now I want to insert this field as value into table B.

How can I do that?

PS: Access will prevent you from inserting a calculated field with SQL Insert into.

Any method to bypass the limitation?

Calculated Field in Table A: IIf([Type]="aa",[Amt_A],[Amt_B])

Yes, I could insert three fields (Type, Amt_A, Amt_B) again in Table B, then insert another calculated field to get what I want, but I was wondering if there is any easier way for me to insert the value directly.

Really appreciate!

1
ms-access
Please show us what you tried so far (your query). - Unhandled Exception
Calculated fields belong in queries, not tables. - Andre
Sorry, I misunderstood before. Couple of days earlier, I tried to insert a table into another and Access prevent me from doing so with debug information regarding "Calculated fields...". But I tried today with different tables and it actually worked...I was still confused though... - Sylvia Liu
It sounds like you might have some database design problems. Have you studied database normalization? essentialsql.com/… - HackSlash

1 Answers

0
votes

Occasionally find out the trick myself, don't know whether it is widely known, I still would like to share this. So "INSERT INTO" query in Access can include some calculated area, however "SELECT INTO" does not work. So if you want to only "SELECT INTO" one calculated field, you could "INSERT INTO" instead and "DELETE * FROM" after process complete.