Prev: Blendcode table and multiple plants
Next: Look up boxes
From: icccapital on 5 Jan 2010 15:04
I was curious to get some ideas for the best way to maintain the address of
mailers that had been sent out.

So by way of example we send out mailers or invoices periodically. And lets
say they go to Joe Smith at 1 place last quarter. Now when Joe Smith changes
his address we then send it to 2 Other Place. Originally I had designed the
database to have the linking table with the invoice number and the client
contact ID, but the issue with that is that when Joe Smith changes his
address it appears that we sent the invoice last quarter to the new address.

Theoretically, what do you guys think is the best way to manage this. I
could copy the address into the linking table and get rid of the contact ID.
Or create a new contact id and copy the address and person into a new table
that basically represents that period's address information. So Joe Smith
would be in this table twice, once with old address with the last quarter
date and once with new address and this quarter's date and the linking table
would point to the ID for these. What do you think?
From: Jeff Boyce on 5 Jan 2010 15:15
Here's another approach ...

If you have an [Address] table and a [Person] table you can use a
[PersonAddress] (junction) table to show which person at which address
(using the primary keys of each). If you then add a FromDate and a ToDate
field to that junction table, you can end-effective date Joe's old address
and begin using the new address (actually, the new AddressID).

If you need to keep history of which address was used to mail objectX,
include the AddressID in your record of mailing.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"icccapital" <icccapital(a)discussions.microsoft.com> wrote in message
news:B14AC2B1-B2CE-4CC8-90C9-99F1FE099BEC(a)microsoft.com...
>I was curious to get some ideas for the best way to maintain the address of
> mailers that had been sent out.
>
> So by way of example we send out mailers or invoices periodically. And
> lets
> say they go to Joe Smith at 1 place last quarter. Now when Joe Smith
> changes
> his address we then send it to 2 Other Place. Originally I had designed
> the
> database to have the linking table with the invoice number and the client
> contact ID, but the issue with that is that when Joe Smith changes his
> address it appears that we sent the invoice last quarter to the new
> address.
>
> Theoretically, what do you guys think is the best way to manage this. I
> could copy the address into the linking table and get rid of the contact
> ID.
> Or create a new contact id and copy the address and person into a new
> table
> that basically represents that period's address information. So Joe Smith
> would be in this table twice, once with old address with the last quarter
> date and once with new address and this quarter's date and the linking
> table
> would point to the ID for these. What do you think?


From: Fred on 6 Jan 2010 12:07
IMHO you have a fundamental "entity: issue. The invoice, with all of it's
then-current details, is an entity that you created and need to look back at.
In that case you would probable need an "invoices" table (probably a pair of
linked tables, the header information and the detail information.

 | 
Pages: 1
Prev: Blendcode table and multiple plants
Next: Look up boxes