Sorting Report Contents Based on =Count(*) Values

[nick404]

I have a report that counts the number of records in each section. I would like to sort the layout of the report based on highest to lowest values shown in the count? The 'sort ascending' / 'sort descending' isn't available for an =count(*) box.



I tried setting the control source as a count in one of my queries (ex query1), however, the record source for the entire report is a separate query (ex query2) and I keep getting a parameter entry box for query1, which I try to link the count to (since this query contains a count field but query2 does not).

This question builds off a previous thread, here's the link for clarification https://www.accessforums.net/reports...ort-52977.html

Code query1:

SELECT TOP 4 Count(tblMasterData.NonConformance) AS CountOfNonConformance, tblMasterData.PartNumber, tblMasterData.NonConformance
FROM tblMasterData
GROUP BY tblMasterData.PartNumber, tblMasterData.NonConformance
HAVING (((tblMasterData.PartNumber)=[Enter Part # to Search]))
ORDER BY Count(tblMasterData.NonConformance) DESC;



Code query2:

SELECT tblMasterData.ID, tblMasterData.PartNumber, tblMasterData.EntryDate, tblMasterData.Commodity, tblMasterData.NonConformance
FROM tblMasterData INNER JOIN qryPartSearch ON (tblMasterData.NonConformance=qryPartSearch.NonCon formance) AND (tblMasterData.PartNumber=qryPartSearch.PartNumber )
ORDER BY tblMasterData.Commodity, tblMasterData.NonConformance;

[ranman256]

make a count query qsCount
Now make another query using the count query, qsCount, join it to the data table tData (on the field your counting)
Now your tData data can be sorted descending

[nick404]

By tData you mean my main data table? And then set the reports source to this joined query? Kind of confused on this..doesn't my query2 already do that? My query1 is essentially a count query and my query2 links this to the table.

[ranman256]

yes, I used generic names, pls replace w yours.
The new 2nd query joins the data table to the counting query.
The report uses this 2nd query.

[nick404]

Ok yes, so I did this, they are the query1 and query 2 in the original post above (query1 = count, query2 =join tbl). But i do not quite understand how to get this to sort my count of records in the report? This isn't bound to any fields in a table or query is it simply a =count(*) of records in a report. Here is a quick snip of what i mean. The red is the counted records value, and the blue is the order i want them (sorted on descending value).

[nick404]

and added this to my report:

Private Sub Report_Load ()
Me.OrderBy = "txtCountOfNonConformance DESC"
Me.OrderByOn = True
End Sub

no luck. the name for the box containing the record count is "txtCountOfNonConformance" and record source is a field from my query. It asks me for a parameter value each time..?

[nick404]

Alright, scratch all that above, I got it to work by adding the field 'CountOfNonConformance' from the first query to the second query and set that as the control source for the counting textbox. I have effectively removed the above VBA from the report, however, I continue to receive a "Enter Parameter for 'txtCountOfNonConformance'" message every time I attempt to run the report. I have reloaded msa many times but it perseveres to show me this message. any ideas?

[nick404]

Never mind, used database documenter and found where the error was coming from.