Sorting a query for selected dates (1 Viewer)

PaulD2019

Registered User.
Local time
Today, 03:25
Joined
Nov 23, 2019
Messages
75
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

Code:
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

Active member
Local time
Yesterday, 19:25
Joined
Jan 22, 2016
Messages
202
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;
 

cheekybuddha

AWF VIP
Local time
Today, 03:25
Joined
Jul 21, 2014
Messages
2,267
Out of curiosity, what datatype is field tblJobRefBook.Client ?

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

PaulD2019

Registered User.
Local time
Today, 03:25
Joined
Nov 23, 2019
Messages
75
Hi, It is a number field that adds the client id number to the table
 

cheekybuddha

AWF VIP
Local time
Today, 03:25
Joined
Jul 21, 2014
Messages
2,267
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.
 

Users who are viewing this thread

Top Bottom