Count & Group a recordsource

[Happy YN]

In a library database, I have a sql string which builds itself up using many criteria on a form from a number of tables. This is then passed on as a recordsource of a report . All works fine. However I wish to create another report which counts the number of records in one specific field e.g city of that huge sql so that it reads 5 instances of London,3 instances of Manchester etc. I know how to make a count query based on a real table or query but how can I make one which queries the result of this complex sql string
I know I can use the count on a report grouping and have managed to do so, however since all I want is the count of each group, I need nothing in the detail section but as soon as I set the detail height of the report to 0 the count doesn't work. If I set it to a minute number .002, all is fine but then if I have 100 records of london, this is evident in the report as there is a lot of white space before it gives the result 100 instances of london owing to the section being multiplied 100 times so I seem to be stuck!
Any ideas please? Thanks

 

[fpendino]

One idea is to turn that sql string into a QueryDef, which stores that SQL and saves it as a query(Check the help file for QueryDefs). Then you can query off of that stored query and delete it once you are done with it.

Hope that helps.

Frank

 

[Happy YN]

That was a quick reply!
I did try that but am not too sure how to process the querydef. I'll have to look for help on that
Thanks again

 

[fpendino]

Happy,
The Access Help File does go into how to setup 'QueryDefs' pretty well. Just do a search for 'Create QueryDef' and it should be the first thing that pops up.

Anyway's, here is an example that I found in my help file that shows some code for it.



The following example creates a new QueryDef object, then opens the query in Datasheet view:

Sub NewQuery()
Dim dbs As Database, qdf As QueryDef, strSQL As String

' Return reference to current database.
Set dbs = CurrentDb
strSQL = "Your SQL String"

' Create new query.
Set qdf = dbs.CreateQueryDef("RecentHires", strSQL)

'Opens the query by it's Name - "RecentHires"
DoCmd.OpenQuery qdf.Name

Set dbs = Nothing
End Sub