Select query returns invalid data (non-existent!)

[epimp702]

Hello everybody,

I'm trying to match entries in a table up against eachother based on a
number of criteria (Quantity, Date). Three types of entries exist:
Those from source 1, 2, and 3. I'm trying to match data from source 1
with data from source 2 and 3. Some is identical, some is not.

In a VBA Script I frist select every entry from source 1 (indicated by
the field "Datasource").

I then run a While loop. For each entry I run a second select query on
the fields I want to match (Quantity, Date). It works OK, ie. as
expected, in some cases. In other cases, the quantity is completely off
- it's as if Access just invents some random quantity instead of using
the one from select query one (the one with source 1 data).

What on Earth is wrong?

Thank you for your assistance.

 

[Jeff L]

It would be easier to assist you if you could post the queries you are
trying to run.

 

[epimp702]

Here is query no. 1:
MyDB.OpenRecordset("SELECT * FROM Table WHERE Source =
'BEC' and Level=2")

And query no. 2 run for each entry returned from query no. 1:
"SELECT * FROM Table WHERE Source <> 'BEC' and
Quantitity = " & MyRec.Fields("Quantitity") & " and Registrationnumber
= '" & MyRec.Fields("Registrationnumber") & "' and Exdate=#" &
MyRec.Fields("ExDate") & "#"


Here is the entire code:
Dim MyDB As DAO.Database, MyRec As DAO.Recordset, MySubRec As
DAO.Recordset
Set MyDB = CurrentDb
Set MyRec = MyDB.OpenRecordset("SELECT * FROM Table WHERE Source =
'BEC' and Level=2")

While Not MyRec.EOF

sqlselectquery = "SELECT * FROM Table WHERE Source <> 'BEC' and
Quantitity = " & MyRec.Fields("Quantitity") & " and Registrationnumber
= '" & MyRec.Fields("Registrationnumber") & "' and Exdate=#" &
MyRec.Fields("ExDate") & "#"

Set MySubRec = MyDB.OpenRecordset(sqlselectquery)
While Not MySubRec.EOF
sqlupdatequery = "UPDATE Table SET MatchFound = True WHERE
TableID = " & MySubRec.Fields("TableID")
DoCmd.SetWarnings False
DoCmd.RunSQL sqlupdatequery
DoCmd.SetWarnings True
MySubRec.MoveNext
Wend
MyRec.MoveNext
Wend

 

[epimp702]

It seems that running the mentioned code several times (5-8) matches
every entry in the table. Have I hit some kind of limit? Or is it
something about memory allocation?

 

[John Vinson]

Here is query no. 1:
MyDB.OpenRecordset("SELECT * FROM Table WHERE Source =
'BEC' and Level=2")

And query no. 2 run for each entry returned from query no. 1:
"SELECT * FROM Table WHERE Source <> 'BEC' and
Quantitity = " & MyRec.Fields("Quantitity") & " and Registrationnumber
= '" & MyRec.Fields("Registrationnumber") & "' and Exdate=#" &
MyRec.Fields("ExDate") & "#"

Why are you doing this iteratively, instead of as a single query? You
should be able to do the same thing with a Self Join query:

SELECT * FROM Table AS A
INNER JOIN Table AS B
ON A.[RegistrationNumber] = B.[RegistrationNumber]
WHERE A.Source = 'BEC' AND A.Level = 2
AND B.Source <> 'BEC' AND B.Exdate = A.Exdate;


John W. Vinson[MVP]

 

[epimp702]

SELECT * FROM Table AS A
INNER JOIN Table AS B
ON A.[RegistrationNumber] = B.[RegistrationNumber]
WHERE A.Source = 'BEC' AND A.Level = 2
AND B.Source <> 'BEC' AND B.Exdate = A.Exdate;

Thanks a lot! I need to learn to use those inner joins more often.
That's a lot simpler and faster than running a loop in a loop.

Some errors still happen in the matching process that I just don't
comprehend.

From my logfile of matches:

Table A=Table B
Quantity: 1091=1091
ID: 43046=45526
Registrationnumber: 005501341=005501341
Exdate: 07-05-2003=07-05-2003

But - the table is like this:

ID;Quantity;Registrationnumber;Exdate;Source
43046;16876;005501341;07-05-2003;BEC
45526;1091;005501341;07-05-2003;DAB
45504;16876;005501341;07-05-2003;DAB

It should have matched 43046 with 45504. I'm completely lost. If I run
the script again, I'll get another mismatched pair while 43046 will be
matched correctly with 45504. What am I doing wrong?


The script is as follows:
Dim MyDB As DAO.Database, MyRec As DAO.Recordset
Set MyDB = CurrentDb
Set MyRec = MyDB.OpenRecordset("SELECT * FROM Table AS A INNER JOIN
Table AS B ON A.[Registrationnumber] = B.[Registrationnumber] WHERE
A.Source = 'BEC' AND A.Level = 2 AND B.Source<> 'BEC' AND B.Exdate =
A.Exdate AND B.Quantity= A.Quantity")

logfil = "Inner Join run at " & Now()

While Not MyRec.EOF
sqlupdatequery = "UPDATE Table SET Matched = True WHERE TableID
= " & MyRec.Fields("B.TableID")
logfil = logfil & Chr(13) & Chr(10) & sqlupdatequery & Chr(13)
& Chr(10) _
& MyRec.Fields("A.Quantity") & "=" & MyRec.Fields("B.Quantity")
& Chr(13) & Chr(10) _
& MyRec.Fields("A.TableID") & "=" & MyRec.Fields("B.TableID") &
Chr(13) & Chr(10) _
& MyRec.Fields("A.Registrationnumber") & "=" &
MyRec.Fields("B.Registrationnumber") & Chr(13) & Chr(10) _
& MyRec.Fields("A.ExDate") & "=" & MyRec.Fields("B.ExDate") &
Chr(13) & Chr(10) _
& Chr(13) & Chr(10)
DoCmd.SetWarnings False
DoCmd.RunSQL sqlupdatequery
DoCmd.SetWarnings True
MyRec.MoveNext
Wend

Set fs = CreateObject("Scripting.FileSystemObject")
Set A = fs.CreateTextFile("c:\logfile.txt", True)
A.Write logfil
A.Close

MyRec.Close
MyDB.Close