unwanted duplicate report pages showing

[waynetheengineer]

Hi all,

I have a database that has two tables:
1 table shows different types of animals:
e.g. rabbits, bears, birds, etc.
a 2nd table that links to each animal type that has individual animals of each type
e.g. in the rabbits section I would have different rabbit names with colour, age, etc. of each of them.

Now when I do a select query to search for all animals that have tails (rabbits being one of them) and then base a report on this query, I get multiple duplicate report pages on the same animal type. This is because, for example, the animal type rabbits has a bunch of individual rabbits within it that all have tails; therefore, the single rabbit report page gets shown many times in the report.

What I would like to do is eliminate the duplicate report pages from showing. I've searched the internet, forums, Help section, etc. and couldn't find anything.

Anyone have any suggestions?

Wayne.

[Rabbit]

Hi all,

I have a database that has two tables:
1 table shows different types of animals:
e.g. rabbits, bears, birds, etc.
a 2nd table that links to each animal type that has individual animals of each type
e.g. in the rabbits section I would have different rabbit names with colour, age, etc. of each of them.

Now when I do a select query to search for all animals that have tails (rabbits being one of them) and then base a report on this query, I get multiple duplicate report pages on the same animal type. This is because, for example, the animal type rabbits has a bunch of individual rabbits within it that all have tails; therefore, the single rabbit report page gets shown many times in the report.

What I would like to do is eliminate the duplicate report pages from showing. I've searched the internet, forums, Help section, etc. and couldn't find anything.

Anyone have any suggestions?

Wayne.

So.. you just want to return rabbit instead of individual rabbits? Use SELECT DISTINCT

[waynetheengineer]

Thank you for the reply, let me try explaining some more:

The Record Source of my report is a select query that contains all fields from both tables: animal type, individual animals.

Each report page contains a Header section showing the details of the animal type (e.g. rabbits) and Detail section with a sub-table list showing all the individual animal details that correspond to the animal type (e.g. rabbit name, rabbit age, rabbit location, has tail or not, etc.). So when you view the report in print preview version, each report page should show a unique animal type with all it's individual animals shown down on the page.

But my report is based on a querry that searches for an animal characteristic (e.g. has tail or not). When I view the query in table view, it produces duplicate animal types in the listing because there are lots of individual animals in each type. And this gets carried through in the report level and duplicates the animal type page for every individual animal querried. I just want to show the animal type page once for each animal type, regardless of how many individual animals were querried in each type. So basically I just want to return a query result once for each animal type if the type contains at least one result (either in the type section or individual animal section) based on the query criteria.

I hope my explanation is better :)

Thanks,
Wayne.

So.. you just want to return rabbit instead of individual rabbits? Use SELECT DISTINCT

[Rabbit]

If the query also includes its animal type, then it sounds like you're looking for Grouping in reports. A Group Header will allow you to show animal type information only once per group rather than once per occurence.

[waynetheengineer]

Hi,

Sorry I made a mistake in my explanation. The report does show them only once, but my form (which has single pages showing an animal type and individual animals per main record listing) is also based on that same query. The form, when shifted to the next animal type record, repeats itself for every individual animal within the animal type. This is because the query outputs duplicate animal types for the search criteria. I would use SELECT DISTINCT ROW in my form Record Source, but the rows are not distinct in the query (the query contains all fields from both animal type and individual animals). How do I choose only distinct animal types from the query result, but still show the form as showing all the individual animals for every type?

Thanks again,

Wayne.

If the query also includes its animal type, then it sounds like you're looking for Grouping in reports. A Group Header will allow you to show animal type information only once per group rather than once per occurence.

[waynetheengineer]

I figured it out.

What I did was I created two separate queries. The report has it's record source set to one query and the form has it's record source set to the other query. The report's query has fields from both tables. The form's query has fields only from the animal type table.

[Rabbit]

Glad you got it working.