Append Query Creating Duplicate Entries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone,

I'm running an append query to add a new entry for each computer in our
office. The fields in each entry are the computer's AssetTag, the updates
UpdateID, and yes/no Completed field. This table is hidden (so I don't have
to worry about it) and it keeps track of which updates are complete on each
computer. When I run my append query, instead of adding a new entry for each
AssetTag in the Computers table it adds SEVERAL duplicate entries - one for
each UpdateID and AssetTag that's already in the Updates table. Basically,
what it is trying to do is double the size of my Updates table, but by adding
15 of the same entry per computer.

In the Design View, I have the AssetTag field's Table set to Computer, so I
don't understand why it seems to be going through the Updates table.


Here's some background on my database:
I have a database with a table named "Computers" with AssetTag (ID number),
Update (Whether or not it is in the update cycle), and several other fields.
I also have a table named "Update Values" which is where I manually input the
UpdateID and Description of updates that are to be completed on the
computers. And the last relevant table I have is named "Updates" and it has
fields for AssetTag, UpdateID, and a Yes/No field for specifying whether or
not the udpate has been completed.


Here's my SQL code (I don't know SQL that well... only learned a little
through the wxMusik player's programmable playlists):

INSERT INTO Updates ( UpdateID, AssetTag )
SELECT [enter value] AS Expr1, Computers.AssetTag
FROM Computers INNER JOIN ([Update values] INNER JOIN Updates ON [Update
values].UpdateID = Updates.UpdateID) ON Computers.AssetTag = Updates.AssetTag
WHERE (((Computers.Update)=Yes))
ORDER BY Computers.AssetTag;


Thanks for reading and for any help you can give!
-Aaron Cooper
 
An Append Query ADDS new records to a table (barring violation of primary
key/unique index)
An Update Query UPDATE existing records already in the table.

e.g.
Update Tablename SET (f1, f2, ... , fn) values (v1, v2, ... vn)

See the help file for more specific examples

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting
 
I realize that. I have it set to append, not update. That' why it doesn't
make sense that it is adding so many entries.

-Aaron

[MVP] S.Clark said:
An Append Query ADDS new records to a table (barring violation of primary
key/unique index)
An Update Query UPDATE existing records already in the table.

e.g.
Update Tablename SET (f1, f2, ... , fn) values (v1, v2, ... vn)

See the help file for more specific examples

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

Aaron Cooper said:
Hi everyone,

I'm running an append query to add a new entry for each computer in our
office. The fields in each entry are the computer's AssetTag, the updates
UpdateID, and yes/no Completed field. This table is hidden (so I don't
have
to worry about it) and it keeps track of which updates are complete on
each
computer. When I run my append query, instead of adding a new entry for
each
AssetTag in the Computers table it adds SEVERAL duplicate entries - one
for
each UpdateID and AssetTag that's already in the Updates table.
Basically,
what it is trying to do is double the size of my Updates table, but by
adding
15 of the same entry per computer.

In the Design View, I have the AssetTag field's Table set to Computer, so
I
don't understand why it seems to be going through the Updates table.


Here's some background on my database:
I have a database with a table named "Computers" with AssetTag (ID
number),
Update (Whether or not it is in the update cycle), and several other
fields.
I also have a table named "Update Values" which is where I manually input
the
UpdateID and Description of updates that are to be completed on the
computers. And the last relevant table I have is named "Updates" and it
has
fields for AssetTag, UpdateID, and a Yes/No field for specifying whether
or
not the udpate has been completed.


Here's my SQL code (I don't know SQL that well... only learned a little
through the wxMusik player's programmable playlists):

INSERT INTO Updates ( UpdateID, AssetTag )
SELECT [enter value] AS Expr1, Computers.AssetTag
FROM Computers INNER JOIN ([Update values] INNER JOIN Updates ON [Update
values].UpdateID = Updates.UpdateID) ON Computers.AssetTag =
Updates.AssetTag
WHERE (((Computers.Update)=Yes))
ORDER BY Computers.AssetTag;


Thanks for reading and for any help you can give!
-Aaron Cooper
Click to expand...
Click to expand...
 
Append = Add, Append <> Update

I do not think that you should have it set to Append, because that will ADD
new entries, which is the undesired behavior. I do not believe that you
want to ADD new entries. I think that you want to UPDATE, thus it should be
an UPDATE query.

Aaron Cooper said:
I realize that. I have it set to append, not update. That' why it doesn't
make sense that it is adding so many entries.

-Aaron

[MVP] S.Clark said:
An Append Query ADDS new records to a table (barring violation of primary
key/unique index)
An Update Query UPDATE existing records already in the table.

e.g.
Update Tablename SET (f1, f2, ... , fn) values (v1, v2, ... vn)

See the help file for more specific examples

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

Aaron Cooper said:
Hi everyone,

I'm running an append query to add a new entry for each computer in our
office. The fields in each entry are the computer's AssetTag, the
updates
UpdateID, and yes/no Completed field. This table is hidden (so I don't
have
to worry about it) and it keeps track of which updates are complete on
each
computer. When I run my append query, instead of adding a new entry
for
each
AssetTag in the Computers table it adds SEVERAL duplicate entries - one
for
each UpdateID and AssetTag that's already in the Updates table.
Basically,
what it is trying to do is double the size of my Updates table, but by
adding
15 of the same entry per computer.

In the Design View, I have the AssetTag field's Table set to Computer,
so
I
don't understand why it seems to be going through the Updates table.


Here's some background on my database:
I have a database with a table named "Computers" with AssetTag (ID
number),
Update (Whether or not it is in the update cycle), and several other
fields.
I also have a table named "Update Values" which is where I manually
input
the
UpdateID and Description of updates that are to be completed on the
computers. And the last relevant table I have is named "Updates" and
it
has
fields for AssetTag, UpdateID, and a Yes/No field for specifying
whether
or
not the udpate has been completed.


Here's my SQL code (I don't know SQL that well... only learned a little
through the wxMusik player's programmable playlists):

INSERT INTO Updates ( UpdateID, AssetTag )
SELECT [enter value] AS Expr1, Computers.AssetTag
FROM Computers INNER JOIN ([Update values] INNER JOIN Updates ON
[Update
values].UpdateID = Updates.UpdateID) ON Computers.AssetTag =
Updates.AssetTag
WHERE (((Computers.Update)=Yes))
ORDER BY Computers.AssetTag;


Thanks for reading and for any help you can give!
-Aaron Cooper
Click to expand...
Click to expand...
Click to expand...
 
Back
Top