No Duplicates in report

[SnowB1]

I have a report that is generated from a query based on Combo Box entries. In the report there is a county, city and state which they are filtering on. I was wondering how I can get it so that each city only appears once so I can total by city. Right now each city shows up as many times as it is in the database. I tried setting the No Duplicates to yes in the format tab and this did not work. Any advice would be appreciated. Thanks


Bobby

 

[Jibbadiah]

Open your query in design view.
Right click on one of the fields you are selecting.
Select "totals".
This will make your query group by city, county and state.

 

[SnowB1]

The Grouping didn't work. I need to collect all the info to count the number of items in each field, but only want to display the county, city, and state at the top of the report once. Instead I get a page for each County,City State entry. Is there anyway of passing those individual values? Thanks.

Bobby

 

[Jibbadiah]

Bobby,

It sounds like you need to look at the sorting and grouping properties of your report.
Open the report in design view.
Right Click on the top left corner and select option sorting and grouping.
Experiment with different options.

If you run the query on it's own with group by on you should only get one record for each city. If your query also selects other fields, then you are likely to have differences across the record - hence multiple records for each city. Sorting and Grouping should help you get around this.

James

 

[SnowB1]

Still not working. I still get the city for every instance of the county. Here is my Sql statement from my query.

SELECT DISTINCT PersonalInfo.Homeless, PersonalInfo.NFresident, PersonalInfo.MinSpent, PersonalInfo.CallerAge, PersonalInfo.Self, PersonalInfo.StaffBoard, PersonalInfo.FamilyFriend, PersonalInfo.CommLarge, PersonalInfo.ServeProvide, PersonalInfo.Cognitive, PersonalInfo.MentalEmo, PersonalInfo.Physical, PersonalInfo.Vision, PersonalInfo.Hearing, PersonalInfo.Multiple, PersonalInfo.AdvocacyServe, PersonalInfo.AssistDevServe, PersonalInfo.ChildServe, PersonalInfo.CommServ, PersonalInfo.FamilyServe, PersonalInfo.HousingServe, PersonalInfo.ILSkillServe, PersonalInfo.InformationServe, PersonalInfo.MentalRest, PersonalInfo.MobilityServe, PersonalInfo.PeerServe, PersonalInfo.PersonalServe, PersonalInfo.PhysicalRest, PersonalInfo.PreventiveServe, PersonalInfo.Prostheses, PersonalInfo.RecServe, PersonalInfo.RehabTech, PersonalInfo.CounselServe, PersonalInfo.Therapeutic, PersonalInfo.TransportServe, PersonalInfo.YouthServe, PersonalInfo.VocationServe, PersonalInftherServe, PersonalInfo.Newsletter, PersonalInfo.EventFlyer, PersonalInfo.VFIBrochure, PersonalInfo.WaiverInfo, PersonalInfo.Survey, PersonalInfther, PersonalInfo.Advocacy, PersonalInfo.SkillTraining, PersonalInfo.PeerSupport, PersonalInfo.Deinstitutionalization, PersonalInfo.ReferralPAS, PersonalInfo.ReferralVFI, PersonalInfo.CallerCounty, PersonalInfo.CallerCity, PersonalInfo.CallerState
FROM PersonalInfo
WHERE (((PersonalInfo.CallerCounty)=[Forms]![CityStateCountyForm]![CmbCallerCounty]) AND ((PersonalInfo.CallerCity)=[Forms]![CityStateCountyForm]![CmbCallerCity]) AND ((PersonalInfo.CallerState)=[Forms]![CityStateCountyForm]![CmbCallerState]));

Is there anything I can do in here or is it a report thing that I just have to mess with until I find the right setting? Thanks for your help.

Bobby

 

[Jibbadiah]

Bobby,

I presume you must need all of the fields on your report, so a group-by was never going to work anyway.

Open the Report in Design View, Right click the top left corner. Choose Sorting and grouping and add a field for City. This will add an extra level to your report. Place the fields of County, City and State under this heading and put the rest under the details section.

You want to group on each instance of city. Set Group Interval to 1.

I have included a sample DB that does something very similar. Open the report in design view and try to understand what it is doing. I think that it operates in the same way that you want yours to operate.

HTH.

James

 

[SnowB1]

Thanks for the sample. I still haven't gotten mine to work, and I even started over and went through the wizard. I will keep trying. Thank you for all your help.

Bobby

 

[Jibbadiah]

Bobby,

If you post a sample database with just the basics and dummy records I can take a look for you. Please save as Access 97 if you want me to help.

James