how do I avoid results multiplication in append queries?

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

Guest

when I make an append query, the results are shown multiplicated, how can I
avoid this?
 
What do you mean by "multiplicated"? Values are doubled, tripled, or
something? Or do you mean duplicated such as the same record more than once
in a table?

Also show us the SQL. Open the query in design view. Next go to View, SQL
View and copy and past it here.
 
when I make an append query, the results are shown multiplicated, how can I
avoid this?
Click to expand...

If you are talking about duplicate rows being added then you first
need to add a unique constraint to ensure this doesn't happen again
then amend your INSERT statement to avoid the constraint biting e.g.
something like this:

INSERT INTO Employees (employee_number)
SELECT L1.employee_number
FROM LoadTable AS L1
WHERE NOT EXISTS
(
SELECT *
FROM Employees AS E1
WHERE E1.employee_number = L1.employee_number
);

Jamie.

--
 
If you are talking about duplicate rows being added then you first
need to add a unique constraint to ensure this doesn't happen again
then amend your INSERT statement to avoid the constraint biting
Click to expand...

Another thought: the Access/Jet engine has a property for 'Global
Partial Bulk Ops'. Using the OLE DB provider you can set the property
at the connection level:

connection.Properties("Jet OLEDB:Global Partial Bulk Ops").Value = 1

will cause allow partial completion of bulk operations i.e. rows those
that fail the constraint will do so silently while allowing the others
to succeed.

Dunno if this property is exposed in the Access user interface or via
DAO.

Jamie.

--
 
Back
Top