URGENT! Sort by Count

[pwicr]

I am trying to create a report that shows how many sales each sales person has in a 2 year period and sort it from the most # of sales to lowest. I want to show all sales people not just top 5 or 10

My fields are sales person and retail date.

 

[pr2-eugin]

Show the Query you have.

 

[pwicr]

Attached is my query design

 

[pr2-eugin]

Nope, I need the SQL view of the Query..

 

[pwicr]

SELECT tblDEALERS.DNameShort, tblSPFSALESPEOPLE.SALESPERSON, tblSPFSUBMISSIONS.SPFSALEID, tblSPFSUBMISSIONS.RETAILSALEDATE
FROM (tblDEALERS INNER JOIN TblDlrPurchases ON tblDEALERS.DLRID = TblDlrPurchases.DLRID) INNER JOIN (tblSPFSUBMISSIONS INNER JOIN tblSPFSALESPEOPLE ON tblSPFSUBMISSIONS.SPFNUM = tblSPFSALESPEOPLE.SPFNUM) ON tblDEALERS.DLRID = tblSPFSALESPEOPLE.DEALER
GROUP BY tblDEALERS.DNameShort, tblSPFSALESPEOPLE.SALESPERSON, tblSPFSUBMISSIONS.SPFSALEID, tblSPFSUBMISSIONS.RETAILSALEDATE
HAVING (((tblDEALERS.DNameShort)=128) AND ((tblSPFSUBMISSIONS.RETAILSALEDATE) Between #9/30/2011# And #9/30/2013#));

 

[pr2-eugin]

Try,

SELECT [COLOR=Blue][B]Count(tblDEALERS.DNameShort) As CountOfSomething[/B][/COLOR], tblDEALERS.DNameShort, tblSPFSALESPEOPLE.SALESPERSON, tblSPFSUBMISSIONS.SPFSALEID, tblSPFSUBMISSIONS.RETAILSALEDATE
FROM (tblDEALERS INNER JOIN TblDlrPurchases ON tblDEALERS.DLRID = TblDlrPurchases.DLRID) INNER JOIN (tblSPFSUBMISSIONS INNER JOIN tblSPFSALESPEOPLE ON tblSPFSUBMISSIONS.SPFNUM = tblSPFSALESPEOPLE.SPFNUM) ON tblDEALERS.DLRID = tblSPFSALESPEOPLE.DEALER
GROUP BY tblDEALERS.DNameShort, tblSPFSALESPEOPLE.SALESPERSON, tblSPFSUBMISSIONS.SPFSALEID, tblSPFSUBMISSIONS.RETAILSALEDATE
HAVING (((tblDEALERS.DNameShort)=128) AND ((tblSPFSUBMISSIONS.RETAILSALEDATE) Between #9/30/2011# And #9/30/2013#))
[COLOR=Blue][B]ORDER BY Count(tblDEALERS.DNameShort); [/B][/COLOR]

 

[pwicr]

SELECT tblDEALERS.DNameShort, tblSPFSALESPEOPLE.SALESPERSON, tblSPFSUBMISSIONS.SPFSALEID, Count(tblSPFSUBMISSIONS.RETAILSALEDATE) AS CountOfSales, tblSPFSUBMISSIONS.RETAILSALEDATE
FROM (tblDEALERS INNER JOIN TblDlrPurchases ON tblDEALERS.DLRID = TblDlrPurchases.DLRID) INNER JOIN (tblSPFSUBMISSIONS INNER JOIN tblSPFSALESPEOPLE ON tblSPFSUBMISSIONS.SPFNUM = tblSPFSALESPEOPLE.SPFNUM) ON tblDEALERS.DLRID = tblSPFSALESPEOPLE.DEALER
GROUP BY tblDEALERS.DNameShort, tblSPFSALESPEOPLE.SALESPERSON, tblSPFSUBMISSIONS.SPFSALEID, tblSPFSUBMISSIONS.RETAILSALEDATE
HAVING (((tblDEALERS.DNameShort)=128) AND ((tblSPFSUBMISSIONS.RETAILSALEDATE) Between #9/30/2011# And #9/30/2013#))
ORDER BY Count(tblSPFSUBMISSIONS.RETAILSALEDATE);

I tried changing it as I know how many the dealership sold but I need to know how many each sales person WITHIN the dealership sold. That code is giving a weird result. (I did try the code as given first)

 

[pr2-eugin]

Could you upload a Stripped DB?

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[Gagan]

Mr. Paul,

You are helping many people to fix their problem, Can you please help me to fix my problem in the Report, I wants to sum the number of items in Sub Report, I can see it in Main Report but can not calculate, If i tried msg will appear, For your review I atached the database in below link.
access-programmers.co.uk/forums/showthread.php?t=255250