Append query duplicating records

[accessonly11]

dear members,

i am using this append query

in start it was working good, but after few days, now it is behaving like this and adding about 32k records in one click. what is going wrong with it.

 

[ebs17]

i am using this append query

Your many pictures do not show a complete query. Switch to the SQL view of the query and copy the statement and show it here as code.

 

[accessonly11]

here it is

PARAMETERS [Enter SaleID] Long, [Enter ProductID] Long, [Enter Qty] Long;
INSERT INTO tblSaleData ( SaleID, ProductID, Qty )
SELECT tblSale.SaleID, [Enter ProductID] AS ProductID, [Enter Qty] AS Qty
FROM tblSale, tblPriceAndPack
WHERE (((tblSale.SaleID)=[Enter SaleID]) AND (([Enter ProductID])=[Enter ProductID]) AND (([Enter Qty])=[Enter Qty]));

 

[ebs17]

I've formatted the statement for readability and also removed superfluous parentheses. This gives us a better basis for discussion.

PARAMETERS
   [Enter SaleID] Long,
   [Enter ProductID] Long,
   [Enter Qty] Long
;
INSERT INTO
   tblSaleData(
      SaleID,
      ProductID,
      Qty
   )
SELECT
   tblSale.SaleID,
   [Enter ProductID] AS ProductID,
   [Enter Qty] AS Qty
FROM
   tblSale
   , tblPriceAndPack
WHERE
   tblSale.SaleID = [Enter SaleID]
      AND
   [Enter ProductID] = [Enter ProductID]
      AND
   [Enter Qty] = [Enter Qty]

1) , tblPriceAndPack
The second table is not used, but its presence ensures a Cartesian product and thus a multiplication of the records.

2) AND [Enter ProductID] = [Enter ProductID] AND [Enter Qty] = [Enter Qty]
That is simply superfluous.

3) Duplicates can also arise if such data records already exist in tblSaleData. Here's how to avoid it:
a) A composite unique index over this field combination
b) An inconsistency check in the query (records from A that are not in B)
Generalized:

INSERT INTO B (Key1, Key2 , Key3)
SELECT DISTINCT A.Key1, A.Key2 , A.Key3
FROM A LEFT JOIN B ON A.Key1 = B.Key1 AND A.Key2 = B.Key2 AND A.Key3 = B.Key3
WHERE B.Key1 IS NULL

Distinct is in case there are duplicates within the query because, for example, the ID is not unique.

I myself would use both points combined.

 

[arnelgp]

you can always create a Form (data entry), so you don't have to [Enter ...] everytime.

 

[plog]

Why do you even have a SELECT query? Why do you even have an APPEND query?

You are inserting 3 values into a table and each of those values you are having the user enter. Why not just take those 3 values and directly insert them into the destination table? Why must they first pass thru your SELECT query? Very odd.

 

[ebs17]

Of course, an input form is also a solution. Doing things differently is often a solution.
With a continuous fire from a loop, however, the use of a form with necessary user actions would be quite cumbersome.
The use of a form can also result in duplicates.

In any case, you should master an append query with the desired requirements.

 

[Pat Hartman]

Once you fix the query to remove the extraneous table, you may still have a duplication problem since ProductID is not the PK so it may not be unique.

In any event, you need to add a unique index to the table you are appending to on SaleID and ProductID. To do this, you need to open the indexes dialog since you cannot create a multi-column index directly in the table view.

Go to the first empty line in the indexes dialog. Give the index a unique name. Pick the first of the two fields and check the unique box. Then go down one line. Leave the index name blank. This tells access that the second field belongs to the above index. Then pick the second field.

 

[accessonly11]

thanks to all for help,
i just removed the unnecessary table of tblprice&pack, now its working ok,
now i will apply unique index by using indexes dialog in related table design view.

you can always create a Form (data entry), so you don't have to [Enter ...] everytime.

do i remove [Enter....] from parameters?

in future i will do that, when i select any product then get sale price from related table using dlookup (is there any alternative best option of dlookup in split database).

then i do calculations in forms unbound text boxes like txtSale * txtQty and append these results in qrySaleOrderDetails with productid, qty, sale price, and total price data.

i dont know is this is best approach or not

 

[ebs17]

a Form (data entry)

... means a bound form with bound controls. Otherwise you would have to transfer the contents into the table again using a query or recordset.

 

[Pat Hartman]

Prompting in a query is a poor choice for two reasons:
1. You can't validate the data entered
2. In certain situations, you will be prompted more than once for the same value.

As @arnelgp suggested, add an unbound field to a form. That allows you to validate the data entered and ensure it is present before opening the form/report bound to the query. It will also prevent you from being prompted more than once.

 

[ebs17]

@accessonly11 but showed a formulated parameter query, only the names of the parameters make you think.

A parameter query in good use will have the parameters passed before execution. That's what the Parameter interface is actually for. In a well-formulated query, parameters only need to be passed once.
If the query needs to fetch parameters, that's bad style.

 

[Pat Hartman]

An example of when a query prompts more than once is for a report. If the RecordSource of the report is a query that prompts for a value, the prompt happens when you open the report to preview it. If you decide to print the report, the query prompts a second time for the value. That is just ONE of the situations.

 

[ebs17]

query that prompts for a value

Like I said, bad style.
Parameters should be passed, not fetched (requested) by the query. You can borrow something from object-oriented programming: The query object is assigned values via its defined interfaces.