Using MS Access for Bulk Mail

aguy

Registered User.
Local time
Today, 14:11
Joined
Dec 27, 2004
Messages
12
I have a database with names and addresses of persons on our mailing list. We mail out (snail mail, mind you) newsletters every month or so. Each state that we mail to is a different table...

Does anyone have any suggestions on how to have Access combine all of the tables (there are 24) into a query or something, and count the number of times each zip code appears?

Thanks.
 
Can't say that I like having a different table for each state's data. I'd just have a State field with all the data in one table. Having said that, you can join the tables together with a UNION query, and a simple totals query based on that can get you a count by zip code.
 
Why eactly are there 24 seperate tables? One for each of the states that you deal with? Are each of the tables identical in structure and contain similiar data? If you could give an idea of what type of data is in each of the tables, I would get a better idea of what you would need to do to get it together, whether it be a union query or a series of append queries. Normalization would probably help your database. I would have one table with the States listed, then use relationships to establish a link to thier related records, keeping them all in one table.
 
It was an exisiting database -- I am only trying to make my life easier :eek: . The structure of each table is identical -- ID, name ,address, city, state, zip, email, and category. When I print the addresses on the newsletters I need them separated by state and sorted by zip.
 
Unfortunatly, I'm not real sure how I should go about fixing the database. Should I just create a new table, paste the records into it from each table, then create another table (listing each state and their abbreviation) and link the state fields?
 
If the tables are indeed identical, a simple UNION query will join them:

SELECT * FROM Table1
UNION
SELECT * FROM Table2
UNION
SELECT * FROM Table3
...

Personally I'd use that query as the base for an append query to put them all into one table, and just use that from here on out. It would seem to be a lot simpler to work with 1 table than 24.
 
I have the union query... If I append do I get a new table or a new query? Also, how do I go about doing that?

pbaldy said:
If the tables are indeed identical, a simple UNION query will join them:

SELECT * FROM Table1
UNION
SELECT * FROM Table2
UNION
SELECT * FROM Table3
...

Personally I'd use that query as the base for an append query to put them all into one table, and just use that from here on out. It would seem to be a lot simpler to work with 1 table than 24.
 
Last edited:
Press the New button from the query tab of the database window. Close the choose files dialog and switch to SQL view. A Union query cannot be built by the QBE builder, you need to write the SQL yourself.

Merging the tables isn't quite as simple as others have said since you need to do something about the primary key. I am assuming that there are no existing relations to the 24 tables so there will be no problem if you assign a new primary key to each record. If that is true, you can create the union query as posted except that you need to select individual columns and omit the original ID column.

If you have to keep the original ID values, that is more of a problem and you won't be able to use an autonumber as the pk. You will need to use a two field pk. One that indicates the original state table and the second will be the original ID value. You'll need to assign your own ID's by using the DMax() method in the future. Search here for help if you need it.
 
Okay, I've got the query set up -- perfect. Thanks a lot for that.

Now, I need to count the zip codes. What I need Access to do is count the number of each zip code and give me that sum... To do this, do I have to enter EACH zip code, like COUNT (#####), or can I have it do all of that for me and just display a list of the code and the number of times it appears?
 
Make a find duplicates query by clicking the NEW button within the query area. Creat one that looks at zip and it should return each unique zip with a count of its reoccurance.
 
The counting worked great. Thanks a lot.

Two more questions, and I think I'll leave you guys alone ;) . I've created a find duplicates query on it, and it's finding duplicate email addresses (say the same person is listed in Alabama and Florida). However, I can't delete the duplicates as I would be able to if I had done a duplicates on a single table. Is there a way to be able to update the tables without have to print out the duplicates list and then going and finding them individually?

Also, is there a way to make the Union Query editable so that I can edit the records and have it update the tables -- or is there an easier way to do that?
 
Last edited:
Union queries do not produce updatable recordsets. Append the date to the common table and then you can delete the duplicates.

To count zipcodes, you would use a "totals" query not a "find duplicates" query.

Select Zip, Count(*) as CountZip From UnionQuery;
 
What do you mean by append the date to the common tables?
 
Here in lies more questions, like which is the correct record? How do you make the determination as to which one is the current address, why is it in multiple tables, and would you want to keep the records that appear in each table? Wouldnt you want to delete one entire set of records so that your not running to many outdate records?
E-mail addresses are a great way to watch for redundant/old data since some people hold onto them alot longer than thier physical addresses. No two people should have the same e-mail address, so there will only be one joeblow@aol.com.
 
jeremie_ingram said:
Here in lies more questions, like which is the correct record? How do you make the determination as to which one is the current address, why is it in multiple tables, and would you want to keep the records that appear in each table? Wouldnt you want to delete one entire set of records so that your not running to many outdate records?
E-mail addresses are a great way to watch for redundant/old data since some people hold onto them alot longer than thier physical addresses. No two people should have the same e-mail address, so there will only be one joeblow@aol.com.

Right, and by the nature of our business some people in the database have offices in different states. For us, we only need to keep one of any number of duplicates. It doens't matter which one, just as long as there is one... I do search for duplicates by their email address, though to search for duplicates in different states I seem to have to go through the union query I created, but is not editable...
 
Last edited:
The problem goes back to the structure of the database itself. As Pat stated, "Union queries do not produce updatable recordsets". What I would do at this point (just to get the printing done) - use the union query to create a tbl containing all of the data. Use queries to eliminate all unwanted records, then use the tbl for the print job.
IMHO - I would sit down and reconsider the basic table structure of the database itself. Analyz it and determine if it is properly normalized. Once you get the basic structure in the proper shape, then your data is alot easier to manipulate and properly use.
 
I would be more than happy to "normalize" the table... I know ya'll are getting tired of me, but please explain this?

Thanks
 
You have way too many tables.
You said you had 24 tables containing similiar fields. These should all be in one table, and add a field for the state to identify which state they are in OR create a seperate table with ALL states listed and you can use a lookup field to refrence that table. Then, when you query your data, you only query 1 single table, and can make adjusments acordingly. You could also use (for example) the client name field and the client e-mail address field as a concantenanted key, so that you could ensure that there were no duplicate entries for any given company. You could easily sort this table on any of the fields (including state) much easier than you could from the multiple tables you have.
If the table you have are based off of the states the companies are located in, what happens when you get a client in one of the other availble 26 states? you would have to create an entirely new table to acomindate them under your current circumstances. Under the suggested table structure, you would not have to do anything but enter the data and select the state. Worse case scenario is you woul dhave to add that state to the table that the look up refrences.
 

Users who are viewing this thread

Back
Top Bottom