Well, it's beginning to make a bit more sense ... One question, however... What's the purpose of "rs.CursorLocation = 3"? I understand the need to position the cursor, but why 3?



JP

Okay, I've defined an autonum PK in both the "anything goes" table, and the "interim" table, and this field (RecID) is not addressed in the code at all. The code (as it stands at the moment) looks like this:

Dim rs1, rs2, rs3, cn
Dim sT1, sT2, sT3,sSQL1
Dim i
i = 0
set cn = CreateObject("ADODB.Connection")
Set rs1 = CreateObject("ADODB.Recordset")
Set rs2 = CreateObject("ADODB.Recordset")
Set rs3 = CreateObject("ADODB.Recordset")
cn.Open "DSN=EssexSmartSys;"
rs3.Open "Select * from ProductionData", cn,3 ,3
rs2.Open "Select * from PDDataClean", cn,3 ,3
rs2.MoveFirst
    Do Until rs2.EOF
    sT1 = rs2("KeyField")
MsgBox "Got this far!"
sSQL1 = "SELECT Count(*) As UnMatched FROM ProductionData WHERE [KeyField] = '" & sT1 & "'"
    rs1.Open sSQL1, cn, 3, 3
            If IsNull(rs1("UnMatched")) Or rs1("UnMatched") = 0 Then
                rs3.AddNew
                rs3("KeyField") = sT1
                rs3.Update
                i = i + 1
            End If
            rs1.Close
        rs2.MoveNext
    Loop
rs2.Close
rs3.Close

MsgBox i & " records added."

Now, as best I understand, the idea was to select the unique records from the "anything goes" table (ProductionData) that have no matches in the "interim" table (PDDataClean) - Yes? Or am I completely confused?

By throwing MsgBoxes around. I'm able to see a little of what's going on, so that helps a little, but I fear I'm not understanding your scheme properly...

     At least a lot of the "housekeeping" is working now!

 Thanks again,
                Joe

JP

"Now, as best I understand, the idea was to select the unique records from the "anything goes" table (ProductionData) that have no matches in the "interim" table (PDDataClean) - Yes? Or am I completely confused?"

The idea in the original code that I wrote, which IS for Access (you mentioned VBScript, so I thought you wanted that - use the original code for Access) is to check the 3 fields you indicated. Your last post only checks 1 field, and not the right field at that. How did the requirements change?

In my code I only add those three columns to Table1, but you would want to add all the columns from the intermediate table to Table1.





mmcdonal

Sorry about that - Table structure's changed... I'd gone ahead and stuck the three fields I was wanting to compare on together into one (KeyField)...

Thanks again, and I'll keep working at this...


  Joe

JP

Hi again!

    After taking time to actually understand what was really going on in the code you so generously posted (Thanks again!), and doing the little necessary bits to make it VBScript-able, I'm a LOT closer to the solution than I was! Thank you VERY much!! One other item I'm struggling with here.... you mentioned moving the other columns from the original table to the "clean" one... It's not too difficult, as there's only eight of them, but it'd look a lot cleaner/less awkward if I could do something like:

St1 = rs2("*") ... rs3("*") = St1...

instead of eight separate

Stn = rs2("Fldn") ... rs3("Fldn") = Stn

Is that sort of thing possible, or just not worth the effort?

Thanks again for all your assistance... (AND patience!)

Joe

JP

OOPS!
Forgot to include the code (as it stands today)

Dim rs1, rs2, rs3, cn, i
Dim sT1, sT2, sT3
Dim sSQL1, sSQL2, sSQL3

set cn = CreateObject("ADODB.Connection")
Set rs1 = CreateObject("ADODB.Recordset")
Set rs2 = CreateObject("ADODB.Recordset")
Set rs3 = CreateObject("ADODB.Recordset")
cn.Open "DSN=EssexSmartSys;"
i = 0
sSQL3 = "SELECT * FROM PDDataClean"
rs3.Open sSQL3, cn, 3, 3

sSQL2 = "SELECT * FROM ProductionData"
rs2.Open sSQL2, cn, 3, 3

rs2.MoveFirst
    Do Until rs2.EOF
    sT1 = rs2("KeyField")
        sSQL1 = "SELECT Count(*) As UnMatched FROM PDDataClean WHERE [KeyField] = '" & sT1 & "'"
        rs1.Open sSQL1, cn, 3, 3
            If IsNull(rs1("UnMatched")) Or rs1("UnMatched") = 0 Then

                rs3.AddNew
                rs3("KeyField") = sT1
                rs3.Update
                i = i + 1
            End If
        rs1.Close

    rs2.MoveNext
    Loop
rs2.Close
rs3.Close

MsgBox i & " records added."


JP

As I recall, sT1, sT2, sT3 where only to take variables to check to see if the record was a dupe or not. Once that is done, I would do this, assuming as you say there are 8 fields altogether, 1, 2, and 3 already being taken care of:

   rs3.AddNew
   rs3("Date") = sT1
   rs3("Time") = sT2
   rs3("MachineName") = sT3
   rs3("NextField") = rs2("NextField")
   rs3("Another") = rs2("Another")
   ...
   rs3.Update

Is that the sort of thing you are after?





mmcdonal

Yes, indeed! That'll do it nicely, if there's not a reasonable way to copy them all in one instruction...

       Thank you SO much!

Joe

JP

One more time....
         Going back through the code adding comments so I'll (hopefully) remember what this code does, I wondered if I could (for the "dirty" table) do a "SELECT DISTINCT" rather than a "SELECT *" on the OPEN, in order to have a smaller set of records to loop through... Reasonable?

                           Joe

JP

This issue there is that you want all the fields available for the transfer, and a Select Distinct will only pull records where EVERY field is different. Since you are only checking 3, you will probably eliminate many more records by including all fields. Try it in the query designer and see. Go to Properties > Unique Values > Yes.



mmcdonal