Advise

[Simonglencross]

I have created a database and have two table one called tblSubscribers and
another called tblSubscriptions, within these tables I have a number of
subscribers and also 6 different magazine type's setup in the subscriptions
table. I had setup the database to print off labels which could then be
printed off these where separated in to cat 1, cat2, cat3, cat 4, cat 5, and
cat 6 for example, but it is now possible for a subscriber to subscribe to
any number of catalogues and these catalogues may well be sent out together
for instance this week I am sending out cat 1, cat 2 and cat3 but next week
it may be cat 2 and cat 6 but I also need to be careful as 1 subscriber
might subscribe to more than 1 catalogue and therefore I would only want one
label and not one for each catalogue selected.

Any advise would be much appreciated, I hope you can understand what I am
trying to achieve.


Kind Regards


Simon

 

[Van T. Dinh]

You have a Many-to-Many relationship and to represent this M-to-M in Access
(or any relational database), you should set up 3 Tables

* tblSubscriber
* tblMagazine
* tblSubscription which is often called the Link / Resolver Table since you
use this Table to link One (particular) Subscriber to Many Magazine and One
(particular) Magazine can be subscribed by Many Subscribers. This Table
should be fairly narrow but has a lot of Records, e.g. one Subscriber may be
linked to 6 different Magazine.

You should not have repeating groups of Fields (you have 6 repeating groups
of 1 Field each in the current set-up). This violates the first Normal Form
of Relational Database Design.

Check Access Help / Access (or any database) books and Microsoft Web site
for Many-to-Many relationship and Normal Forms.

 

[Simonglencross]

Sorry for not explaining myself properly I was very tired when I wote this I
do have three tables exactly how you have described below, its nthe next bit
I am very stuck with and if you can help it would be much appreciated.


Thank you in advance.

Simon

 

[Van T. Dinh]

If you have the correct Table Structure the tblSubscription should have the
ForeignKeys:

frg_SubscriberID
frg_MagazineID

The it is easy with a Query like:

SELECT DISTINCT frg_SubscriberID
FROM tblSubscription
WHERE ([frg_MagazineID] In (1, 2, 3))

to select disctinct Subscribers who subscribes in at least one of the
Magazines 1, 2 and 3.

 

[Simonglencross]

Yes I have the foreign keys as you have described below and thanks for your
help so far, how would you suggest is the best way of applying this so that
the user can make the selections and print off the relevant labels?

Simon

If you have the correct Table Structure the tblSubscription should have the
ForeignKeys:

frg_SubscriberID
frg_MagazineID

The it is easy with a Query like:

SELECT DISTINCT frg_SubscriberID
FROM tblSubscription
WHERE ([frg_MagazineID] In (1, 2, 3))

to select disctinct Subscribers who subscribes in at least one of the
Magazines 1, 2 and 3.

--
HTH
Van T. Dinh
MVP (Access)

Sorry for not explaining myself properly I was very tired when I wote

this

I
do have three tables exactly how you have described below, its nthe next bit
I am very stuck with and if you can help it would be much appreciated.


Thank you in advance.

Simon


since
you and
One may Normal
Form tblSubscribers
and

cat

5, subscribe I
am

 

[Van T. Dinh]

I am not sure what you are asking???

The SQL String I posted should give the SubscriberID of the Subscribers that
you need to print the Labels for.

You can modify the SQL String to obtain the Subscriber's details by using an
Inner Join to the tblSubscriber.

After you get the data correctly returned, use the Label Wizard to create
the Label Report.

 

[Simonglencross]

I need to be able to set up a way where the user can select any magazine
type combination and print off the labels with the subscribers names and
addresses on, rememebering that I only need one label per subscriber
regardless of the magazine type. for example mr smith may have subscribed to
magazine 1,2 and 3 and the operator has selected to print labels for
magazine 1 and 3 in this instance I would only want 1 address label and not
2. I need to make this process as simple as possible and user friendly so
all the operator does is somehow select the magazines and prints the labels,
does this make it a little clearer any help much appreciated.


Kind Regards

Simon

 

[Van T. Dinh]

It looks like you have a concurrent thread that leads to the same direction,
i.e. multi-select ListBox. For sample code on using the Multi-select
ListBox, see The Access Web article:

http://www.mvps.org/access/forms/frm0007.htm

I stop this thread and watch on the other thread. There is no point both of
us spending time giving you the same advice.