Append query appends twice. Why?

K

Kerman

When I run this query, I get the data appended twice. If I enter the number
100 in the [BeginCertNolbl] field, I get two records appended to my "CertNo"
field in my "CheckedOutCertsAllNumbers" table. Two records identical of 100
etc. Any Ideas?...Thanks..

INSERT INTO CheckedOutCertsAllNumbers ( CertNo, Inspector, TypeOfCert,
DateCheckedOut, BeginingCertInitial )
SELECT [Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Expr1,
CheckedOutCertificates.Inspector, CheckedOutCertificates.TypeOfCert,
CheckedOutCertificates.DateCheckedOut,
CheckedOutCertificates.BeginningInitial
FROM CheckedOutCertificates
WHERE
(((CheckedOutCertificates.Inspector)=[Forms]![frmCheckedOutCertificates]![cb
oInspector]) AND
((CheckedOutCertificates.TypeOfCert)=[Forms]![frmCheckedOutCertificates]![cb
oTypeofCert]) AND
((CheckedOutCertificates.DateCheckedOut)=[Forms]![frmCheckedOutCertificates]
![DateCheckedOutlbl]) AND
((CheckedOutCertificates.BeginningInitial)=[Forms]![frmCheckedOutCertificate
s]![BeginningInitiallbl]));
 
S

Sylvain Lafontaine

Check the SELECT query and make sure that it is returning only one record
from CheckedOutCertificates.

S. L.
 
K

Kerman

I have found that my append query based on my forms testbox's will append
more and more times each time it is used. Example, If I enter 100 in
[BeginCertNolbl] I will get one record in my table of 100. The next time if
I enter 101, I will get two records of 101, the next entry of 102 I get
three records of 102 and so on...What gives...Thanks

Sylvain Lafontaine said:
Check the SELECT query and make sure that it is returning only one record
from CheckedOutCertificates.

S. L.

Kerman said:
When I run this query, I get the data appended twice. If I enter the
number
100 in the [BeginCertNolbl] field, I get two records appended to my
"CertNo"
field in my "CheckedOutCertsAllNumbers" table. Two records identical of
100
etc. Any Ideas?...Thanks..

INSERT INTO CheckedOutCertsAllNumbers ( CertNo, Inspector, TypeOfCert,
DateCheckedOut, BeginingCertInitial )
SELECT [Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Expr1,
CheckedOutCertificates.Inspector, CheckedOutCertificates.TypeOfCert,
CheckedOutCertificates.DateCheckedOut,
CheckedOutCertificates.BeginningInitial
FROM CheckedOutCertificates
WHERE
(((CheckedOutCertificates.Inspector)=[Forms]![frmCheckedOutCertificates]![cb
oInspector]) AND
((CheckedOutCertificates.TypeOfCert)=[Forms]![frmCheckedOutCertificates]![cb
oTypeofCert]) AND
((CheckedOutCertificates.DateCheckedOut)=[Forms]![frmCheckedOutCertificates]
![DateCheckedOutlbl]) AND
((CheckedOutCertificates.BeginningInitial)=[Forms]![frmCheckedOutCertificate
s]![BeginningInitiallbl]));
Click to expand...
Click to expand...
 
J

JohnFol

As Sylvain says, check the Select part using 100 in the BeginCertNolbl
field. This will tell you if it's the criteria returning more than 1 row or
not. .. . .

ie
SELECT [Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Expr1,
CheckedOutCertificates.Inspector, CheckedOutCertificates.TypeOfCert,
CheckedOutCertificates.DateCheckedOut,
CheckedOutCertificates.BeginningInitial
FROM CheckedOutCertificates
WHERE
(((CheckedOutCertificates.Inspector)=[Forms]![frmCheckedOutCertificates]![cb oInspector]) AND ((CheckedOutCertificates.TypeOfCert)=[Forms]![frmCheckedOutCertificates]![cb oTypeofCert]) AND((CheckedOutCertificates.DateCheckedOut)=[Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl]) AND((CheckedOutCertificates.BeginningInitial)=[Forms]![frmCheckedOutCertificates]![BeginningInitiallbl]));"Kerman" <[email protected]> wrote in messagenews:[email protected]...>I have found that my append query based on my forms testbox's will append> more and more times each time it is used. Example, If I enter 100 in> [BeginCertNolbl] I will get one record in my table of 100. The next timeif> I enter 101, I will get two records of 101, the next entry of 102 I get> three records of 102 and so on...What gives...Thanks>> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>> wrote in message news:eUyvPE%[email protected]...>> Check the SELECT query and make sure that it is returning only one record>> from CheckedOutCertificates.>>>> S. L.>>>> "Kerman" <[email protected]> wrote in message>> news:[email protected]...>> > When I run this query, I get the data appended twice. If I enter the>> > number>> > 100 in the [BeginCertNolbl] field, I get two records appended to my>> > "CertNo">> > field in my "CheckedOutCertsAllNumbers" table. Two records identicalof>> > 100>> > etc. Any Ideas?...Thanks..>> >>> > INSERT INTO CheckedOutCertsAllNumbers ( CertNo, Inspector, TypeOfCert,>> > DateCheckedOut, BeginingCertInitial )>> > SELECT [Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Expr1,>> > CheckedOutCertificates.Inspector, CheckedOutCertificates.TypeOfCert,>> > CheckedOutCertificates.DateCheckedOut,>> > CheckedOutCertificates.BeginningInitial>> > FROM CheckedOutCertificates>> > WHERE>> >>(((CheckedOutCertificates.Inspector)=[Forms]![frmCheckedOutCertificates]![cb>> > oInspector]) AND>> >>((CheckedOutCertificates.TypeOfCert)=[Forms]![frmCheckedOutCertificates]![cb>> > oTypeofCert]) AND>> >>((CheckedOutCertificates.DateCheckedOut)=[Forms]![frmCheckedOutCertificates]>> > ![DateCheckedOutlbl]) AND>> >>((CheckedOutCertificates.BeginningInitial)=[Forms]![frmCheckedOutCertificate>> > s]![BeginningInitiallbl]));>> >>> >>>>>>>
 
R

Randy

I'm not sure what you are asking. If you are saying to comvert to a select
query, I tried that , it is returning 3 records of 100...What do I need to
do?...Thanks..
JohnFol said:
As Sylvain says, check the Select part using 100 in the BeginCertNolbl
field. This will tell you if it's the criteria returning more than 1 row
or not. .. . .

ie
SELECT [Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Expr1,
CheckedOutCertificates.Inspector, CheckedOutCertificates.TypeOfCert,
CheckedOutCertificates.DateCheckedOut,
CheckedOutCertificates.BeginningInitial
FROM CheckedOutCertificates
WHERE
(((CheckedOutCertificates.Inspector)=[Forms]![frmCheckedOutCertificates]![cb
oInspector]) AND
((CheckedOutCertificates.TypeOfCert)=[Forms]![frmCheckedOutCertificates]![cb
oTypeofCert])
AND((CheckedOutCertificates.DateCheckedOut)=[Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl])
AND((CheckedOutCertificates.BeginningInitial)=[Forms]![frmCheckedOutCertificates]![BeginningInitiallbl]));"Kerman"
messagehave found that my
append query based on my forms testbox's will append> more and more times
each time it is used. Example, If I enter 100 in> [BeginCertNolbl] I will
get one record in my table of 100. The next timeif> I enter 101, I will
get two records of 101, the next entry of 102 I get> three records of 102
and so on...What gives...Thanks>> "Sylvain Lafontaine" <sylvain aei ca
(fill the blanks, no spam please)>> wrote in message
Check the SELECT query
and make sure that it is returning only one record>> from
in message>> > When I run
this query, I get the data appended twice. If I enter the>> > number>> >
100 in the [BeginCertNolbl] field, I get two records appended to my>> >
"CertNo">> > field in my "CheckedOutCertsAllNumbers" table. Two records
identicalof>> > 100>> > etc. Any Ideas?...Thanks..>> >>> > INSERT INTO
CheckedOutCertsAllNumbers ( CertNo, Inspector, TypeOfCert,>> >
DateCheckedOut, BeginingCertInitial )>> > SELECT
[Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Expr1,>> >
CheckedOutCertificates.Inspector, CheckedOutCertificates.TypeOfCert,>> >
CheckedOutCertificates.DateCheckedOut,>> >
CheckedOutCertificates.BeginningInitial>> > FROM CheckedOutCertificates>>
WHERE>>
(((CheckedOutCertificates.Inspector)=[Forms]![frmCheckedOutCertificates]![cb>> oInspector]) AND>>
((CheckedOutCertificates.TypeOfCert)=[Forms]![frmCheckedOutCertificates]![cb>> oTypeofCert]) AND>>
((CheckedOutCertificates.DateCheckedOut)=[Forms]![frmCheckedOutCertificates]>> ![DateCheckedOutlbl]) AND>>
((CheckedOutCertificates.BeginningInitial)=[Forms]![frmCheckedOutCertificate>>
Click to expand...
s]![BeginningInitiallbl]));>> >>> >>>>>>>
Click to expand...
Click to expand...
 
J

JohnFol

Your original point was :

"When I run this query, I get the data appended twice. If I enter the
number 100 in the [BeginCertNolbl] field, I get two records appended to my
"CertNo" field in my "CheckedOutCertsAllNumbers" table"

This is because your query returns multiple records as you have just
confirmed.

So what is the problem? What are you trying to achieve that isn't as
expected?


Randy said:
I'm not sure what you are asking. If you are saying to comvert to a
select query, I tried that , it is returning 3 records of 100...What do I
need to do?...Thanks..
JohnFol said:
As Sylvain says, check the Select part using 100 in the BeginCertNolbl
field. This will tell you if it's the criteria returning more than 1 row
or not. .. . .

ie
SELECT [Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Expr1,
CheckedOutCertificates.Inspector, CheckedOutCertificates.TypeOfCert,
CheckedOutCertificates.DateCheckedOut,
CheckedOutCertificates.BeginningInitial
FROM CheckedOutCertificates
WHERE
(((CheckedOutCertificates.Inspector)=[Forms]![frmCheckedOutCertificates]![cb
oInspector]) AND
((CheckedOutCertificates.TypeOfCert)=[Forms]![frmCheckedOutCertificates]![cb
oTypeofCert])
AND((CheckedOutCertificates.DateCheckedOut)=[Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl])
AND((CheckedOutCertificates.BeginningInitial)=[Forms]![frmCheckedOutCertificates]![BeginningInitiallbl]));"Kerman"
messagehave found that my
append query based on my forms testbox's will append> more and more times
each time it is used. Example, If I enter 100 in> [BeginCertNolbl] I
will get one record in my table of 100. The next timeif> I enter 101, I
will get two records of 101, the next entry of 102 I get> three records
of 102 and so on...What gives...Thanks>> "Sylvain Lafontaine" <sylvain
aei ca (fill the blanks, no spam please)>> wrote in message
Check the SELECT query
and make sure that it is returning only one record>> from
in message>> > When I run
this query, I get the data appended twice. If I enter the>> > number>> >
100 in the [BeginCertNolbl] field, I get two records appended to my>> >
"CertNo">> > field in my "CheckedOutCertsAllNumbers" table. Two records
identicalof>> > 100>> > etc. Any Ideas?...Thanks..>> >>> > INSERT INTO
CheckedOutCertsAllNumbers ( CertNo, Inspector, TypeOfCert,>> >
DateCheckedOut, BeginingCertInitial )>> > SELECT
[Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Expr1,>> >
CheckedOutCertificates.Inspector, CheckedOutCertificates.TypeOfCert,>> >
CheckedOutCertificates.DateCheckedOut,>> >
CheckedOutCertificates.BeginningInitial>> > FROM CheckedOutCertificates>>
WHERE>>
(((CheckedOutCertificates.Inspector)=[Forms]![frmCheckedOutCertificates]![cb>>
oInspector]) AND>>
((CheckedOutCertificates.TypeOfCert)=[Forms]![frmCheckedOutCertificates]![cb>>
oTypeofCert]) AND>>
((CheckedOutCertificates.DateCheckedOut)=[Forms]![frmCheckedOutCertificates]>>
![DateCheckedOutlbl]) AND>>
((CheckedOutCertificates.BeginningInitial)=[Forms]![frmCheckedOutCertificate>>
s]![BeginningInitiallbl]));>> >>> >>>>>>>
Click to expand...
Click to expand...
Click to expand...
 
R

Randy

I need the query to append only one record as entered in [BeginCertNolbl].
I get duplicates as it is now...Thanks
JohnFol said:
Your original point was :

"When I run this query, I get the data appended twice. If I enter the
number 100 in the [BeginCertNolbl] field, I get two records appended to my
"CertNo" field in my "CheckedOutCertsAllNumbers" table"

This is because your query returns multiple records as you have just
confirmed.

So what is the problem? What are you trying to achieve that isn't as
expected?


Randy said:
I'm not sure what you are asking. If you are saying to comvert to a
select query, I tried that , it is returning 3 records of 100...What do I
need to do?...Thanks..
JohnFol said:
As Sylvain says, check the Select part using 100 in the BeginCertNolbl
field. This will tell you if it's the criteria returning more than 1 row
or not. .. . .

ie
SELECT [Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Expr1,
CheckedOutCertificates.Inspector, CheckedOutCertificates.TypeOfCert,
CheckedOutCertificates.DateCheckedOut,
CheckedOutCertificates.BeginningInitial
FROM CheckedOutCertificates
WHERE
(((CheckedOutCertificates.Inspector)=[Forms]![frmCheckedOutCertificates]![cb
oInspector]) AND
((CheckedOutCertificates.TypeOfCert)=[Forms]![frmCheckedOutCertificates]![cb
oTypeofCert])
AND((CheckedOutCertificates.DateCheckedOut)=[Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl])
AND((CheckedOutCertificates.BeginningInitial)=[Forms]![frmCheckedOutCertificates]![BeginningInitiallbl]));"Kerman"
messagehave found that my
append query based on my forms testbox's will append> more and more
times each time it is used. Example, If I enter 100 in>
[BeginCertNolbl] I will get one record in my table of 100. The next
timeif> I enter 101, I will get two records of 101, the next entry of
102 I get> three records of 102 and so on...What gives...Thanks>>
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>>
wrote in message Check
the SELECT query and make sure that it is returning only one record>>
from CheckedOutCertificates.>>>> S. L.>>>> "Kerman" <[email protected]>
wrote in message>> > When I
run this query, I get the data appended twice. If I enter the>> >
number>> > 100 in the [BeginCertNolbl] field, I get two records appended
to my>> > "CertNo">> > field in my "CheckedOutCertsAllNumbers" table.
Two records identicalof>> > 100>> > etc. Any Ideas?...Thanks..>> >>> >
INSERT INTO CheckedOutCertsAllNumbers ( CertNo, Inspector, TypeOfCert,>>
DateCheckedOut, BeginingCertInitial )>> > SELECT
[Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Expr1,>> >
CheckedOutCertificates.Inspector, CheckedOutCertificates.TypeOfCert,>> >
CheckedOutCertificates.DateCheckedOut,>> >
CheckedOutCertificates.BeginningInitial>> > FROM
CheckedOutCertificates>> > WHERE>>
(((CheckedOutCertificates.Inspector)=[Forms]![frmCheckedOutCertificates]![cb>>
oInspector]) AND>>
((CheckedOutCertificates.TypeOfCert)=[Forms]![frmCheckedOutCertificates]![cb>>
oTypeofCert]) AND>>
((CheckedOutCertificates.DateCheckedOut)=[Forms]![frmCheckedOutCertificates]>>
![DateCheckedOutlbl]) AND>>
((CheckedOutCertificates.BeginningInitial)=[Forms]![frmCheckedOutCertificate>>
s]![BeginningInitiallbl]));>> >>> >>>>>>>
Click to expand...
Click to expand...
Click to expand...
 
S

Sylvain Lafontaine

The BeginCertNolbl field is not part of the WHERE clause and, as such, as no
effect on the filtering or the number of records returned by the SELECT
query. All the records returned by the SELECT query will get inserted into
the CheckedOutCertsAllNumbers table and all these new records will have the
same value for CertNo, this value being the value of the BeginCertNolbl
field.

Maybe you have a misunderstanding about the effect of writing « SELECT
[Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Expr1 ... » ?

S. L.

Randy said:
I need the query to append only one record as entered in [BeginCertNolbl].
I get duplicates as it is now...Thanks
JohnFol said:
Your original point was :

"When I run this query, I get the data appended twice. If I enter the
number 100 in the [BeginCertNolbl] field, I get two records appended to
my "CertNo" field in my "CheckedOutCertsAllNumbers" table"

This is because your query returns multiple records as you have just
confirmed.

So what is the problem? What are you trying to achieve that isn't as
expected?


Randy said:
I'm not sure what you are asking. If you are saying to comvert to a
select query, I tried that , it is returning 3 records of 100...What do
I need to do?...Thanks..
As Sylvain says, check the Select part using 100 in the BeginCertNolbl
field. This will tell you if it's the criteria returning more than 1
row or not. .. . .

ie
SELECT [Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Expr1,
CheckedOutCertificates.Inspector, CheckedOutCertificates.TypeOfCert,
CheckedOutCertificates.DateCheckedOut,
CheckedOutCertificates.BeginningInitial
FROM CheckedOutCertificates
WHERE
(((CheckedOutCertificates.Inspector)=[Forms]![frmCheckedOutCertificates]![cb
oInspector]) AND
((CheckedOutCertificates.TypeOfCert)=[Forms]![frmCheckedOutCertificates]![cb
oTypeofCert])
AND((CheckedOutCertificates.DateCheckedOut)=[Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl])
AND((CheckedOutCertificates.BeginningInitial)=[Forms]![frmCheckedOutCertificates]![BeginningInitiallbl]));"Kerman"
messagehave found that my
append query based on my forms testbox's will append> more and more
times each time it is used. Example, If I enter 100 in>
[BeginCertNolbl] I will get one record in my table of 100. The next
timeif> I enter 101, I will get two records of 101, the next entry of
102 I get> three records of 102 and so on...What gives...Thanks>>
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)>> wrote in message
Check the SELECT
query and make sure that it is returning only one record>> from
CheckedOutCertificates.>>>> S. L.>>>> "Kerman" <[email protected]>
wrote in message>> > When
I run this query, I get the data appended twice. If I enter the>> >
number>> > 100 in the [BeginCertNolbl] field, I get two records
appended to my>> > "CertNo">> > field in my "CheckedOutCertsAllNumbers"
table. Two records identicalof>> > 100>> > etc. Any
Ideas?...Thanks..>> >>> > INSERT INTO CheckedOutCertsAllNumbers (
CertNo, Inspector, TypeOfCert,>> > DateCheckedOut,
BeginingCertInitial )>> > SELECT
[Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Expr1,>> >
CheckedOutCertificates.Inspector, CheckedOutCertificates.TypeOfCert,>>
CheckedOutCertificates.DateCheckedOut,>> >
CheckedOutCertificates.BeginningInitial>> > FROM
CheckedOutCertificates>> > WHERE>>
(((CheckedOutCertificates.Inspector)=[Forms]![frmCheckedOutCertificates]![cb>>
oInspector]) AND>>
((CheckedOutCertificates.TypeOfCert)=[Forms]![frmCheckedOutCertificates]![cb>>
oTypeofCert]) AND>>
((CheckedOutCertificates.DateCheckedOut)=[Forms]![frmCheckedOutCertificates]>>
![DateCheckedOutlbl]) AND>>
((CheckedOutCertificates.BeginningInitial)=[Forms]![frmCheckedOutCertificate>>
s]![BeginningInitiallbl]));>> >>> >>>>>>>
Click to expand...
Click to expand...
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

Whats wrong with this SQL query? 4
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