Sorting a query for selected dates (1 Viewer)

[PaulD2019]

Hi all,

i need to make a new report on the database I created for use in our office so we can find the top spending clients between two dates selected on a form, I guess it will be mainly used for top spends by clients for the year but being able to choose selected sales between two dates would be a bonus.

Currently I have a list form created from the below query that shows the totals for each client since the database was created

SELECT tblJobRefBook.JobStatus, tblJobRefBook.Client, Sum(tblJobRefBook.CostOfWorks) AS SumOfCostOfWorks, tblClients.ClientName
FROM tblClients INNER JOIN tblJobRefBook ON tblClients.ClientID = tblJobRefBook.Client
GROUP BY tblJobRefBook.JobStatus, tblJobRefBook.Client, tblClients.ClientName
HAVING (((tblJobRefBook.JobStatus)="Invoiced" Or (tblJobRefBook.JobStatus)="Invoiced On Valuation"))
ORDER BY Sum(tblJobRefBook.CostOfWorks) DESC;

The table "tblJobRefBook" has fields "StartDate" & "FinishDate" that could be used to filter the dates, I guess the field "FinishDate" would be the be best one to use out of the two, I have tried but haven't had much luck so far. I want to be able to select two dates on a form then be able to show the top clients from there.

Could anyone assist please? Thanks in advance

 

[tvanstiphout]

Your current query does not involve dates, so we will modify it. I also believe that rather than a Having clause you should use a Where clause:

SELECT tblJobRefBook.JobStatus, tblJobRefBook.Client, Sum(tblJobRefBook.CostOfWorks) AS SumOfCostOfWorks, tblClients.ClientName
FROM tblClients INNER JOIN tblJobRefBook ON tblClients.ClientID = tblJobRefBook.Client
where tblJobRefBook.JobStatus in ("Invoiced", "Invoiced On Valuation")
and tblJobRefBook.FinishDate between Forms!myCriteriaForm!myStartDate and Forms!myCriteriaForm!myEndDate
GROUP BY tblJobRefBook.JobStatus, tblJobRefBook.Client, tblClients.ClientName
ORDER BY Sum(tblJobRefBook.CostOfWorks) DESC;

 

[PaulD2019]

@tvanstiphout

That works great, thanks

 

[cheekybuddha]

Out of curiosity, what datatype is field tblJobRefBook.Client ?

Is it a string, and does it hold something different from tblClients.ClientName ?

 

[PaulD2019]

Hi, It is a number field that adds the client id number to the table

 

[cheekybuddha]

Apologies, it was late last night as I read this, and I mis-read the join clause as being on two fields called ClientID, and so thought that tblJobRefBook.Client was another/different field.