Update Append Query

[kabir_hussein]

Hi

Is there any way where you can append query but just add all the new data added to a form.

at present my query appends all the data from a given table-i get error message saying 69 of 71 records were not saved. this is probably cause they already exist from previous appending.

can anyone help

kabir

 

[FoFa]

Question, why is the form not adding the new record itself?
You can create an insert (append) query that pulls the data from the form fields instead of a table if you desire.
See this thread

 

[kabir_hussein]

No the form is adding new record to the required table, however what i was hoping to do is make the form send the data to another table as well as i need the information for something else later in the database, hence my append query

 

[kabir_hussein]

I am trying to save data in two places because the one data will be changed as times go one and the other data will be fixed.

The two tables will then be used later for calculations purposes.

if i wanted to write a query what criteria would i need?

thank you

 

[FoFa]

If you have a primary key on the one table (T1) (where the form stores the data) and a matching foreign/primary key on the other table (T2), you could write your query to insert those records that do not exist. But this is not the best approach (but we don't always use the best approach). You could use your form to set a YES/NO field on the T1 table, use a query to detect those set, insert them into T2 and reset the flag. You could base a query off the form itself (like in the link in my first post to you) and execute it on insert. You could like I said at first base a query on inserting rows that are not currently there by keys.

T1 PK, C1, C2 - PK primary Key, Cn some column
T2, PK, FK, C1, C2 PK primary key, FK foregin Key

INSERT INTO T2
(FK, C1, C2)
SELECT PK, C1, C2 from T1 left join T2 on T1.PK = T2.FK WHERE T2.FK IS NULL

You might have to play with the syntax a bit, but that should give you the idea, but it assumes you have keys to use to do this with.

 

[kabir_hussein]

Hi i have kind of solved my apped query problem but i was hoping if there any code where if there is any new data that the append query will only try save the new data.

if not do you have any suggestions pls

 

[kabir_hussein]

code i am using-but not working

Hi this is the code i am using but it doesnt seem to work

Where am i going wrong

INSERT INTO tenderbackup ( TenderlinkID, PartID, SupplierID, [Lead Week], [Basis Of Supply], [Tender Quantity], [Tender Unit Price], [Target Unit Price], [Tender Cost], [Cross Reference to Cleint Spec], TenderID )
SELECT tenderlink.TenderlinkID, tenderlink.PartID, tenderlink.SupplierID, tenderlink.[Lead Week], tenderlink.[Basis Of Supply], tenderlink.[Tender Quantity], tenderlink.[Tender Unit Price], tenderlink.[Target Unit Price], tenderlink.[Tender Cost], tenderlink.[Cross Reference to Cleint Spec], tenderlink.TenderID
FROM tenderlink
WHERE (((tenderlink.TenderlinkID)="where Forms![tender1]![TenderlinkID] not In (select Forms![tender1]![TenderlinkID] )") AND ((tenderlink.TenderID)=[Forms]![tender]![TenderID]));

 

[FoFa]

INSERT INTO tenderbackup ( TenderlinkID, PartID, SupplierID, [Lead Week], [Basis Of Supply], [Tender Quantity], [Tender Unit Price], [Target Unit Price], [Tender Cost], [Cross Reference to Cleint Spec], TenderID )
SELECT tenderlink.TenderlinkID, tenderlink.PartID, tenderlink.SupplierID, tenderlink.[Lead Week], tenderlink.[Basis Of Supply], tenderlink.[Tender Quantity], tenderlink.[Tender Unit Price], tenderlink.[Target Unit Price], tenderlink.[Tender Cost], tenderlink.[Cross Reference to Cleint Spec], tenderlink.TenderID
FROM tenderlink
WHERE ((tenderlink.TenderlinkID) not In (select tenderbackup.TenderID from tenderbackup));

 

[kabir_hussein]

i have done what you said but now it does not save on my back up table, i do not get error messages or anything.

the data is saved on the main table but not the backup table.

 

[FoFa]

The data has to exist in your primary table before it will detect it. If you run this query before the data is actually saved in your primary table, the query will not see it, and I suspect that is what is happening. You would better served by running this query after they are complete (if they enter 5 records, this query would move all 5 at the same time) then you only have to run this once instead of for each new record. Also if the tenderID exist in the backup table, it will not move it.

 

[kabir_hussein]

Hi

how i have done the save button is i have written code that the button saves the data onto the main table first and then runs the query.

so data does exist in the main table.
i have tested to see if the data is saved onto main table and it is but not the backup table. what could be wrong :-(

 

[FoFa]

Have you tried putting a doevents between your save and query run to make sure Access is "caught up"? I have noticed sometimes certain functions in Access do not occur when we think they do. Just because you go in and see the data after the fact, does not mean Access had it there when the query was run. Also look at what else may stop it from the moving the data (like TenderID exists in the backup table).

 

[kabir_hussein]

TenderID does sometime exist in the tender backup hence i am trying to use the tenderlink as a way of checking if it already is in the database.

tenderlinkID is the PK and tenderID is the FK, does this mean i am doing something wrong.

i am trying to make the code reference to the tenderlinkID and if it is not in the backup table is it then added

Am i going wrong some where

 

[FoFa]

WHERE ((tenderlink.TenderlinkID) not In (select tenderbackup.TenderID from tenderbackup));

This WHERE clause says to select those records in the tenderlink table when the tenderlinkID is not in the tenderbackup (as tenderID). So IF you record is in your tenderlink table, and the tenderlinkID in the tenderlink table is NOT in the tenderbackup table in the tenderID field, it should populate the data.

 

[kabir_hussein]

the where statement is to add new data.

it should if it worked add all tenderlinkID from the tenderlink table that are not in the tenderbackup.

this is because it is an append query and at present the append query trys to add all the data from tenderlink, thus producing an error message saying that the append query can not add 68(or so records) to the database. this is because they already exist

thank you for all your help so far i am very greatful