How to join rows of multiple tables through query

[jason]

Using Access 2003.

I'm trying to create a simple mailing list DB with three tables:
MailerTbl (has info about the mailer media, date sent, etc): MailerID,
MailerName, MailerDescr, MailerDate, etc.
Mailers_Join_MailListTbl (join table between MailerTbl and MailListTbl):
MailerJoinID, NameID, MailerID
MailListTbl (holds info about contacts): NameID, Name, Address, City, State,
etc.

We plan to use this DB to keep our contact names organized and to be able to
know who we sent which mailer to and when.
I'd like to be able to query who hasn't been sent a mailer and mark that
individual.
So if I have a mailer with ID# 1, and a contact name of Jon Test (ID# 34) I
could run a query looking for contacts that haven't been sent mailer #1 :
SELECT MailList.Name, Mailers.MailerName, Mailers_Join_MailList.MailerID
FROM Mailers INNER JOIN (MailList INNER JOIN Mailers_Join_MailList ON
MailList.NameID = Mailers_Join_MailList.NameID) ON Mailers.MailerID =
Mailers_Join_MailList.MailerID
WHERE (((Mailers_Join_MailList.MailerID)<>1));

This gives me the names that have never been contacted with mailer #1. Then,
after I use the names, I'd like to be able to (in a query, or some sort of
automated way) mark these names with mailer #1 in the
Mailers_Join_mailListTbl.

Hope this isn't too confusing, but it's as clear as I can make it right now.

Any help would be greatly appreciated.
Jason

 

[Ron2006]

Create an update query that is basically the same as your current query
but have it then set the "Mailers_Join_MailList.MailerID" to 1 or
whatever it is supposed to be.

Ron

 

[jason]

Thanks for the reply.
Perhaps I didn't clarify my question. With the update the way you suggest,
that would remove the mailer id and just replace it with a new one.
In the Mailers_Join_MaillistTbl is basically where I want to join the Mailer
and MailList tables. The Mailers_Join_MaillistTbl itself has a PK with an
autonumber.
The table looks like:
MailerJoinID NameID MailerID
1 1 1
2 2 1
3 2 2
etc...
So everytime I send a contact a particular mailer, I would indicate it in
this table. For example, in the second row, MailerJoinID# 2 shows that
contact ID# 2 was sent Mailer# 1. Later, contact ID# 2 was also sent Mailer#
2.

I know this seems kinda confusing, but not sure how else to explain it.

I would still appreciate some help on this.

Jason

 

[Ron2006]

Then I think my suggestion is:

The same query that is used to establish who you have not sent the
mailer to be used first to send the mailer and then as the basis for an
append query to add records to the table you describe below:

The table looks like:
MailerJoinID NameID MailerID
1 1 1
2 2 1
3 2 2
etc...

I would also seem that you need to have that query use some type of
"global"/unbound txtbox or whatever to test for which mailer you are
looking for (which my may be doing but have left out for simplicity of
posting here.

The WHERE clause would need to refer to that field and not the literal
1 or whatever.

Ron

 

[jason]

Thanks again Ron.
You're suggesting to first use a query to pull the contacts I want to send a
mailer to and then use an append query to add the records to the Join Table.
But how can I change the value of some of the data before appending it to
the Join Table.
For example, I run a query for all contacts that haven't been sent mailer#
"4." After I'm done with that list, I want to add them to the Join Table but
with a new mailer ID of "4." I don't want to change the existing mailer ID
for that contact, because then I wouldn't know that I've already sent a
certain mailer. So basically, I want to be able to see that contact John Doe
was sent mailer #1, the mailer# 5, etc.
From the help file, it looks like I may be able to use a subquery for this,
but only if I'm using an ADP DB. I'm using an Access MDB. I'm still stumped
on this one.

Any additional suggestions would be great.

Thanks.

 

[Ron2006]

We have reached a "Show me yours and I will show you mine situation".

I don't believe that the query you show at the beginning will give you
a proper list of people to receive mailers.

It will NOT cover the situation of a brand new customer who has NOT
received any mailers.
and
It will supply the same customer multiple times if he has received
mailer 1 and 2 and 3 and you ask if he has received mailer 4. (And in
fact you would get them even IF that had received mailer 4 since you
simply not counting mailer 4 records)

Once you have a query that accurately supplies a unique customer ID for
someone who has NOT received the mailer you desire, you write an append
query that uses that query as its root and appends to the mailerjoin
table using the customerID and the number of the new mailer ID.

You need 1 query to supply all the customers who HAVE received Mailer
(say) 4 (forms!Formaname!NewMailerID). And then our BASE query should
probably be a query grouped by customers linking your customer ID
tables with this HAVE query with a left join (outer join, I beleive)
where the customer ID is NOT in the HAVE Query (is null).

The append query then may look something like this:

INSERT INTO MailJoinTable( [customerID],[MailerID])
SELECT [BASE].[customerID], forms!Formaname!NewMailerID

Ron