problemd with update query

[mohammadagul]

Evening All,

I have this problem in my update query. I would try to be as simple as possible.

I have two tables

Table 1 : tblProductSub
ProductID
ProductName
SerialNo
Description
Reorderlevel

Table2 : tblSupplierSubInvoice
ProductID
ProductName
SerialNo
Description
Reorderlevel

I have created an update query which takes the values from tblsupplierSubInvoice into tblProductSub

In the initial steps when I try to run the query all the data is perfectly transferred. But when I create a new Supplier Invoice and try to update the ?tblProductsSub? table it actually take the last entry made and update all the values to that value. Where as it should only add the new entry and leave the rest of the entries as they were.

I am herewith attaching the database. Please let me know as what am I doing wrong here.

Try adding a new Supplier Invoice and then running the update query. You will see that I will update all the records to the last record.

Thanks

 

[Pat Hartman]

1. You should not duplicate this data in two tables. It should only exist in a single table.
2. Update queries update existing records. Append queries add new records. You are using the word update query but you are talking about appending records. Are you confused?

 

[mohammadagul]

infect i am..

if you ask help in msaccees for update table u will find the updatetable help file in ms access. i actually used that help to create this query.

secondly th reason i am using this data in two separate table is that the Supplier invoice table is already full of fields about 100. furthermore i want to use the product actuall table to make union queries with invoce table to find my inventory balance.

hope you understand my point. as far as transferring the data is concerned , what should i do, either use the append query or update query. Help!!!

 

[Pat Hartman]

what should i do, either use the append query or update query. Help!!!

- I already told you. Use an update query if you want to modify existing rows. Use an append query if you want to add new rows. See this post if you may need to do both in the same query.
Update query that can append new rows

 

[mohammadagul]

as per abovementioned link i am doing the same . not a single thing diffrenet then mentioned. but still my update query is not givign me the correct answer