A2k Best form design for mutiple address editing

[Steve]

Hi all

Ok then I have searched through the back postings for a while now on google
and read some interesting stuff on how to model a typical client / order db
however....

My problem is that I have a client who wants to have multiple contacts &
multiple address for each client. (about 2,000 client) He also has about 10
standard access driven letters he sends out to them periodically - however
he wants the ability to set up multiple mailing address's for each client,
which means in theory that any contact may have the same piece of mail sent
to more than 1 address.

He has some clients who like Invoices etc going both to the office / shop /
home - but of course this differs per client

I have set up a Main client table and sub tables for Address / Contacts /
etc and junction tables to link together the Mailing addresses etc and this
seems fine - my big concern is that using proper database design in my
junction table for the Mailing address - I can see no easy way of allowing
the user to view and edit who gets the letters efficiently. They work well
as a list in a continuous form - but if you have say 10 letters x 4 contacts
x 2 addresses it would seem very difficult for the user to maintain easily
and spot any errors quickly.

I have thought about have the letter names as different fields in the
junction table but that seems to go against all the rules of database
design - yet would be easy to maintain as it would look like a spreadsheet
for the user.

I have also thought about making a temp table at the editing stage and
setting up as above and then re writing back into a normalised manner - but
that seems very long winded - so any thoughts on the best way to display the
multiple addresses would brilliant.
Many thanks for any thoughts
Steve
Tables
CLIENT
ClientIDNo (PK)
CreditScore
Etc Etc

CLIENT ADDRESS
ClientAddressIdNo (PK)
ClientIDNo (FK)
AddressType
Addressee
Street
Area
Etc Etc

CLIENTCONTACT
ClientContactIDNo (PK)
ClientIDNo (FK)
Client Forename
Etc Etc

JUNCTION TABLE - MAILING ADDRESS
MailingAddressIDNo (PK)
ClientIDNo (FK)
ClientAddressIDNo (FK)
LetterIDNo (FK)
LetterAddressee
LetterSalutation

[Pieter Linden]

"Steve" <st***********@virgin.net> wrote in message news:<6M********************@newsfep1-win.server.ntli.net>...

Hi all

Ok then I have searched through the back postings for a while now on google
and read some interesting stuff on how to model a typical client / order db
however....

My problem is that I have a client who wants to have multiple contacts &
multiple address for each client. (about 2,000 client) He also has about 10
standard access driven letters he sends out to them periodically - however
he wants the ability to set up multiple mailing address's for each client,
which means in theory that any contact may have the same piece of mail sent
to more than 1 address.

He has some clients who like Invoices etc going both to the office / shop /
home - but of course this differs per client

I have set up a Main client table and sub tables for Address / Contacts /
etc and junction tables to link together the Mailing addresses etc and this
seems fine - my big concern is that using proper database design in my
junction table for the Mailing address - I can see no easy way of allowing
the user to view and edit who gets the letters efficiently. They work well
as a list in a continuous form - but if you have say 10 letters x 4 contacts
x 2 addresses it would seem very difficult for the user to maintain easily
and spot any errors quickly.

I have thought about have the letter names as different fields in the
junction table but that seems to go against all the rules of database
design - yet would be easy to maintain as it would look like a spreadsheet
for the user.

My two cents - if you seriously violate sound database design
principles, querying your database will be absolutely awful. You can
get around *some* of it with union queries and other ways, but it'll
be a real nuisance to set up. What would the problem be with basing
the report on a query and/or filtering in the open event of the
report? Setting this thing up as a spreadsheet to make them happy
seems to be a serious limitation. IMO, it's just asking for trouble.
But you're the one collecting the paycheck... I'd try to talk them out
of that one...