Whats wrong with this SQL query?

K

Kerman

This append query is supposed to insert a range of numbers from the
beginning to the end. Example: 12000 to 12024 would be inserted into my
table as: 12000, 12001, 12002, 12003 etc. What happens is after the last
number is inserted of 12024 I get, 100000 all the way up to over 1,000,000.
It should stop at 12024. Any ideas...Thanks...Randy

INSERT INTO CheckedOutCertsAllNumbers ( CertNo, Inspector, TypeOfCert,
DateCheckedOut, BeginingCertInitial )
SELECT [N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Expr1,
CheckedOutCertificates.Inspector, CheckedOutCertificates.TypeOfCert,
CheckedOutCertificates.DateCheckedOut,
CheckedOutCertificates.BeginningInitial
FROM Num, CheckedOutCertificates
WHERE
((([N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl])<=[Forms]![frmCh
eckedOutCertificates]![EndCertNolbl]) AND
((CheckedOutCertificates.Inspector)=[Forms]![frmCheckedOutCertificates]![cbo
Inspector]) AND
((CheckedOutCertificates.TypeOfCert)=[Forms]![frmCheckedOutCertificates]![cb
oTypeofCert]) AND
((CheckedOutCertificates.DateCheckedOut)=[Forms]![frmCheckedOutCertificates]
![DateCheckedOutlbl]) AND
((CheckedOutCertificates.BeginningInitial)=[Forms]![frmCheckedOutCertificate
s]![BeginningInitiallbl]))
ORDER BY [N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl];
 
J

John Vinson

This append query is supposed to insert a range of numbers from the
beginning to the end. Example: 12000 to 12024 would be inserted into my
table as: 12000, 12001, 12002, 12003 etc. What happens is after the last
number is inserted of 12024 I get, 100000 all the way up to over 1,000,000.
It should stop at 12024. Any ideas...Thanks...Randy
Click to expand...


My guess is that BeginCertNolbl is a Text field rather than a numeric
field. Try changing the first line of the Where clause from

((([N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl])<=
[Forms]![frmCheckedOutCertificates]![EndCertNolbl]) AND

to

(([N] + Val([Forms]![frmCheckedOutCertificates]![BeginCertNolbl])) <=
Val([Forms]![frmCheckedOutCertificates]![EndCertNolbl]))

The Val() function will explicitly do a numeric rather than a string
comparison.

John W. Vinson[MVP]
 
R

Randy

I tried your suggestion below, but the table is still populated with
hundreds of thousands of records. All the fields in the tables in question
are set to numbers and long integer. I cant figure this out..Thanks for
your help
John Vinson said:
This append query is supposed to insert a range of numbers from the
beginning to the end. Example: 12000 to 12024 would be inserted into my
table as: 12000, 12001, 12002, 12003 etc. What happens is after the
last
number is inserted of 12024 I get, 100000 all the way up to over
1,000,000.
It should stop at 12024. Any ideas...Thanks...Randy
Click to expand...


My guess is that BeginCertNolbl is a Text field rather than a numeric
field. Try changing the first line of the Where clause from

((([N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl])<=
[Forms]![frmCheckedOutCertificates]![EndCertNolbl]) AND

to

(([N] + Val([Forms]![frmCheckedOutCertificates]![BeginCertNolbl])) <=
Val([Forms]![frmCheckedOutCertificates]![EndCertNolbl]))

The Val() function will explicitly do a numeric rather than a string
comparison.

John W. Vinson[MVP]
Click to expand...
 
C

Chris2

Kerman said:
This append query is supposed to insert a range of numbers from the
beginning to the end. Example: 12000 to 12024 would be inserted into my
table as: 12000, 12001, 12002, 12003 etc. What happens is after the last
number is inserted of 12024 I get, 100000 all the way up to over 1,000,000.
It should stop at 12024. Any ideas...Thanks...Randy

INSERT INTO CheckedOutCertsAllNumbers ( CertNo, Inspector, TypeOfCert,
DateCheckedOut, BeginingCertInitial )
SELECT [N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Expr1,
CheckedOutCertificates.Inspector, CheckedOutCertificates.TypeOfCert,
CheckedOutCertificates.DateCheckedOut,
CheckedOutCertificates.BeginningInitial
FROM Num, CheckedOutCertificates
WHERE
((([N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl])<=[Forms]![frmCh
eckedOutCertificates]![EndCertNolbl]) AND
((CheckedOutCertificates.Inspector)=[Forms]![frmCheckedOutCertificates]![cbo
Inspector]) AND
((CheckedOutCertificates.TypeOfCert)=[Forms]![frmCheckedOutCertificates]![cb
oTypeofCert]) AND
((CheckedOutCertificates.DateCheckedOut)=[Forms]![frmCheckedOutCertificates]
![DateCheckedOutlbl]) AND
((CheckedOutCertificates.BeginningInitial)=[Forms]![frmCheckedOutCertificate
s]![BeginningInitiallbl]))
ORDER BY [N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl];
Click to expand...

Here is the Query, cleaned up as best I can:

INSERT INTO CheckedOutCertsAllNumbers

(CertNo

,Inspector

,TypeOfCert

,DateCheckedOut

,BeginingCertInitial)

SELECT [N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Expr1

,CheckedOutCertificates.Inspector

,CheckedOutCertificates.TypeOfCert

,CheckedOutCertificates.DateCheckedOut

,CheckedOutCertificates.BeginningInitial

FROM Num

,CheckedOutCertificates

WHERE ((([N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl])

<= [Forms]![frmCheckedOutCertificates]![EndCertNolbl])

AND ((CheckedOutCertificates.Inspector) =
[Forms]![frmCheckedOutCertificates]![cboInspector])

AND
((CheckedOutCertificates.TypeOfCert)=[Forms]![frmCheckedOutCertificates]![cb
oTypeofCert])

AND
((CheckedOutCertificates.DateCheckedOut)=[Forms]![frmCheckedOutCertificates]
![DateCheckedOutlbl])

AND
((CheckedOutCertificates.BeginningInitial)=[Forms]![frmCheckedOutCertificate
s]![BeginningInitiallbl]))

ORDER BY [N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl];



My apologies, the lines still wrap.





The only thing that leaps out immediately is that you aren't using an ANSI
SQL-92 JOIN format, but are rather using the ANSI SQL-89 style JOIN. I
highly recommend the ANSI SQL-92 JOIN format.



Now, appreciate, please, when I say the following, that I don't know what
the Primary Keys of the tables are (it wasn't mentioned).



I don't see any line on the WHERE clause indicating that any part of the
Num table is being "JOINed" to the CheckedOutCertificates table (a part of
the ANSI SQL-89 JOIN format).



It appears as if Num and CheckedOutCertificates are being JOINed in a
manner that will cause one row to come into existence for every row in
CheckedOutCertificates where the BeginCertNolbl is less than or equal to
EndCertNolbl, multiplied by every Row in Num. Or, essentially, a partial
cartesian product. Or, at least, it seems it *might* be that way.



It would certainly explain why it's INSERTing too many rows.





Sincerely,



Chris O.
 
R

Randy

Thanks for the help Chris, it works now! Thanks...
Chris2 said:
Kerman said:
This append query is supposed to insert a range of numbers from the
beginning to the end. Example: 12000 to 12024 would be inserted into my
table as: 12000, 12001, 12002, 12003 etc. What happens is after the last
number is inserted of 12024 I get, 100000 all the way up to over 1,000,000.
It should stop at 12024. Any ideas...Thanks...Randy

INSERT INTO CheckedOutCertsAllNumbers ( CertNo, Inspector, TypeOfCert,
DateCheckedOut, BeginingCertInitial )
SELECT [N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Expr1,
CheckedOutCertificates.Inspector, CheckedOutCertificates.TypeOfCert,
CheckedOutCertificates.DateCheckedOut,
CheckedOutCertificates.BeginningInitial
FROM Num, CheckedOutCertificates
WHERE
((([N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl])<=[Forms]![frmCh
eckedOutCertificates]![EndCertNolbl]) AND
((CheckedOutCertificates.Inspector)=[Forms]![frmCheckedOutCertificates]![cbo
Inspector]) AND
((CheckedOutCertificates.TypeOfCert)=[Forms]![frmCheckedOutCertificates]![cb
oTypeofCert]) AND
((CheckedOutCertificates.DateCheckedOut)=[Forms]![frmCheckedOutCertificates]
![DateCheckedOutlbl]) AND
((CheckedOutCertificates.BeginningInitial)=[Forms]![frmCheckedOutCertificate
s]![BeginningInitiallbl]))
ORDER BY [N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl];
Click to expand...

Here is the Query, cleaned up as best I can:

INSERT INTO CheckedOutCertsAllNumbers

(CertNo

,Inspector

,TypeOfCert

,DateCheckedOut

,BeginingCertInitial)

SELECT [N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Expr1

,CheckedOutCertificates.Inspector

,CheckedOutCertificates.TypeOfCert

,CheckedOutCertificates.DateCheckedOut

,CheckedOutCertificates.BeginningInitial

FROM Num

,CheckedOutCertificates

WHERE ((([N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl])

<= [Forms]![frmCheckedOutCertificates]![EndCertNolbl])

AND ((CheckedOutCertificates.Inspector) =
[Forms]![frmCheckedOutCertificates]![cboInspector])

AND
((CheckedOutCertificates.TypeOfCert)=[Forms]![frmCheckedOutCertificates]![cb
oTypeofCert])

AND
((CheckedOutCertificates.DateCheckedOut)=[Forms]![frmCheckedOutCertificates]
![DateCheckedOutlbl])

AND
((CheckedOutCertificates.BeginningInitial)=[Forms]![frmCheckedOutCertificate
s]![BeginningInitiallbl]))

ORDER BY [N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl];



My apologies, the lines still wrap.





The only thing that leaps out immediately is that you aren't using an
ANSI
SQL-92 JOIN format, but are rather using the ANSI SQL-89 style JOIN. I
highly recommend the ANSI SQL-92 JOIN format.



Now, appreciate, please, when I say the following, that I don't know what
the Primary Keys of the tables are (it wasn't mentioned).



I don't see any line on the WHERE clause indicating that any part of the
Num table is being "JOINed" to the CheckedOutCertificates table (a part of
the ANSI SQL-89 JOIN format).



It appears as if Num and CheckedOutCertificates are being JOINed in a
manner that will cause one row to come into existence for every row in
CheckedOutCertificates where the BeginCertNolbl is less than or equal to
EndCertNolbl, multiplied by every Row in Num. Or, essentially, a partial
cartesian product. Or, at least, it seems it *might* be that way.



It would certainly explain why it's INSERTing too many rows.





Sincerely,



Chris O.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Append query appends twice. Why? 7
Query appends duplicate records, 2 times, 3 times, 4 times etc 11
Iif statement in SQL append query? 1
SQL Query missing first number? 2
Help with code "Type Mismatch" 6
Need help with form coding (Date Problems) 1
Help with code 3

Top