Automatically populate fields?

  • Thread starter Thread starter Rene Crespo
  • Start date Start date
R

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
(e-mail address removed)
(e-mail address removed)
 
Hi,

I would rather use another table that would enumerate the client-item
relation. Basically, that table would get two fields, ClientID, ItemID, and
if a record exist, then the client is interested in that item (and if there
is no record for a given clientID about an ItemID, then, that client is not
interested in that item).

To produce your report, just make an inner join between that table and
your initial ones, Clients and Items, through their common fields:


FROM ( Clients INNER JOIN ClientsItems
ON Clients.ClientID=ClientsItems.ClientID )
INNER JOIN Items
ON Items.ItemID=ClientsItems.ItemID

and use that query, rather than the initial tables, as source for your
report.


That is generally the procedure we use to make a many-to-many relation (many
clients implied in a relations with many items): through a third junction
table.


When you add a client, you add nothing to the junction table (by default,
the client want no item), but you can ask in which item the client may be
interested. If so, add the required records (NewClient, ItemID) in
ClientsItems.

When you add an item, you may ask which client would be interested in it,
and then, also add a the new records (ClientID, NewItem) in ClientsItems.

To add or remove an "interest", add or remove the record in ClientsItems.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top