Tough totals query (1 Viewer)

[poporacer]

I have a report that I need to show only the employees that meet a certain set of criteria. There are 3 tables used for this report. The first table is the Employee table that gives the specifics of the employee. The second table is the Sales Data (tblSales) that has the employee ID, Sales date, and Sales Type. The 3rd table has the sales requirement data. The criteria is a bit odd and I will try to make it clear (hopefully). The report shows the employees that have met or exceeded the "qualified" sales in the last 12 months. The tricky part is what constitutes a qualified sale. There are 2 types of sales, "S" and "SF". All of sales type "S" are qualifying. The first 6 "SF" type of sale per calendar year are not qualifying. That is where it gets tricky. So I need a query that will give me a list of the employees that have met or exceeded the sales target.
So the basic formula would be something like this:

SFFirst would be the total # of SF sales from Jan 1 of the previous year to the previous month as the current month but for the previous year. So for example taking today, June 25, 2015 SFFirst would be the # of SFSales from 1/1/14 to 5/31/14.

SFSecond would be the total # of SF sales from the current month but for the previous year to Dec 31 of the previous year. So in the above example it would be June 1, 2014 to Dec 31, 2014.

SFCurrent is the total # of SF sales form Jan 1 of the current year to the last day of the previous month.

STotal is the total number of S sales in the previous month.

SFTotal is the # of qualified SF sales for the previous 12 months.

So the formula would be something like this:

If SFFirst >=6 then SFTotal=SFSecond

Else If SFSecond+SFFirst-6<0 then SFTotal=0

Else SFTotal=SFSecond+SFFirst-6

I can get a query for each part but I can't figure out how to combine them.
The query for the SFFirst is:

SELECT EMP_ID, IIf(Sum(IIf(SalesType="SF",1,0))>5,6,Sum(IIf(SalesType="SF",1,0))) AS SFFirst
FROM tblSalesData
WHERE (SalesDate BETWEEN DateSerial(Year(Now())-1,1,1) AND DateSerial(Year(Now())-1,Month(Now()),0)) AND (SickType = 'SF')
GROUP BY EMP_ID;

I hope this makes sense. Thanks for looking at this.

 

[plog]

Can you post sample data? I will need 2 sets:

A. Starting data from your 3 tables. Include table and field names and enough sample data to cover all cases.

B. Expected resulting data when using A. Show me what you expect to be returned when you process the sample data you provide.

Either upload a spreadsheet or use this format:

TableNameHere
Field1Name, Field2Name, Field3Name...
David, 14, 1/12/2015
Sally, 32, 3/3/2013

 

[poporacer]

I attached a sample database with data and the separate queries. So what I need is a query to return the formula based on the included queries.
Here is a description of each query:

qrySFFirstPart=total number of SF Sales from the non-reported period last year.

qrySFSecondPart=total number of SF Sales from the reported period last year.

qrySFYTD=total number of qualifying SF sales for the year to date (the first 6 SF sales are not counted)

qrySLast12=total number of S sales for reported period (last 12 months)

So I need a query to combine the results of these queries based on a formula.

So for each employee I need to get the results based on the formula:
(this is pseudo code as it is not designed for a query)

If qryFirstPart>5 then qrySecondPart+qrySFYTD+qrySLast12
else if qryFirstPart+qrySecondPart-6<0 then qrySFYTD+qrySLast12
    else qryFirstPart+qrySecondPart-6+qrySLast12
    end if
end if

The results you should get from the final query (As long as you run it before July 1st...lol) would be:

PERNR QualSales
5982 25
7679 9
8037 97

 

[poporacer]

I think I am getting closer. I restructured the queries and If I can get them to work I think I can combine them. Here is what I am having problems with. This query is not returning the expected results, It returns the total number of records for each employee it doesn't filter out the date or SF criteria.

SELECT PERNR,Count(IIf( SalesType='SF' AND SalesDate Between DateSerial(Year(Now())-1,1,1) AND DateSerial(Year(Now())-1,Month(Now()),0),1,0)) AS SFFirst
FROM tblSalesData
GROUP BY PERNR;

I also tried

