467,911 Members | 1,380 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,911 developers. It's quick & easy.

Updating table from data in two other tables via a query

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
Nov 13 '05 #1
  • viewed: 1299
Share:
3 Replies
"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
Nov 13 '05 #2
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

Nov 13 '05 #3
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)
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Chris Jackson | last post: by
2 posts views Thread by Ray Holtz | last post: by
3 posts views Thread by Grant | last post: by
4 posts views Thread by Geoff | last post: by
5 posts views Thread by JimmyKoolPantz | last post: by
33 posts views Thread by bill | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.