Updating subform with info relating to many entries

[Guest]

I am setting up a Contact Management database whose subform records all
mailings. We might send the same brochure to 1500 of the 4400 entries, and
500 of a different brochure to some of these, plus others. I am very new to
access, but have got the form and subform set up, but I am sure I wont have
to enter in each brochure for each entry. Despite an expensive training
course, and books I have purchased, I cannot seem to find the answer. I
would be very grateful of help.

 

[Arvin Meyer [MVP]]

I would set up a mailing group (or a brochure group if you prefer to call it
that)

Each group will contain the foreign key value of each person/entry to
receive that brochure/mailing.

tblContacts
ContactID
FirstName
LastName
etc.

tblMailingGroups
GroupID
GroupDescription
etc.

tblMailings
GroupID
ContactID

the Mailings table will use a compound primary key so that the unique
combination cannot be repeated. Yes, there will be thousand of entries in
the Mailing intersection table, but the data will only need to be entered
once for each unique combination of Contact and Group. After that you can
make as many mailings as you want with that table. DO NOT FALL INTO THE TRAP
OF ADDING A COLUMN FOR EACH MAILING GROUP. That's a spreadsheet, not a
database.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Thank you Arvin. I can see the logic in what you suggest, and understand
your ref to a spreadsheet - this is what we are moving from. As a beginner,
it will take me a little time to digest your reply fully, and to go back to
the books to see what a compound primary key is.
Regards

 

[Guest]

Arvin, several hours later.....
with the Mailings Table, am I to manually enter the GroupID and ContactID? I
fear this will would be too time consuming, although I will need to decide
who gets what mailings, so a Contact may belong to several Groups.
Suggestions?
Appreciate your help.

 

[Arvin Meyer [MVP]]

The subform for the Mailings table should consist of 2 combo boxes. I'd
temporarily set the default value of the GroupID each time I started adding
that group's contacts, then go into design view and change it for the next
group. If you have any mailings where all of the individuals (or most of
them) are in a mailing group, I'd use an Append Query to put the entire set
in, then remove the few that you may not want. There is no way to do this
without pain unless you already have the data in a spreadsheet.

In that case you can import the data as a table, then normalize it by using
it to append records that match.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Hello Arvin,
I am slowly getting there with this database, but have another question I
hope you can help me with. As previously mentioned on my main contacts form
I have a subform to record mailings. I have also set up mailing groups as
suggested.

Items can be sent on an irruglar basis to individual contacts (e.g. 1
brochure to 5 contacts from various groups). These we need to enter
inidividually, no problem.

With the large mailings to Groups, how can I record these mailings onto the
same form used for the individual mailings above? I have all the relevant
fields in the subform.

tbl Despatch Record
DespatchID
ContactID (linked to Table 1 - main form)
CollateralID
Group Description (linked to MailoutGroupID in tblMailoutGroups)
Collateral Name (linked to CollateralID in tblGrpMailoutRecord)
Qty
Date


tblMailings
GroupID (linked to MailoutGroupID in tblMailoutGroup)
ContactID (linked to Table1 - main form)

tbl MailoutGroups
MailoutGroupID
Group Description
ContactID

tbl GrpMailoutRecord
GrpMailoutID
CollateralID (linked to Collateral ID in tblBrochureList)
Qty
Date

BrochureList
CollateralID
CollateralName

Any help will be gratefully received.
Regards
Mondomum