Matching fields in reports

[THWatson]

I have a database which has some 400 family surnames, and each surname is assigned to a District. The families in these districts are called on by some 40 Connectors, leaving each Connector with about 10 families upon which to call.<br>The main database includes, among many others, the following fields...<br>LastName (every entry has one of these)<br>District (every entry has one of these)<br>Connector (a C appears in the Connector column if that LastName is a Connector)<br><br>There is also a second table which identifies the various Districts by their number, and the corresponding Connector.<br><br>When making a report grouped by District, I would like the page heading to show, for example, &quot;District 01 - Jones&quot; (assuming Jones is the Connector's LastName). This would readily identify both the District and the Connector's name at the top of the page. I can get the pages by District groupings with no problem, and I can format the Connector's name on that page in shading (by using a Conditional Formatting command that comes into play when the Connector column has a C in it). But I'm sure there must be a way to do what I want...I just can't figure it out.<br><br>I have tried various types of joins between the main table and the Connector's table, by District, and by Connector, and get all kinds of wonderful results except the one I want.<br><br>Any suggestions?<br>Thanks.<br>Tom

 

[seaport]

I am not very clear about what you want. However, you may try using two grouping levels: district and connector. <br><br>Another way I can think about is:<br>In the underlying query of the report, create a field - DistrictConnector: Distinct & Connector. Then use that field &quot;DistrictConnector&quot; as the grouping level.

 

[Cornerstone]

Not sure I am clear either but if you just want the district name - connector name and they are both in the same table then:<br><br>In the report design go int o the grouping and sorting button and change the grouping from district, which is what I assume it is now to:<br><br>[district] & &quot;-&quot; & [connectorname]<br><br>

 

[THWatson]

Thanks, Seaport and Cornerstone.<br>I have tried your suggestions. They don't really give me what I want. Changing the sorting and grouping in the report to [District]&&quot; - &quot;&[Concector] puts the families in a particular district on a page and then the Connector on the next page, making 2 pages for each District.<br><br>Maybe I didn't explain myself well originally. Here, using only fields that are critical to my problem, is how the main database table is set up.<br>LastName&nbsp;&nbsp;&nbsp;FirstNames&nbsp;&nbsp;&nbsp;District&nbsp;&nbsp;&nbsp;Connector<br>Adams&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Jim&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;01<br>Franklin&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Ben&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;01<br>Jones&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Wendy&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;01<br>Smith&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Fred&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;C<br>Butcher&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Glen&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;02<br>Samuels&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Albert&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;02<br>Winstone&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Greta&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;02<br>Felcher&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Margaret&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;02&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;C<br><br>As this shows, Fred Smith is the Connector for District 01, so a C appears by his name in the Connector field. Likewise, Margaret Felcher is the Connector for District 2, so a C appears beside her name in the Connector field.<br><br>When I go to make a report - with each district on a separate page - I have no trouble whatsoever grouping them into districts. The page header for District 01 will show &quot;District 01&quot; and the page header for District 02 will show &quot;District 02&quot;<br><br>What I would like to be able to do is have the page header show like this:<br>&quot;District 01 - Fred Smith&quot;<br>&quot;District 02 - Margaret Felcher&quot;<br><br>This would readily identify not only the District but the Connector for that District on the top of each page. As I indicated in my initial posting, I found a way, using conditional formatting, to show the Connector name for the District in grey colour on the page...and this is semi-okay...but not as I would like it.<br><br>Again, maybe my problem is the design of the original database table (I didn't do that) so maybe I have to do some fixing there. But before I do that, I wondered if there is a way to accomplish what I have outlined without going to that length.<br><br>I am interested in your suggestions.<br>Tom

 

[THWatson]

Guess what!<br>After pondering and pondering, I finally figured it out. The trouble was that I was working with an existing query. Once I made an entirely new query, and combined fields in it, the rest was solved.<br>Your suggestions helped put me on the right track.<br>Thanks for your help.<br>Tom