Append Query run amok - Duane

G

Guest

I thought I had this issue cracked from a previous post - but it's run amok
on me.

I have a Contact form (derived from a Contact Table) and within that I have
a Notes subform (derived from a Notes table)

There are occassions when I want to add a Note Record to specific Contact
Records that says something like "Fax sent to client on 28/2/2006 requestion
donation)

I created a select query (as per Duane's advice below) and then an append
query to add a new notes record - so far so good. It added a new notes
record to 100 selected records. I then ran the append query again to see
what would happen and it added the new notes TWICE to each record (ie -
you're about to append 200 records), the third time it added it FOUR times
and so on.

How do I get it to only add the new notes ONCE to each contact

Here's the SQL

INSERT INTO Notes ( ID, [Date], UserInitials, Comments )
SELECT Contacts.ID, Date() AS [Date], "SK" AS UserInitials, "What about
this" AS Comments
FROM Contacts INNER JOIN Notes ON Contacts.ID = Notes.ID
WHERE (((Contacts.[Contact Type])="Area Coordinator"));







You can create a query based on your Contacts table. Add the ParentID field
to the grid. Create more columns in the grid like
Comments: "These are your comments to append"
DateField: Date()
--others--
Set a criteria on this query so that it only returns the ParentIDs that you
want appended. Change the query to an append query and choose to append to
your Notes table. Select the appropriate append to fields from Notes so that
the values get pushed into the proper fields.

Run the append query.

If you have a problem, you MUST come back with the SQL view of your append
query.
 
D

Duane Hookom

Only run the query once. Why would you want to run it four times? An append
query is an action query that modifies your records.
 
G

Guest

Hi Duane - because 'this week' I sent out a fax to these people. 'Next week'
I followed it up with an email so want to append the notes records with this
new message - so theoretically it only has 3 notes - the original note, my
append note of faxes, my append note of email.

What would happen though [if my trial is refelective of that] is I will have
my origional note, my append note of faxes and two append notes of email.

Duane Hookom said:
Only run the query once. Why would you want to run it four times? An append
query is an action query that modifies your records.

--
Duane Hookom
MS Access MVP
--

Compelling Consulting said:
I thought I had this issue cracked from a previous post - but it's run amok
on me.

I have a Contact form (derived from a Contact Table) and within that I
have
a Notes subform (derived from a Notes table)

There are occassions when I want to add a Note Record to specific Contact
Records that says something like "Fax sent to client on 28/2/2006
requestion
donation)

I created a select query (as per Duane's advice below) and then an append
query to add a new notes record - so far so good. It added a new notes
record to 100 selected records. I then ran the append query again to see
what would happen and it added the new notes TWICE to each record (ie -
you're about to append 200 records), the third time it added it FOUR times
and so on.

How do I get it to only add the new notes ONCE to each contact

Here's the SQL

INSERT INTO Notes ( ID, [Date], UserInitials, Comments )
SELECT Contacts.ID, Date() AS [Date], "SK" AS UserInitials, "What about
this" AS Comments
FROM Contacts INNER JOIN Notes ON Contacts.ID = Notes.ID
WHERE (((Contacts.[Contact Type])="Area Coordinator"));







You can create a query based on your Contacts table. Add the ParentID
field
to the grid. Create more columns in the grid like
Comments: "These are your comments to append"
DateField: Date()
--others--
Set a criteria on this query so that it only returns the ParentIDs that
you
want appended. Change the query to an append query and choose to append to
your Notes table. Select the appropriate append to fields from Notes so
that
the values get pushed into the proper fields.

Run the append query.

If you have a problem, you MUST come back with the SQL view of your append
query.
 
D

Duane Hookom

I see what you did wrong. You included the Notes table in your "FROM"
clause. There would be no reason to have any table other than Contacts in
the FROM clause:

INSERT INTO Notes ( ID, [Date], UserInitials, Comments )
SELECT Contacts.ID, Date() AS [Date], "SK" AS UserInitials, "What about
this" AS Comments
FROM Contacts
WHERE (((Contacts.[Contact Type])="Area Coordinator"));

--
Duane Hookom
MS Access MVP
--

Compelling Consulting said:
Hi Duane - because 'this week' I sent out a fax to these people. 'Next
week'
I followed it up with an email so want to append the notes records with
this
new message - so theoretically it only has 3 notes - the original note, my
append note of faxes, my append note of email.

What would happen though [if my trial is refelective of that] is I will
have
my origional note, my append note of faxes and two append notes of email.

Duane Hookom said:
Only run the query once. Why would you want to run it four times? An
append
query is an action query that modifies your records.

--
Duane Hookom
MS Access MVP
--

"Compelling Consulting" <[email protected]>
wrote in message
I thought I had this issue cracked from a previous post - but it's run
amok
on me.

I have a Contact form (derived from a Contact Table) and within that I
have
a Notes subform (derived from a Notes table)

There are occassions when I want to add a Note Record to specific
Contact
Records that says something like "Fax sent to client on 28/2/2006
requestion
donation)

I created a select query (as per Duane's advice below) and then an
append
query to add a new notes record - so far so good. It added a new notes
record to 100 selected records. I then ran the append query again to
see
what would happen and it added the new notes TWICE to each record (ie -
you're about to append 200 records), the third time it added it FOUR
times
and so on.

How do I get it to only add the new notes ONCE to each contact

Here's the SQL

INSERT INTO Notes ( ID, [Date], UserInitials, Comments )
SELECT Contacts.ID, Date() AS [Date], "SK" AS UserInitials, "What about
this" AS Comments
FROM Contacts INNER JOIN Notes ON Contacts.ID = Notes.ID
WHERE (((Contacts.[Contact Type])="Area Coordinator"));







You can create a query based on your Contacts table. Add the ParentID
field
to the grid. Create more columns in the grid like
Comments: "These are your comments to append"
DateField: Date()
--others--
Set a criteria on this query so that it only returns the ParentIDs that
you
want appended. Change the query to an append query and choose to append
to
your Notes table. Select the appropriate append to fields from Notes so
that
the values get pushed into the proper fields.

Run the append query.

If you have a problem, you MUST come back with the SQL view of your
append
query.
 
G

Guest

Brilliant - thanks Duane ...............................


Duane Hookom said:
I see what you did wrong. You included the Notes table in your "FROM"
clause. There would be no reason to have any table other than Contacts in
the FROM clause:

INSERT INTO Notes ( ID, [Date], UserInitials, Comments )
SELECT Contacts.ID, Date() AS [Date], "SK" AS UserInitials, "What about
this" AS Comments
FROM Contacts
WHERE (((Contacts.[Contact Type])="Area Coordinator"));

--
Duane Hookom
MS Access MVP
--

Compelling Consulting said:
Hi Duane - because 'this week' I sent out a fax to these people. 'Next
week'
I followed it up with an email so want to append the notes records with
this
new message - so theoretically it only has 3 notes - the original note, my
append note of faxes, my append note of email.

What would happen though [if my trial is refelective of that] is I will
have
my origional note, my append note of faxes and two append notes of email.

Duane Hookom said:
Only run the query once. Why would you want to run it four times? An
append
query is an action query that modifies your records.

--
Duane Hookom
MS Access MVP
--

"Compelling Consulting" <[email protected]>
wrote in message
I thought I had this issue cracked from a previous post - but it's run
amok
on me.

I have a Contact form (derived from a Contact Table) and within that I
have
a Notes subform (derived from a Notes table)

There are occassions when I want to add a Note Record to specific
Contact
Records that says something like "Fax sent to client on 28/2/2006
requestion
donation)

I created a select query (as per Duane's advice below) and then an
append
query to add a new notes record - so far so good. It added a new notes
record to 100 selected records. I then ran the append query again to
see
what would happen and it added the new notes TWICE to each record (ie -
you're about to append 200 records), the third time it added it FOUR
times
and so on.

How do I get it to only add the new notes ONCE to each contact

Here's the SQL

INSERT INTO Notes ( ID, [Date], UserInitials, Comments )
SELECT Contacts.ID, Date() AS [Date], "SK" AS UserInitials, "What about
this" AS Comments
FROM Contacts INNER JOIN Notes ON Contacts.ID = Notes.ID
WHERE (((Contacts.[Contact Type])="Area Coordinator"));







You can create a query based on your Contacts table. Add the ParentID
field
to the grid. Create more columns in the grid like
Comments: "These are your comments to append"
DateField: Date()
--others--
Set a criteria on this query so that it only returns the ParentIDs that
you
want appended. Change the query to an append query and choose to append
to
your Notes table. Select the appropriate append to fields from Notes so
that
the values get pushed into the proper fields.

Run the append query.

If you have a problem, you MUST come back with the SQL view of your
append
query.
 

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

Append Query 1
append query 2
append Query duplicating records 1
Append Query Question 2
Append Query 1
append query 1
append query 4
Append/Refresh Tables 1

Top