Automatic Population of a Field

[Rene Crespo]

Hello,

Sorry about the length of this explanation!

I am using Access 2000 to assist an export sales department keep track of
its weekly offers of products to clients. The database already keeps track
of "product offers" and prepares a report that a group of Clients can
receive by email or fax each week. The challenge is that not all clients
are interested in all the "items" on the product offers (more than 200 items
listed). While some clients would like to know about Item1, Item2 offers
others only would want to hear about Item10 and 12, still others may indeed
want the totality of all ItemsOffered. I am looking for a way to prepare
client-specific reports listing only those Items that the particular client
is interested in. Client interests in items don't change, they always are
looking for specific items. The Sales department may add items to their
offers (by adding an Item to "tblItems") from time to time.

Solution: I have created an additional field in the tblClients table
(OfferPreferences). This field is linked to a SUBTABLE
(tblOfferPreferences) containing four fields: tblOfferPreferencesID,
ClientID (same as tblClientsID, unique identifier), ItemID and Email (a
logical variable, YEs/No). I then proceed to manually populate the ItemID
field by picking one of the 200 items from the tblItems (a table containing
data about items that the company produces or carries). I repeat the
process until I have picked all 200 items in the Items table. The problem
is that there are many clients and manually entering all items from the
tblItems table is quite time-consuming. Moreover, if the sales deaprtment
adds an item to the tblItems after the OfferPreferences have already been
set for a client, they must also remember to go back to each ClientID (in
the tblClients) and be sure to add that new item to the subset of Client
(OfferPreferences) and then to check off the logical variable (Yes/No) if
that particular client will likely want to receive offers for this new item
in the future. Then they have to repeat this process for every client in
the tblClients.

Is there an easier way to do this? Perhaps with an Update query? I want to
be able to automatically populate the field ItemID based on the existing
items listed in a separate table. Each new client should automatically have
a subset of items linked to ClientID. The logical variable Yes/No should
then be checked off for all those items that the client would want to
receive by email. I would use a form to update this information
periodically and a query linking weekly offers by the sales department with
the client preferences just defined.

Any ideas?

Thank you in advance!!!

René M. Crespo
Export Manager
Uni-Viande Inc
Quebec, Canada
re*********@bellnet.ca
ve****@uni-viande.com

[Pavel Romashkin]

I am a little confused with your current setup of tblOfferPreferences
and addition of the field OfferPreferences to tblClients.
It seems to me that all you need is a junction table between tblClients
and tblItems, having 2 fields - ClientID and ItemID, their combination
serving as PK. If a client wants an item, add a record with ClientID,
ItemID to the junction table. Then you can pull out the needed items
easily in SQL.
Updating the junction table is very easy using a subform.
As far as automating this, I see no way - how will the automation know
what each client wants? Also, as new items are added, do you want the
new item to be offered to every client or not? Either way you can easily
automate the update to the junction table, but you can't make the DB
discriminate the addition - say, one client gets new item while another
does not.

Pavel

Rene Crespo wrote:

Hello,

Sorry about the length of this explanation!

I am using Access 2000 to assist an export sales department keep track of
its weekly offers of products to clients. The database already keeps track
of "product offers" and prepares a report that a group of Clients can
receive by email or fax each week. The challenge is that not all clients
are interested in all the "items" on the product offers (more than 200 items
listed). While some clients would like to know about Item1, Item2 offers
others only would want to hear about Item10 and 12, still others may indeed
want the totality of all ItemsOffered. I am looking for a way to prepare
client-specific reports listing only those Items that the particular client
is interested in. Client interests in items don't change, they always are
looking for specific items. The Sales department may add items to their
offers (by adding an Item to "tblItems") from time to time.

Solution: I have created an additional field in the tblClients table
(OfferPreferences). This field is linked to a SUBTABLE
(tblOfferPreferences) containing four fields: tblOfferPreferencesID,
ClientID (same as tblClientsID, unique identifier), ItemID and Email (a
logical variable, YEs/No). I then proceed to manually populate the ItemID
field by picking one of the 200 items from the tblItems (a table containing
data about items that the company produces or carries). I repeat the
process until I have picked all 200 items in the Items table. The problem
is that there are many clients and manually entering all items from the
tblItems table is quite time-consuming. Moreover, if the sales deaprtment
adds an item to the tblItems after the OfferPreferences have already been
set for a client, they must also remember to go back to each ClientID (in
the tblClients) and be sure to add that new item to the subset of Client
(OfferPreferences) and then to check off the logical variable (Yes/No) if
that particular client will likely want to receive offers for this new item
in the future. Then they have to repeat this process for every client in
the tblClients.

Is there an easier way to do this? Perhaps with an Update query? I want to
be able to automatically populate the field ItemID based on the existing
items listed in a separate table. Each new client should automatically have
a subset of items linked to ClientID. The logical variable Yes/No should
then be checked off for all those items that the client would want to
receive by email. I would use a form to update this information
periodically and a query linking weekly offers by the sales department with
the client preferences just defined.

Any ideas?

Thank you in advance!!!

René M. Crespo
Export Manager
Uni-Viande Inc
Quebec, Canada
re*********@bellnet.ca
ve****@uni-viande.com