Select query returns non-existing data
Hello everybody,
I'm trying to match entries in a table up against each other 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 first 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.
[1180 byte] By [
epimp] at [2007-12-23]
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
Cannot see a problem in the logic!
Have you actually spelt the field name "Quantitity" the same in both tables, or is one Quantity?
ADG at 2007-8-30 >
Yes. I have this small example of what happens. However, it seems to happen randomly so if I ran the script again I would probably get another ID that was left unmatched.
Here it matches ID 30195 with ID 32116 properly with this SQL query:
SELECT * FROM Table WHERE Source <> 'BEC' and Quantity = 350000 and
Registrationnumber = '005700785' and Exdate=#18-03-2003#
(Note: I do dates the European way)
TableID QuantityRegistrationnumber CurrencyID ExDate PayDate YieldType Matched Source
30195 350000 005700785 EUR 18-03-2003 28-03-2003 UDB No BEC
31790 350000 005700785 EUR 18-03-2003 08-03-2005 RUS No BEC
32115 005700785 NLG 15-03-2004 No DAB
32116 350000 005700785 NLG 18-03-2003 Yes DAB
But here it makes the faulty notion that the Quantitiy of the row with
TableID 30319 is 7454 (like at TableID 31762) thus resulting in
this query:
SELECT * FROM Table WHERE Source <> 'BEC' and Quantity = 7454 and
Registrationnumber = '005501202' and Exdate=#16-04-2003#
TableID Quantity Registrationnumber CurrencyID ExDate PayDate YieldType Matched Source
30319 6581 005501202 EUR 16-04-2003 01-05-2003 UDB No BEC
32056 6581 005501202 FRF 16-04-2003 No DAB
31762 7454 005501202 EUR 16-04-2003 22-12-2004 RUS No BEC
epimp wrote: