Reports with a grouped field.

[Paul]

I'm trying to produce a report that was afirst page that with have a list of
the same fields then all the other pages just like a normal report. like this:

front page:
CITY (list of all the city fields.)

second page
ORG NAME, ADDRESS, CITY, ect.

basicly all the city names from my report together on their own. How is this
done?

 

[Allen Browne]

Use a subreport.

1. Create a query that lists all the cities. If you don't have a separate
table of cities, the query will be something like this:
SELECT DISTINCT City FROM Clients
WHERE City Is Not Null
ORDER BY City;

2. Create a report using that query. Save.

3. Place that report as a subreport into the Report Header section of your
main report. If you don't see a Report Header section when you open your
main report in design view, it's on the View menu.

 

[Paul]

Ok I tried that, but it only gave me the first city on the list. I linked the
subport using the ID field. When i tried linking by city it gave me the first
city twice. i'm a bit lost.

 

[Paul]

Sorry, meant to say. when linked by ID it just gave me the first city then
the main report.

 

[Allen Browne]

Open the main report in design view.
Right-click the edge of the subreport control, and choose Properties.
Clear the LinkMasterFields and LinkChildFields properties.

The subreport should then show all cities.

 

[Paul]

That sort of works. It will show me ALL the cities, but I only want the
cities that match with the cities on the report. so if I select between a
date and 5 records come up, I want the five cities that match to be on the
first page.

 

[Allen Browne]

In that case, you will need to apply criteria to the subreport's query that
matches the same criteria you apply to your main report.

The easiest way to so that would be to use a form to enter the criteria. The
subreport's query would then have something like this in the Criteria under
the date/time field:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]