Results 1 to 13 of 13
  1. #1
    FJM is offline Competent Performer
    Windows 98/ME Access 2003
    Join Date
    Jun 2010
    Posts
    117

    Sort by count

    I am running a report based on the following query


    Click image for larger version. 

Name:	Capture.PNG 
Views:	14 
Size:	24.6 KB 
ID:	31626
























    and this is the design of the report


    Click image for larger version. 

Name:	Capture2.PNG 
Views:	12 
Size:	23.7 KB 
ID:	31627


























    When the report runs, it lists the customers alphabetically and shows their individual total number of records.
    Using this setup, is it possible to reconfigure things so that when the report runs, it sorts firstly by the "number of records" (highest total to lowest total) and then by customer as there are many customers whose total number of records is only 1

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you need another query, the count , inside your query.
    make the count query: qsCountCustRecs
    select customer, Count(NCRID) as CountOfRecs from NcrDetails.

    remove NCRDETAILS table and join with qsCountCustRecs
    now your report can sort by [CountOfRecs] Desc

  3. #3
    FJM is offline Competent Performer
    Windows 98/ME Access 2003
    Join Date
    Jun 2010
    Posts
    117
    Having a bit of difficulty with the count query: qsCountCustRecs
    Can you spell out in baby steps how the query is set up.....

  4. #4
    FJM is offline Competent Performer
    Windows 98/ME Access 2003
    Join Date
    Jun 2010
    Posts
    117
    I think I have sorted the new query, but now that I have deleted the NCRDETAILS table, how is it joined to NCRFORM....

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    1 to 1 joins are not usually part of table design - what would be the point? All the fields could go on one table. Your counts are by customer so that would need to be used in the join, which doesn't seem possible based on your table design. This may need to be revisited.

    Another option would be to use the Sort & Group within the report itself, this may or may not work.

  6. #6
    FJM is offline Competent Performer
    Windows 98/ME Access 2003
    Join Date
    Jun 2010
    Posts
    117
    Hello all
    This is the query I have designed...
    Click image for larger version. 

Name:	Capture3.PNG 
Views:	10 
Size:	22.4 KB 
ID:	31637









































    When I run the query it gives me the results I need. The thing I am struggling with is how to convert this into a report which gives me what I need

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The date field needs to be a "where", not a group by. I don't see how it can be giving you the results you want, unless you are doing this for only one day. (BTW, it is very bad form to use Access reserved words as field names - "date" is a reserved word in Access and you will have to be very careful how you refer to it in future, it is far better to use a different name.)

    You're right, unless you can join the counts query to the table this won't work. You need table redesign.

  8. #8
    FJM is offline Competent Performer
    Windows 98/ME Access 2003
    Join Date
    Jun 2010
    Posts
    117
    I have changed the date field to a "where".
    I am getting the results because in the date field it asks for a start and end date based on fields in another form.
    When I choose the dates between 01/01/2017 and 13/12/2017 I get the results I expect.
    What I need now is to use the data from this query to sort into what I want.
    Is this possible ?

  9. #9
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Did you try the report's sort?

  10. #10
    FJM is offline Competent Performer
    Windows 98/ME Access 2003
    Join Date
    Jun 2010
    Posts
    117
    Yes, I have tried a couple of things but nothing I do works the way I want. I even started the report again with the wizard but no joy.
    In case I am missing a trick here or not explaining things, I need to know if by using the query above (CountOfRecs), I can produce a report either manually or with the wizard which will give me the following:-
    It will sort by Descending numbers the number of NCRID's and then as a secondary sort, by customer e.g
    FGH Ltd = 120
    TYU Ltd = 89
    KLM Ltd = 5
    ABC Ltd = 1
    BCD Ltd = 1
    MNO Ltd = 1

  11. #11
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not sure why the report sorting wouldn't work, it works for me. Maybe you can attach your database here with some sample data.

  12. #12
    FJM is offline Competent Performer
    Windows 98/ME Access 2003
    Join Date
    Jun 2010
    Posts
    117
    The database is split into Front end and Back end
    How do I attach them...

  13. #13
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Create a new database, import the two tables (remove some data if there are a lot of records and/or sensitive data). Import the query and report - and form? Close, zip, Go Advanced, click on Attach icon.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-07-2014, 02:59 AM
  2. sort filter, doesn't sort everything
    By deso in forum Queries
    Replies: 4
    Last Post: 01-25-2013, 05:27 AM
  3. COUNT Group Count Records COUNT FUNCTION
    By PMCOFFEY in forum Access
    Replies: 9
    Last Post: 11-09-2012, 09:40 PM
  4. Replies: 11
    Last Post: 12-21-2011, 09:48 AM
  5. How to sort by three sort orders
    By captgnvr in forum Access
    Replies: 4
    Last Post: 11-09-2009, 07:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums