query returns unwanted multiple occurances of records

[Krazy Darcy]

I have a book catalog in which author details are in a sub table. I am using
a query to show only records with print label is set to "yes". this print que
list is in another sub table.
My problem is that some books have joint authors and have more than one
entry in the authors table. This means when I run the query It shows more
than one label for the book for each of it's authors. (the label uses record
no, title, author last name, author first name, and subject group).

I want to print only one label per book regardless of how many authors it has?

Thanks for your time.

 

[Duane Hookom]

What do you want to do with the authors? Do you want to show only one? Do you
want to display them all? Will they all fit on the label?

If you want to display all authors in a comma separated list, search google
on "Hookom generic concatenate".

I think you could also use a multi column subreport inside the label to
display the authors.

 

[John W. Vinson]

I have a book catalog in which author details are in a sub table. I am using
a query to show only records with print label is set to "yes". this print que
list is in another sub table.
My problem is that some books have joint authors and have more than one
entry in the authors table. This means when I run the query It shows more
than one label for the book for each of it's authors. (the label uses record
no, title, author last name, author first name, and subject group).

I want to print only one label per book regardless of how many authors it has?

Thanks for your time.

Be sure that only the fields in the book catalog table have their "show"
checkbox checked in the grid (or, equivalently, include only those fields in
the SELECT clause of the SQL); and set the Unique Values property of the query
to Yes.

 

[Krazy Darcy]

The labels areto be printed on ordinary paper and attached to the book
(unsure uet of if just one label on outside or small one outside and bigger
inside cover). I would like to have all authors listed but the form that
shows me the "print que" (books with print que=yes) can not have sub forms as
it is a continuous form.

However I have disregarded the form at the time being as it shows te books
in the que (and used to launch the report) and added a sub report (using the
wizard) that shows the authors. Now a differant error crops up. It shows a
full label entry for the book with all authors listed on the label, but if
there is one author then the book appears once, if there are three co-authors
then the book appears in the que 3 times.

 

[Duane Hookom]

There is a generic concatenate function at
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4&SID=7b517ccf8c2c5ca4ddaa35144z879594
which should meet your needs for combining the authors into a single
expression.

 

[Krazy Darcy]

Got it going now.

It turnes out my problem was because I had all the fields from the tables
that I wanted in the labels in my query. By only having record number. book
title and year in my query, the query returned only one result per book. Then
having a sub report that had fields record number, author first name, author
last name in the main report resulted in the authors appear for that book on
that label (with no duplicate labels). Before creating the queries and
reports I had defined relationships between my tables which the subreport
used to select the appropriate authors for each book based on the record
number in the main record.