Eliminate vertically repeating records in a select query? 1

[sarajini]

Hi,
I am trying to put together a query with many layers.

ClientInfo (containing client name and ID) is the master table, which is connected (by “ClientID” to two child tables: Cases and BriefServices.

“Cases” has two child tables of its own: Advocates and Problems.

I wrote a query that shows me everything: ClientInfo, Cases, Briefs, Advocates, Problems…my problem is that the ClientInfo field repeats. If I have a client with both a Case and a Briefservice, the ClientInfo field is stated both on the line for the briefservice and the case. If I have a client with a Case with three advocates, the clientinfo—as well as the caseinfo—is repeated three times.

All the vertically duplicated cells make it confusing to glance at the displays-everything query.

I want to be able to display the records so that ClientInfo is only listed once for each client, even though one Client may have several cases. Likewise, I want to make sure that each case is only listed once, even though each case may have several advocates or problems.

Is there any way to do this? Another query?
If there's no way, our office can deal
Thanks very much!!!!!
sarajini

 

[scriverb]

The purpose of a query is to select and organize you data so you may do something with it. The ClientInfo data is in fact related to the data from the other tables whether there are 1,2,3,4, or 5 rows. Don't worry that is looks like this.

ACCESS provides you with other tools to make it look the right way and make it easier to read. You need to develope a report using your Query as the Record Source. When you setup your report you can Group and Sort on the ClientID field and have a Group Header where you only state the ClientInfo one time and then the details detail with will be possibily many rows are displayed in the Detail Section below it.

Also, you can list them all in the Detail Section and then the ClientInfo data controls have a property HideDuplicates that can be set to True and then the data is only printed once and then on each subsequent row in the Detail section it is blank until the data changes. This would give you one full line of data and then the extra rows would only have the related many detail information from the other tables.

I hope this is what you are looking for. Post back with questions and we will try to answer them and help you set this up.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???