Grouping records in a report

[dawnd3]

Is it possible to only show records and their grouping when there is more than one record in a grouping. In other words, I have 1000's of records that I have grouped in a report, but most only have 1 detail record per grouping. I want to illiminate these from the report and just show the ones that have 2 or more detail records. Make sense?

Thanks,

Dawn

http://www.calliopeconsulting.net

 

[tracy1234]

You could use something like this:


select attributes, count(*)
from mytable
where ...
group by attributes
having count(*)>1

 

[dawnd3]

Ok, Tracy, I am pretty clueless when it comes to VBA. Can you tell me where to put this code? In the OnOpen of the report? And can you tell me the exact syntax? Say the report will be based on the query qryMatches. And the records in the detail section are Company names.

Thanks a bunch,

Dawn

http://www.calliopeconsulting.net

 

[tracy1234]

Are you going straight from a table to the report or are you using a query as the report source?

 

[SmallBrain]

Let me try

I assumed your table is called "Customer" that have field called "CustTown"

Create two queries, 1st is counted query, and 2nd is select query, like this:

1st QUERY
1. Create query of your table in design view
2. Enable Total Query (click icon "Sigma" on toolbar)
3. Insert field that you consider to be a group...twice, example:
Insert CustTown in column 1 and in column 2
4a. Make sure, on column 1, row Total shows Group By
4b. Make sure, on column 2, row Total shows Count
5. Save the query to "qrCountCust"
---

2nd QUERY
1. Create query design view
2. Insert your tabel "Customer" and query "qrCountCust" above as source.
3. Create relational from table Customer into qrCountCust on CustTown (drag fieldname CustTown from table Customer onto CustTown in qrCountCust)
4. Insert all field you want to show in report from table Customer
5. Insert field 'CountOfCustTown' from query qrCountCust
6. On row 'Criteria', column 'CountofCustTown', write this ">1" (without quotes)
7. Run this query.. Is all records show number greater than 1 on column 'CountOfCustTown'? If the answer is yes.. your query is successfull and ready to be a report source, else, read step-by-step my tip above.
8. Save this query to "qrReportCust"
---
Now create your report, using wizard and point to this 'qrReportCust' as report data source.


Maybe HELP


PS: If you need more explanation, mail me and attach a sample database to me.
tertib@infomedia.web.id

 

[tracy1234]

Sorry Dawn, had to run out without thoroughly reading your response saying you were basing your report on a query.

SmallBrain's reply is correct. He uses the logic I alluded to in my sql to create a count query and then join that to another query which pulls the "meat" of the report.

It sounds like you already have a query called qryMatches to pull the bulk of your report fields. So you'd need to follow BigBrain's directions to create two more queries - One to get a count of customers with an appropriate group by field, and another to join qryMatches and the count query.

This 3rd query would be what you'd base your report off of.

I hope that this reply helps you to create your solution.

-Tracy