SELECT [COLOR=black]PERNR,[/COLOR][COLOR=#1f497d][FONT=Calibri][COLOR=black]Count(CASE WHEN SalesType='SF' AND SalesDate Between DateSerial(Year(Now())-1,1,1) AND DateSerial(Year(Now())-1,Month(Now()),0) THEN 1 else 0 END)AS SFFirst
FROM tblSalesData
GROUP BY PERNR[/COLOR][COLOR=#000000];[/COLOR][/FONT][/COLOR]

But I got a syntax error and after further research MS Access does not support the Case statement.

Any idea why the first query isn't working correctly?

 

[poporacer]

Ok, so I figured it out.... The Count function counts all records that have a value, so when I use the IIf statement I was setting it to 0 or 1 thinking that it was only going to count the 1... But because 0 is not null, it counts that entry as well. The solution is to either change the Count to a Sum function or change the IIf Statement like this:

SELECT PERNR, Count(IIf(SalesType='SF' And 
 SalesDate Between DateSerial(Year(Now())-1,1,1) And DateSerial(Year(Now())-1,Month(Now()),0),1,[B]Null[/B])) 
 AS SFFirst
FROM tblSalesData
GROUP BY PERNR;

And with that, I was able to build the complicated query with the complex logic involved... In case anyone is interested, here is the method I used to get to the end result:
First, I changed the queries so that the filter criteria is within the count statement like this:

SFFirstPart

SELECT PERNR, Count(IIf(SalesType='SF' And SalesDate Between DateSerial(Year(Now())-1,1,1) And DateSerial(Year(Now())-1,Month(Now()),0),1,Null)) 
 FROM tblSalesData
GROUP BY PERNR;

SFSecondPart

SELECT PERNR, Count(IIf( SalesType='SF' AND SalesDate Between DateSerial(Year(Now())-1,Month(Now()),1) AND DateSerial(Year(Now())-1,12,31),1,Null)) 
FROM tblSalesData
GROUP BY PERNR;

SFYTD

SELECT PERNR, IIf(Count(IIf(SalesType='SF' And SalesDate Between DateSerial(Year(Now()),1,1) And DateSerial(Year(Now()),Month(Now()),0),1,Null))<6,0,Count(IIf(SalesType='SF' And SalesDate Between DateSerial(Year(Now()),1,1) And DateSerial(Year(Now()),Month(Now()),0),1,Null))-6) 
 FROM tblSalesData
GROUP BY PERNR;

SLast12

SELECT PERNR, Count(IIf(SalesType='S' And SalesDate Between DateSerial(Year(Now())-1,Month(Now()),1) And DateSerial(Year(Now()),Month(Now()),0),1,Null)) 
FROM tblSalesData
GROUP BY PERNR;

So with these queries working, I wrote the Pseudo Code of:

SELECT PERNR, IIF(SFFirstPart>5, SFSecondPart + SFYTD+SLast12,
 IIf(SFFirstPart + SFSecondPart - 6<0,SFYTD+SLast12, 
 SFFirstPart+SFSecondPart-6+SFYTD+SLast12)

Then I just copied the part of each query and replaced the variables in the psuedocode with the actual code...Here is the result... If you can see a way to simplify it, I would gratly appreciate it.

SELECT tblSalesData.PERNR, 
 IIf(Count(IIf(SalesType='SF' And SalesDate Between DateSerial(Year(Now())-1,1,1) And DateSerial(Year(Now())-1,Month(Now()),0),1,Null))>5,
  Count(IIf( SalesType='SF' AND SalesDate Between DateSerial(Year(Now())-1,Month(Now()),1) AND 
 DateSerial(Year(Now())-1,12,31),1,Null))+IIf(Count(IIf(SalesType='SF' And SalesDate Between DateSerial(Year(Now()),1,1) And 
 DateSerial(Year(Now()),Month(Now()),0),1,Null))<6,0,Count(IIf(SalesType='SF' And SalesDate Between DateSerial(Year(Now()),1,1) 
 And DateSerial(Year(Now()),Month(Now()),0),1,Null))-6)+
 Count(IIf(SalesType='S' And SalesDate Between DateSerial(Year(Now())-1,Month(Now()),1) And DateSerial(Year(Now()),Month(Now()),0),1,Null)),
 IIF( Count(IIf(SalesType='SF' And SalesDate Between DateSerial(Year(Now())-1,1,1) And DateSerial(Year(Now())-1,Month(Now()),0),1,Null))+
  Count(IIf( SalesType='SF' AND SalesDate Between DateSerial(Year(Now())-1,Month(Now()),1) AND DateSerial(Year(Now())-1,12,31),1,Null))-6<0 , 
 IIf(Count(IIf(SalesType='SF' And SalesDate Between DateSerial(Year(Now()),1,1) And DateSerial(Year(Now()),Month(Now()),0),1,Null))<6,0,
 Count(IIf(SalesType='SF' And SalesDate Between DateSerial(Year(Now()),1,1) And DateSerial(Year(Now()),Month(Now()),0),1,Null))-6) + 
 Count(IIf(SalesType='S' And SalesDate Between DateSerial(Year(Now())-1,Month(Now()),1) And DateSerial(Year(Now()),Month(Now()),0),1,Null)),
 Count(IIf(SalesType='SF' And SalesDate Between DateSerial(Year(Now())-1,1,1) And DateSerial(Year(Now())-1,Month(Now()),0),1,Null)) +
 Count(IIf( SalesType='SF' AND SalesDate Between DateSerial(Year(Now())-1,Month(Now()),1) AND DateSerial(Year(Now())-1,12,31),1,Null))+
 IIf(Count(IIf(SalesType='SF' And SalesDate Between DateSerial(Year(Now()),1,1) And DateSerial(Year(Now()),Month(Now()),0),1,Null))<6,0,
 Count(IIf(SalesType='SF' And SalesDate Between DateSerial(Year(Now()),1,1) And DateSerial(Year(Now()),Month(Now()),0),1,Null))-6)+
 Count(IIf(SalesType='S' And SalesDate Between DateSerial(Year(Now())-1,Month(Now()),1) And DateSerial(Year(Now()),Month(Now()),0),1,Null))-6 ) ) AS AdjSales
FROM tblSalesData
GROUP BY tblSalesData.PERNR;

Thanks for taking a look at this.

 

[plog]

If all those date criteria are the same on each field, you can move it to WHERE clause and write it once.

Also, there's probably no reason use the DateSerial function on the date in the criteria. I'd use DateAdd on Date() and just let that figure it out--that will save you a few function calls.

You probably shouldn't have that iff statement resolve to NULL if its false, instead have it resolve to 0-- it will ensure math works even if you get no results.

 

[poporacer]

plog,
no the date criteria is different so I can't separate it out... In regards to the DateAdd, I haven't found a way to get the first or last of a month without using a combination of 2 functions (DateAdd and DateDiff) when I can do it with one DateSerial function. If there is a better way then I would like to know.. In regards to resolving the IIf statement to Null, I mentioned in my previous post that I was using the Count function and the Count function counts all values except null, so if I make it 0 then it still counts it if I was using the SUM within the IIf statement then resolving it to 0 would work. Unless I am not understanding what you are saying.