Updating table from data in two other tables via a query

[Steve Housechild]

I have a table of 'Customers', a table of 'Newsletters' and a table
for storing which customers have recieved which letters 'CustLett'.

By using a query, I have pulled a list of customers who qualify for
newsletter No.1. The are displayed using a subform for viewing
purposes.

What I want to do now (in one swift action) is to add the customer
account no's to the table 'CustLett' along with the ID of Newsletter
No.1. I think I can do this one at a time, but with 10,000 customers
on the database, this would take a long time.

Please let me know if you have any ideas.

Thanks

Steve

[John Winterbottom]

"Steve Housechild" <s-**********@supanet.com> wrote in message
news:6b*************************@posting.google.co m...

I have a table of 'Customers', a table of 'Newsletters' and a table
for storing which customers have recieved which letters 'CustLett'.

By using a query, I have pulled a list of customers who qualify for
newsletter No.1. The are displayed using a subform for viewing
purposes.

What I want to do now (in one swift action) is to add the customer
account no's to the table 'CustLett' along with the ID of Newsletter
No.1. I think I can do this one at a time, but with 10,000 customers
on the database, this would take a long time.

insert into CustLett (customerID, newsletterID)
select c.customerID, n.newsletterID
from customers as c, newsletters as n
where not exists
(
select * from CustLett c2
where c2.customerID = c.customerID
and c2.newsletterID = n.newsletterID
)
if this is not what you want please post your table structures with some
sample data and the ooutput you need

[Wayne Morgan]

If you have the Customer ID and the Newsletter ID in the query you have
already made, you could use that query as the basis for an Append query to
add those records to the CustLett table.

Example:
Insert Into CustLett (CustomerID, NewsletterID)
Select qryYourQuery.CustomerID, qryYourQuery.NewsletterID
From qryYourQuery;

--
Wayne Morgan
MS Access MVP
"Steve Housechild" <s-**********@supanet.com> wrote in message
news:6b*************************@posting.google.co m...

I have a table of 'Customers', a table of 'Newsletters' and a table
for storing which customers have recieved which letters 'CustLett'.

By using a query, I have pulled a list of customers who qualify for
newsletter No.1. The are displayed using a subform for viewing
purposes.

What I want to do now (in one swift action) is to add the customer
account no's to the table 'CustLett' along with the ID of Newsletter
No.1. I think I can do this one at a time, but with 10,000 customers
on the database, this would take a long time.

Please let me know if you have any ideas.

Thanks

Steve

[Siobhan Perricone]

On 26 May 2004 05:16:44 -0700, s-**********@supanet.com (Steve
Housechild) wrote:

I have a table of 'Customers', a table of 'Newsletters' and a table
for storing which customers have recieved which letters 'CustLett'.

By using a query, I have pulled a list of customers who qualify for
newsletter No.1. The are displayed using a subform for viewing
purposes.

What I want to do now (in one swift action) is to add the customer
account no's to the table 'CustLett' along with the ID of Newsletter
No.1. I think I can do this one at a time, but with 10,000 customers
on the database, this would take a long time.

Please let me know if you have any ideas.

Use an append query.

Open up the query you used to figure out which customers qualify for
the newsletter in question. SAVE AS a different file name so it
doesn't munge your select query. Then change it into an append query
by going up to QUERY then selecting APPEND. When you make it an
append query it will ask you to choose the table you want to append
to, choose your CustLett table. Then in the query fields create a
field for the newsletter like this:

Field: [NewsletterFieldName]: "No.1"
(or whatever it is you populate the newsletter field with)
Table: (should be blank)
Append To: (choose the newsletter field)

You can check to make sure it's choosing the right records before you
run the query, and then you just run the query and it should append
the records.

--
Siobhan Perricone
Systems Developer
Vermont Agency of Natural Resources
(my comments are my own, not my employer's)