append Query duplicating records

[Alex]

I have an Append Query which runs from an On click event
attached to a command button in a form.

When the append query fires it adds records to a table
which is then queried and shows up in a subform.

Some of the Append Query fields are pulled from the main
form (based on table Tests) and are used to make new
records in one of the tables used in the subform (based on
tables Questions and Answers).

test_id:forms!frmtest!test_id APPENDED TO test_id
in "Answers" table.

question id (from Questions table) APPENDED TO question_id
in "Answers" table.

registration_id: forms!frmtest!registration_id APPENDED TO
registration_id in "Answers" table.

test_master_id (from Questions table) criteria set at "2".

The problem I am having is that once there are 2 or more
records in the main form, the Append Query will create
double/triple/quadruple the records in the subform. e.g.
first record in main form - append query creates the
required 8 records in the subform; second record in the
main form - append query creates the required 8 records in
the subform; third record in the main form - append query
creates 16 records in the subform (the required 8 plus a
duplication of those 8); fourth record in the main form -
append query creates 24 records in the subform (the
required 8 times 3 (so 3 duplications)).

Does anyone have any idea at all why it is doing this??

Please help,
Alex.

 

[Jeff Boyce]

Alex

It isn't clear, from your explanation, why you are duplicating data "pulled
from the main form ... and used to may new records...". This is generally
not a good idea in a relational database. Perhaps a description of your
data would lead to suggestions for normalization (which would let you use
more of Access' strengths).

If your append query returns all the rows from your main table, it will
produce (i.e., append) one for each row in your main table. Posting your
SQL statement would give readers something to work from.