Simple Append Query

[chendes]

I am trying to do a simple append query to update a table. What I am trying to achieve is update a table when changes have been made to the table that I am appending from.

INSERT INTO TransactDetail
SELECT *
FROM Trans;
I would like to put a WHERE clause in
WHERE Trans.trdreferenceNo > TransactDetail.trdreferenceNo
This however does not work.
trdreferenceNo is a primary key.

Any help will be fantastic

 

[nicsin]

Hi chendes,

the structure of the INSERT query is


INSERT INTO tableName(fields separated with commas) VALUES (values separated with commas)


the number of values have to match the number of fields and the corresponding field type. Can you provide the tables' structure so we could have a better view?

good luck,
nicsin

A boy is smoking and leaving smoke rings into the air.

His girlfriend gets irritated with the smoke and says to her lover:
"Can't you see the warning written on the cigarettes packet, smoking is bad to
your health!"

The boy replies back:
"Darling, I am a programmer. We don't worry about warnin

 

[chendes]

here is the strucuture

Insert into Transactdetail (trdReference,trdTransDate,trdMembNo,trdCasualNo,trdActCode,trdDesc,trdHeads,trdValue)
SELECT*
FROM Trans

WHERE Trans.trdReference > Transactdatail.trdReference

Great Joke by the way

 

[nicsin]

From what I understand both of the tables have the same structure right? And the WHERE clause is for the SELECT query right? There issue here is that the SELECT query will probably return many records but the INSERT INTO only inserts one so we've got a conflict here. In order to help you, you have to provide more details about what is your situation for now and what do you want to do. I presume you have two tables TransactDetail and Trans, which have the same structure and you are trying to duplicate records from into the other. May I ask why? This is impossible with a single query since the INSERT only inserts one record at a time. What you can do is create a separate table using

SELECT * INTO duplicateTable
FROM Trans
WHERE Trans.trdReference >
(select max(Transactdetail.trdReference)
from Transactdatail)

This will create another table with the duplicated data but I am not sure why are you doing this in the first place so I don't know if it is helpful.

If, on the other hand, you want to use some vba code you can do what you want if you used a recordset to pull the data you want to duplicate and then put the INSERT query in a loop to run as many times as the records in the recordset. If you want more help in doing this tell me and I will ellaborate.

nicsin

 

[chendes]

nicsin

That is a fantastic idea. The reason why I am doing it like this is the trans table is actually a query from a table which is ODBC. I need to import these tables and make them work soley on Access tables as I am demonstrating how a program works and have no access to the network to use the tables. If I import these tables instead of linking them they run horribly slowly. But if I create a query using ODBC tables and then save them as a table the performance is 100 times faster. I know it is a long winded way but guess will have to sharpen up my DB skills 100 fold.

Thanks a span for that
Cheers
chendes

 

[nicsin]

Glad to help

good luck,
nicsin