help with comparing table adding and copying record (1 Viewer)

sspreyer

Registered User.
Local time
Today, 08:34
Joined
Nov 18, 2013
Messages
251
all,

it really long shot can't seem find any code spinets to get me started or if its even possible.

Right..... i have 3 tables
Main table "Main table holds the current data "
History table "history table holds old data from main table "
excel Import table "temp table that holds data import from excel"



already worked the code out to import the excel data to a table ("excelimport")

need to compare the maintbl and excelimport table if field (key1) matches then old records get added to history table and existing records in maintbl get updated from the data from excelimport and new records get added.

sorry not very good at explaining so i have made a spread sheet showing the before tables and after table once the excel import is done also attached test db. aswell

any help much appreciated





thanks in advance

Shane

View attachment testdb.xlsx

View attachment TestDB.accdb
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 15:34
Joined
Feb 19, 2013
Messages
16,553
if field (key1) matches then old records get added to history table
you need an append query - something like

Code:
INSERT INTO tblHistory
SELECT tblMain.*
FROM tblMain INNER JOIN tblExcelImport ON tblMain.ID= tblExcelImport.ID

existing records in maintbl get updated from the data from excelimport
you need an update query
Code:
UPDATE tblMain (fld1,Fld2....)
SELECT tblExcelImport.fld1,tblExcelImport.fld2,.....
FROM tblMain INNER JOIN tblExcelImport ON tblMain.ID= tblExcelImport.ID

and new records get added
you need another append query

Code:
INSERT INTO tblMain
SELECT tblExcelImport.*
FROM  tblMain RIGHT JOIN tblExcelImport ON tblMain.ID= tblExcelImport.ID
WHERE tblMain.ID IS NULL
 

sspreyer

Registered User.
Local time
Today, 08:34
Joined
Nov 18, 2013
Messages
251
you need an append query - something like

Code:
INSERT INTO tblHistory
SELECT tblMain.*
FROM tblMain INNER JOIN tblExcelImport ON tblMain.ID= tblExcelImport.ID

here i put this think thats right lol
Code:
INSERT INTO tblHistory
SELECT tblMain.*
FROM tblMain INNER JOIN tblExcelImport ON tblMain.key1 = tblExcelImport.key1;




you need an update query

Getting syntax error do i need to change fld1 fld2 to field names?

Code:
UPDATE tblMain (fld1,Fld2....)
SELECT tblExcelImport.fld1,tblExcelImport.fld2,.....
FROM tblMain INNER JOIN tblExcelImport ON tblMain.ID= tblExcelImport.ID





you need another append query

Code:
INSERT INTO tblMain
SELECT tblExcelImport.*
FROM  tblMain RIGHT JOIN tblExcelImport ON tblMain.ID= tblExcelImport.ID
WHERE tblMain.ID IS NULL

i used this think its right data looks good lol

Code:
INSERT INTO tblMain
[COLOR="Red"]SELECT tblExcelImport.*
FROM tblMain RIGHT JOIN tblExcelImport ON tblMain.key1= tblExcelImport.key1
WHERE tblMain.key1 IS NULL;
[/COLOR]

oh thanks for help much appreciated CJ
only thing is aswell when i add the data to history table i have a date field called date1 anyway i can put todays date in that field when adding it to the history table

cheers


shane
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:34
Joined
Feb 19, 2013
Messages
16,553
Getting syntax error do i need to change fld1 fld2 to field names?
I can read most things - but not minds! - code supplied is example only, change field and table names to suit, remove the ….

have a date field called date1 anyway i can put todays date in that field
yes

assuming your date field is the last field then something like

INSERT INTO tblHistory
SELECT tblMain.*, Date()
…….
 

sspreyer

Registered User.
Local time
Today, 08:34
Joined
Nov 18, 2013
Messages
251
hi really sorry but still getting a syntax error defo doing something wrong lol

[CODE]UPDATE tblMain (Room,Owner_name,Building)
SELECT tblExcelImport.room, tblExcelImport.Owner_name,tblExcelImport.building,
FROM tblMain INNER JOIN tblExcelImport ON tblMain.key1= tblExcelImport.key1[/CODE]

the field names match

thanks again

shane
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:34
Joined
May 7, 2009
Messages
19,169
UPDATE tblMain
Set tblMain.Room=tblExcelImport.room,
tblMain.Owner_name=tblExcelImport.Owner_name,
tblMain.Building=tblExcelImport.building
FROM
tblMain INNER JOIN tblExcelImport
ON tblMain.key1= tblExcelImport.key1
 

sspreyer

Registered User.
Local time
Today, 08:34
Joined
Nov 18, 2013
Messages
251
UPDATE tblMain
Set tblMain.Room=tblExcelImport.room,
tblMain.Owner_name=tblExcelImport.Owner_name,
tblMain.Building=tblExcelImport.building
FROM tblMain INNER JOIN tblExcelImport
ON tblMain.key1= tblExcelImport.key1

HI arnelgp ,
this now say's im missing operator ??

thanks

shane
 

sspreyer

Registered User.
Local time
Today, 08:34
Joined
Nov 18, 2013
Messages
251
missing operator on this line

FROM tblMain INNER JOIN tblExcelImport ON tblMain.key1=tblExcelImport.key1
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:34
Joined
May 7, 2009
Messages
19,169
UPDATE tblMain INNER JOIN tblExcelImport
ON tblMain.key1= tblExcelImport.key1
Set tblMain.Room=tblExcelImport.room,
tblMain.Owner_name=tblExcelImport.Owner_name,
tblMain.Building=tblExcelImport.building
 

sspreyer

Registered User.
Local time
Today, 08:34
Joined
Nov 18, 2013
Messages
251
UPDATE tblMain INNER JOIN tblExcelImport
ON tblMain.key1= tblExcelImport.key1
Set tblMain.Room=tblExcelImport.room,
tblMain.Owner_name=tblExcelImport.Owner_name,
tblMain.Building=tblExcelImport.building

now no syntax errors found owner_name was Owners_name on one of the tables

but now its not updating it runs but not updating any off the fields

also notice duplicate when run the query in datasheet mode
thanks

shane
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:34
Joined
May 7, 2009
Messages
19,169
While on design view of query, click on View on ribbon. Are there any recirds showing. If none then there us no match. If there is then chk if they have same data.
 

sspreyer

Registered User.
Local time
Today, 08:34
Joined
Nov 18, 2013
Messages
251
While on design view of query, click on View on ribbon. Are there any recirds showing. If none then there us no match. If there is then chk if they have same data.

i dont know what i done clear the data compact repaired added fresh data to the test db and now works fine thanks arnelgp :D
 

Users who are viewing this thread

Top Bottom