Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Loop through each report to display next table

Status
Not open for further replies.

d222222

Programmer
Jun 12, 2007
34
US
I hope this will make sense. I'll do the best I can to explain what I'm trying to do. I have a report that is not returning the results the way I want it to. This is how I want it to display:

Site Code Site Name
SENIOR_CTR Senior Center

Property Code Property Name
CTR_001 Senior Center - Fontana

Parcel Code Parcel Name
01921274 Fontana Senior Center

Property Code Property Name
CTR_002 Senior Center - Rialto

Parcel Code Parcel Name
01912743 Rialto Senior Center - Main Street
01935673 Rialto Senior Center - First Street

The Site Code report table runs off a database table with a primary key of Site_ID and it is the parameter the report is using.

The Property Code report table is run off another database table that has a primary key of Pr_ID and it also has a column that contains the Site_ID to connect the two tables.

The Parcel Code report table is run off another database table that has the Pr_ID to connect the Property and Parcel table but not the Site_ID which is the parameter.

As you can see, what I need is for it to get the FIRST Pr_ID from the Property table, find the Pr_ID's that match in the Parcel table and list all of the parcels, then go to the next Property ID and list all of the Parcel ID's for it (as shown above).

However what it is doing is just listing all of the Property Codes for the Site and then all of the Parcels for those properties. It is returning the correct data so it is getting the Pr_ID from the second table but I want it to loop through.

This is what it is returning:

Site Code Site Name
SENIOR_CTR Senior Center

Property Code Property Name
CTR_001 Senior Center - Fontana
CTR_002 Senior Center - Rialto
CTR_002 Senior Center - Rialto

Parcel Code Parcel Name
01921274 Fontana Senior Center
01912743 Rialto Senior Center - Main Street
01935673 Rialto Senior Center - First Street

It is also repeating the second Property Code twice because there are two Parcel Codes for that one and it should only show it once.

The first table is using one dataset and the other two tables are using another dataset. I've tried so many things like separate datasets and subreports and nothing is working.

Can anyone help me?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top