Adding a new field that is calculated in a make table query

[RobNauticus]

Hello, I am trying to create a new table from a linked table that adds a new field that is calculated from two other fields in the linked table. As of right now I am trying to use an update query first that combines the linked table and a new table with the field that I want added. I created the relationship by copying the key fields from the linked table to the new one. I perform the calculation on the update query then that whole query is the basis for the make table query. Now every time I try run the update query, it says every record (the total number of records in the table) cannot be updated due to key violations. What am I doing wrong? Is there an easier way?

Any help would be appreciated...

Thanks a lot,
Rob

 

[Edski]

Rob,
I don't know what you are doing wrong but I don't know why you are linkg the two tables. This works for me:

Make Table Query(1)
SELECT Tbl_Link.* INTO tblNew
FROM Tbl_Link;

Update Table Query
SELECT Tbl_Link.*, [fld1]+[fld2] AS newField
FROM Tbl_Link;

Make Table Query(2)
SELECT qryUpdateTable.* INTO tblNew
FROM qryUpdateTable;

Am I missing something?

 

[RobNauticus]

So are you saying that I should create 2 make table queries and one update query? I was trying to make these queries in the standard design view of Access but I guess I should be using SQL? Anyways

I think this will help point me in the right direction.

Much Appreicated

 

[Edski]

HI Rob,
Sorry but the first Make Table query is not really needed. I don't know why I did that!

Also, there are no primary keys defined in the new table.
Cheers.