473,221 Members | 1,778 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,221 software developers and data experts.

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
3 1481
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Chris Jackson | last post by:
I'm a novice Access user and am not sure how to solve the following problem. Any help with the following would be greatly appreciated! I have two tables with identical structures, the first holds...
2
by: Ray Holtz | last post by:
I have a form that shows a single record based on a query criteria. When I click a button it is set to use an append query to copy that record to a separate table, then deletes the record from the...
34
by: Jeff | last post by:
For years I have been using VBA extensively for updating data to tables after processing. By this I mean if I had to do some intensive processing that resulted in data in temp tables, I would have...
3
by: Grant | last post by:
I need some sample code showing how to manipulate data in my access database using C#. This is what Im trying to do: Dropdownlist with datagrid both bound to datasource. When the drop down list...
4
by: Geoff | last post by:
Hi I'm hoping somebody can help me with the following problem that has occurred to me. Suppose I have two tables in an SQL Server database. Let's call these tables A and B. Assume that A has...
1
by: beil.jp | last post by:
Hi All - I'm new to Google Groups and just a novice user of Access, so please forgive me if my question is elementary: I am having a problem using the "update query" for my first time. In my...
5
by: JimmyKoolPantz | last post by:
Situation: I am writing a program that opens up a data file (file ext .dbf), imports the information into a dataset. The program also, searches through the dataset for key words ("company...
33
by: bill | last post by:
In an application I am writing the user can define a series of steps to be followed. I save them in a sql database using the field "order" (a smallint) as the primary key. (there are in the range...
3
by: HSXWillH | last post by:
I've looked through the site and not found what I'm looking for here. I am not code-versed or anything like that so my skills are rudimentary at best. I'm using Access 03 on a Windows Vista...
7
by: sparks | last post by:
I am working on a database that has a lot of calculated values on the forms. These were never put into the tables. But were tied to unbound fields on the forms. Now 8000 records later they want...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.