Combining update and append functionality through a query 2

[electioniering]

Hi all,
recently started using access queries and would like to know how to accomplish the following scenario:
I'm trying to build a mailing list DB (Using Access 2003 MDB, btw). Right now I have 3 tables:
- Mailer (holds information about mailer, date sent, media, etc.)
- CustomerList (info about our customers like address)
- JoinTable (where I link the PK's from both previous tables).

When I get ready to send a mailer, I'd like to be able to run a query, for example, of people that haven't received mailer ID# 5063. I'll export those names, give it to the Ad department. Then, I'd like to be able to mark those customers with this new mailer ID that I've just used.
So later if I run a query of customers to show all customers with all mailers sent I can have something like this:

JoinTableID - MailerID - CustomerListID
3 - 5063 - 005
78 - 4900 - 005
56 - 6898 - 012

Notice how one customer has been reached with two different mailers.

Right now I can either update the JoinTable (which only overwrites the mailerID was already there) or I can append to the end of the JoinTable (which only repeats the same information that already existed in the table).

I know this is kind of confusing, but if someone could give me some hints or tell me where I can get information on how to solve this, anything at all, would be great.

Thanks.

 

[PHV]

repeats the same information that already existed in the table
Create a composite index on (MailerID,CustomerListID) not allowing duplicates.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[electioniering]

Thanks for the reply, but I don't believe that would help. I don't want to prevent mailer or customer duplicates, I meant that I don't want to repeat the same information that was already there. For example, if my query showed that patient # 566 was sent mailer #999, when I try to append to the JoinTtable, I then have two rows with this same information. Only difference being the JoinID, which is an AutoNumber.

 

[lespaul]

I don't want to repeat the same information that was already there

By adding an index to JoinTable of 'No Duplicates' to the combination of MailerID and CustomerListID you won't be able to enter the same mailer to the same customer twice. Isn't that what you're trying to accomplish?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[electioniering]

I see what you mean, lespaul. Is it simply making the property change in the design view of the table Indexed (No Duplicates) to both the mailer and customer fields?

Thanks for the clarification.

 

[electioniering]

I just changed both mailer and customer fields in the JoinTable to "No Duplicates" and I'm not able to enter the same mailer id twice in the entire table, not just for one customer.
Maybe I misunderstood something lespaul and PHV have said.

 

[PHV]

Select BOTH fields in the table design view for creating the composite unique index.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[electioniering]

AAAAh, I got it. I looked in the help file and see that I have to click on the Indexes (lightning bolt) icon.

Now the problem is that that only solves a minor part of the equation. I've yet to figure out how to change a value in the query I just ran (add a new mailer ID to customers I just sent adverts to), then add that list back into the JoinTable to keep track of mailers sent.

 

[electioniering]

Any clues?