Appending values from a select query to a table

[martakeithy]

Hi,
I have a database with a list of clients who I wish to send mail, and then keep brief details of the type of mail, date sent etc...
What I have at the moment is a client table and mailshot table. The client table has name address etc...
The mailshot table holds the type of mailshot; leaflet, brochure etc...
As the relationship between them was m:n, I have seperated them with a small sentmail table. This table contains the primary keys from client and mailshot tables, plus a primary key of it's own.
I have made a query based on client and mailshot, which selects clients by geographical area and the mailshot id - so far so good. This query is then mailmerged with a word letter.
The problem is I want to keep a record of the mailshot. I thought I could use the small table to do this, just to store all the client primary keys to whom mailshots were sent?
First of all is this the best way?
Secondly, how do I move to the sentmail table, all the primary keys of the clients selected for the mail shot. I also want to move the primary key of the mailshot table to the sentmail table.
I would be relieved if anyone could suggest a way to sort this out.
Thanks
Marta

 

[PWise]

create a append query based upon the select query

 

[martakeithy]

Hi Pwise,
thanks for the advice. My problem is I am having trouble finding out how to create an append query from a select query in the books that I have.
Can you advise?
Thanks
Marta

 

[PWise]

you say
i have made a query based on client and mailshot, which selects clients by geographical area and the mailshot id - so far so good.
in the append grid put this query and create an append query

 

[martakeithy]

Hi Pwise,
I have done what you said and it worked. Thank you!
This leaves me with two separate operations: one to make the mailshot; one to update the sentmailings table.
I am wondering if it might be possible to carry out both with one action. What do you think?
Maisie

 

[PWise]

hodo you make the mail shot do you go in to word or do you do it with access?

 

[martakeithy]

Hmmm!
That's a moot point at the moment. I'm having trouble getting the mailmerge function to work in Access. I go to tools; export to word; select the document, then an message appears telling me there is no data (or words to that effect).
I'm going to have to spend a bit longer figuring out how to do it.
Ideally though I would want to run the merge from Access. That being the case, do you know what I should do to run the select and append queries in one action?
Thanks
Marta

 

[PWise]

this is how i would do i do it dont have code handy but i will give you the steps

1)create a word mailmerge document
on the open data source dialog check select method
click open select via odbc
select odbc name
select options check views ok
select your query
fill in your merge fields
2)create a append query append to sentmail table primery key from client where it matches geographical area sent and
on your form create a command button primery key from mail shot (i would add to sentmail table datesent) and append that also
3)on your form crate a command botton in the on click event
create a word object
open word doc
print out
close doc
run your append query