For your label report's RecordSource use a SELECT DISTINCT query. Any records
where all the selected fields are the same will be printed only once. It
doesn't matter if any fields toy are not including in the label are not
duplicated, only the ones actually selected in the query.
In query design view you do this by opening the query's properties sheet
(View | Properties on the menu bar after making sure that the query, not a
field, is selected by clicking in any empty column in the design grid)) and
then selecting 'Yes' as the Unique Values property (if you switch to SQL view
after doing this you'll see that the query now starts with 'SELECT DISTINCT'.
The fact that you have duplicates does suggest that your table is not
properly normalized, and could advantageously be broken down into separate
tables. To take a simple example, say for instance you have two categories
of addressees, 'business' and 'personal', and some addressees fall within
both categories. Rather than having the addresses duplicated the table
should be decomposed into three tables, Addressees, Categories and
AddresseeCategories. The first two of these are simply unique lists of
addressees and categories. The Addressees table should have a unique numeric
AddresseeID primary key column such as an autonumber as names can be
duplicated legitimately so are unsuitable for keys. The Categories table can
have just the one column as catgory names will be unique, so this table would
be:
Category
------------
Business
Personal
You can add as many more rows as you wish of course.
The Addressees table would have columns AddresseeID, Title, FirstName,
LastName etc.
The AddresseeCategories table in fact models a many-to-many relationship
between Addressees and Businesses and would have two columns referencing the
primary keys of each as foreign keys. So if Jane Smith has an ID of 42 is
both a personal and business addressee, and John Brown has an ID of 99 but is
a business addressee only the rows in AddresseeCategories to model this would
be:
AddressID Category
-----------------------------
42 Personal
42 Business
99 Business
For your labels report you simply join the Addressees and
AddresseeCategories tables, use a SELECT DISTINCT query again and restrict it
to whatever category or categories you want to print.
When tables are normalized in this way all redundancy is eliminated. This
protects the integrity of the data as each 'fact' is stored only once, so the
situation I came across in one database where I found myself listed under
three separate guises as author of technical articles in my own field of
work, K Sheridan, K W Sheridan and K V Sheridan (the last a simple typo) is
avoided. Anyone looking for papers by me as K W Sheridan would have been
denied the opportunity of reading those articles for which I was cited as
just K or K V Sheridan. No doubt many would regard this as a blessing, but
its not what the database was intended to do!
You might think that decomposing a table like this involves a lot of work,
but in fact its very easily done by first setting up the empty tables,
creating enforced relationships between them, and then filling them with a
few 'append' queries, leaving the AddresseeCategories table until last as the
others need to be filled first to provide the data a for this one.
Once a normalized design like this has bee set up data entry is easily
catered for by having a main form with a subform. This can be an addressees
main form and categories subform, so you enter an addressee and assign them
to one or more categories in the subform; or it can be done the other way
round with a categories main form and an addressees subform There is no
reason why you can't have both of course.
Ken Sheridan
Stafford, England
