vba code - add new and duplicate records

[ashley1]

Hiya
Need sum quick help
i have 2 tables tableA and tableB
by queries i used to add any new records from tableA to tableB and allowed duplicates, but it doesnt give me accurate results. (id number is generated automatically but the id number changes to every record) This basically doesnt add the duplictes properly.

so im thinking of doing it in vba
im a quick learner is there any1 who can guide me or start me off please with a little coding

i have a student database and need sumthing aound the lines:-

if student doesnt exist then add from table1 to table2
if student exists but course is differnet then add from table1 to table2

thanks

 

[ColinEssex]

Why store duplicate data in table B?

Col

 

[ashley1]

its duplicate in the sense that a student can apply to more then one course
tableA gets imported weekly by an excel sheet so the data changes, thats why we have tableB as a main table

 

[DALIEN51]

This Is How!

Create the following function:

DIM MYDB AS DATABASE
DIM SOURCE AS RECORDSET
DIM TARGET AS RECORDSET

SET MYDB = CURRENTDB()
SET SOURCE = MYDB.OPENRECORDSET("{name of table A}")
SET TARGET = MYDB.OPENRECORDSET("{name of table B}")

SOURCE.MOVEFIRST
DO UNTIL SOURCE.EOF

TARGET.MOVEFIRST
DO UNTIL TARGET.EOF

IF SOURCE![STUDENT NAME] = TARGET![STUDENT NAME] AND SOURCE![COURSE NAME] = TARGET![COURSE NAME] THEN
GOTO ONWARD
END IF

TARGET.MOVENEXT
LOOP

TARGET.ADDNEW
TARGET![STUDENT NAME] = SOURCE![STUDENT NAME]
TARGET![COURSE NAME] = SOURCE![COURSE NAME]
TARGET.UPDATE

ONWARD:

SOURCE.MOVENEXT
LOOP

 

[ColinEssex]

You should have a table for Student details,
StudentID (AutoNumber)
Forename
Surname
address1
Address2
Postcode
DOB,
etc.

Then have another table for courses students are on

CourseID
StudentID
etc

One student can have many courses.

Col

 

[ashley1]

thanks will try the coding
much appreciated dalien

col i cant split my tables up due to the way our database runs, we have to have all the information in one table

thanks for ur help,