TOP 5 agents by state over given date range

[kudurider]

Hi,

I want to get the sum of sales of the top 5 best agents by state.

I have been able to do the following, however, this only gives me the state of "vic". I want it to show all states.

SELECT TOP 5 filedata.Debtor, Debtors.FullName, Sum(filedata.NetTotal) AS SumOfNetTotal, Debtors.State, Count(filedata.BorQ) AS CountOfBorQ
FROM filedata INNER JOIN Debtors ON filedata.Debtor = Debtors.Code
WHERE (((filedata.BorQ)="b") AND ((filedata.DepDate) Between [Forms]![F-financemenu].[startdate] And [Forms]![F-financemenu].[enddate]))
GROUP BY filedata.Debtor, Debtors.FullName, Debtors.State
HAVING (((Debtors.State)="vic"))
ORDER BY Sum(filedata.NetTotal) DESC;

which returns

DebtorFullNameSumOfNetTotalStateCountOfBorQ1429ABC company147150VIC11360XYZ Company107204.55VIC32534MNO organisation68799.86VIC31168PQR business58934.67VIC31326FGH industries32480VIC2

(count of BorQ) is the number of sales

I have tried using UNION ALL and changing the State but that only caused syntax errors.

Any assistance would be greatly appreciated.

thanks

kudurider

 

[pr2-eugin]

Hello kudurider, Welcome to AWF..

Showing just the Query will not help at this point. As we have no idea of how your DB is set up. So could you please post a Stripped down version of your file?

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)

 

[kudurider]

Hi,

sorry it has taken awhile to get back here.

I have created a small sample database as it was complicated trying to strip down the original. The attached has enough dummy data to be effective.

In this DB I have created two queries. The first shows the top 5 agents by the State of VIC. The second query shows the top 5 Sales by each state and its corresponding report.

What I really want is the combination of these to produce a result that gives me the Top 5 agents (their sum totals) per State in one query. I tried doing the UNION ALL like I did in the top 5 Sales, however, it kept giving me syntax errors.

Hope this is enough for you to be able to help.

thanks

Kudurider