Comparing Values in 2 different tables

[cenderawasih]

I need to add certain data from Table A into table B if and only if the data in table A does not exist yet in table B.
The problem is, when i use the DO UNTIL Loop, The 1st data in table B that doesn't match the 1st data in table A is consider as UNMATCH data by the system although if that data already exist in table B but maybe in the next next row after the 1st row......

What i need now for my code to be able to compare for all data in table A with all the data in table B. Then when there is no match (meaning the data exist in table A but does not exist in table B), The copy codes from table A to table B for these UNMATCH data will be executed. This is my code :

Do Until rstFPT1JP00.EOF

If Not rstFPT1JP00.Fields("PTMCU&quot = rstTemp.Fields
("tempPTMCU&quot Then

rstTemp.AddNew
rstTemp.Fields("tempPTMCU&quot = rstFPT1JP00.Fields
("PTMCU&quot.Value
rstTemp.Update

End If

rstFPT1JP00.MoveNext
rstTemp.MoveNext

Loop

 

[BNPMike]

Access is a relational database which uses SQL. This provides set-at-a-time facilities and powerful joining capabilities. Try and use these instead of 3rd generation programming. You can achieve what you want with SQL without any need for programming in VBA. Join the two tables to identify the records that already exist in the new table and then use this set as an append query (inserts) to load your data.

 

[cenderawasih]

BNPMike,

How is it to do this in SQL.......
When u said join...do u mean joining both tables in a query or by using SQL?

 

[BNPMike]

You can do this with a simple query (ie an SQL statement or two). I'm not clear what you are doing though. Normally you might first detect whether the new (A) record is in the existing table (B). If it is you might either reject it or transfer some of its data values to the existing record. Now you would normally detect whether the record is already present by comparing keys ie you should have a primary key. If you have that sorted out, then your first option is simply to do an append query rfrom A into B. B will reject all the existing records automatically. You don't have to do anything.

If on the other hand you want to update existing records then you simply join A to B on the primary key and create an update query where you will set selected fields in B to be set equal to corresponding fields in A. Then you do a left join from A to B picking out the ones with B.keyfield is null, and use this as an append query. This adds the new records.

The complication here is you seem to be comparing sets of fields to determine which records are matching and which aren't. You can still do that if you want - just join on all the ones of interest - but make sure you're not doing this only because you haven't sorted out your primary keys properly. There is no good reason not to identify and declare the primary key.

 

[RoyVidar]

Using recordsets, the records are unordered, so you need to loop one of the recordsets (start to end) and use the appropriate search method according to the recordset type etc on the other recordset.

DAO - .findfirst/.seek
ADO - .find/.seek

For instance (DAO .findfirst)

[tt]rstTemp.FindFirst "tempPTMCU= " & rstFPT1JP00("PTMCU&quot
if rstTemp.NoMatch then
' do your adding
end if
rstFPT1JP00.MoveNext[/tt]

- here assuming PTMCU is numeric, if not, add the appropriate qulifiers (' for text, # for dates)
- but doing the whole operation in a query, as BNPMike suggests, might be better

Roy-Vidar