Sum is counting all records including 0s

[inspectorgadjet]

I am working on a project that requires a LOT of calculation, and I have managed to get to the end and have everything working. Or so I thought until yesterday afternoon.
I have a query, that calculates the average number of hours each person has spent in a specific situation per week. This works just great and returns the correct results. I then need to work out which Band they fall into based on number of hours. For example. Person A averaged 4 hours and will go into Band A, Person B averaged 12 hours and will go into Band B. I've also got this part of the query working. So far so good. The query outputs data similar to the following:

QueryA
Name AvgHours BandA BandB BandC BandD
PersonA 5 1 0 0 0
PersonB 12 0 1 0 0
PersonC 16 0 0 1 0

I've filled in the Band columns using an Iif function:
Iif(AvgHours BETWEEN 0 AND 6, 1, 0) and so on.

The database user cna then view a report which displays all of the data with checkboxes instead of 1s and 0s. This works out just great.

The next stage for me is to get the total number in each band and send it to another central database for reporting. This is where the problem comes in. I have created another query to calculate the totals.

QueryATotals
SELECT 1 AS Area,'2003/2004' AS Year, Sum(QueryA.BandA) AS BandATotal, Sum(QueryB.BandB) AS BandBTotal, Sum(QueryC.BandC) AS BandDTotal, Sum(QueryD.BandD) AS BandDTotal FROM QueryA

I have hardcoded an area number and year in for test purposes. The problem now seems to be that BandA returns a count of all the records in the query. So using my above example, it would return 3. BandB returns 0 when it should be a 1, and BandC returns a 1, which is correct.

I have tried using iif to calculate the right results, using a count in a subquery with a where clause, but nothing works. BandA always gets the total number of records, BandB is always 0, and BandC gets the right result. I'm stumped. Can anyone help me out?

 

[ecniv]

SELECT 
 1 AS Area,
 '2003/2004' AS [Year], 
 Sum(QueryA.BandA) AS BandATotal, 
 Sum(QueryB.BandB) AS BandBTotal, 
 Sum(QueryC.BandC) AS BandDTotal, 
 Sum(QueryD.BandD) AS BandDTotal 
FROM QueryA
Group By
 Area,
 [Year]

I think the above is ok - as you'd need to group by those not included in functions. Whether you just need the 1 and '2003/2004' instead of Area, Year.
Note Year is a function, perhaps this is mixing up, put square brackets around the field names and new field names and table names to be safe.

I ran a fast test on an mdb with a table that replicates the results from the queryA you mentioned, and each one added correctly.


Vince

 

[inspectorgadjet]

Thanks, but it didn't help.
I used Year as an example. It's actually getting the value from a combo on a form.
Also, I just noticed that I said QueryA.BandA and QueryB.BandB, etc. It should actually all have been QueryA. There are four columns BandA-BandD in QueryA.
I think the main query - QueryA - is doing something weird with the values it returns for the Band columns.
I wish I could show the exact queries here, but that would cause some problems for me at work.

 

[inspectorgadjet]

Well I think that as the main query is slightly more complicated than a normal query, I am going to paste a slightly edited version of the queries here which may get me some better help. I am going to colour code the sections of the query as it is enough to make your head spin.

Code for QueryA (Returns the correct results for the report)

SELECT [COLOR=DarkOrange]UserInfo.SURNAME[/COLOR], [COLOR=DarkRed]UserInfo.FORENAME[/COLOR], [COLOR=DarkSlateBlue]UserInfo.YR[/COLOR], [COLOR=DarkGreen]UserInfo.[E/DATE] AS [Relevant Date][/COLOR], 
[COLOR=Blue]NZ((SELECT SUM(TotalHours) FROM qryProvisionHours 
WHERE qryProvisionHours.ID=UserInfo.ID AND 
(StartOfProvision BETWEEN Forms![Area Summaries]!cmbStartFinYear AND Forms![Area Summaries]!cmbEndFinYear) AND 
(EndOfProvision BETWEEN Forms![Area Summaries]!cmbStartFinYear AND Forms![EOTAS Area Summaries]!cmbEndFinYear)),0) 
AS [Total No Provision Hours][/COLOR], 
[COLOR=Lime]NZ((SELECT SUM(TotalProvisionDays) FROM qryProvisionHoursByStudent 
WHERE qryProvisionHoursByStudent.ID=UserInfo.ID AND 
(StartOfProvision BETWEEN Forms![Area Summaries]!cmbStartFinYear AND Forms![Area Summaries]!cmbEndFinYear) AND 
(EndOfProvision BETWEEN Forms![Area Summaries]!cmbStartFinYear AND Forms![Area Summaries]!cmbEndFinYear)),0) 
AS [Total No School Days Excluded][/COLOR], 
[COLOR=Magenta]FormatNumber(NZ(IIf([Total No School Days Excluded]=0,0,([Total No Provision Hours]/[Total No School Days Excluded])*5),0),2) AS [Average Hours][/COLOR], 
[COLOR=Red](IIf([Average Hours]>=(SELECT TOP 1 BandAStart FROM BV159HourBands 
WHERE ValidFrom <= Forms![EOTAS Area Summaries]!cmbStartFinYear ORDER BY ValidFrom) And 
[Average Hours]<(SELECT TOP 1 BandAEnd FROM BV159HourBands 
WHERE ValidFrom <= Forms![EOTAS Area Summaries]!cmbStartFinYear ORDER BY ValidFrom),1,0)) AS [Band A][/COLOR], 
[COLOR=Magenta](IIf([Average Hours]>=(SELECT TOP 1 BandBStart FROM BV159HourBands 
WHERE ValidFrom <= Forms![EOTAS Area Summaries]!cmbStartFinYear ORDER BY ValidFrom) And 
[Average Hours]<(SELECT TOP 1 BandBEnd FROM BV159HourBands 
WHERE ValidFrom <= Forms![EOTAS Area Summaries]!cmbStartFinYear ORDER BY ValidFrom),1,0)) AS [Band B][/COLOR], 
[COLOR=Purple](IIf([Average Hours]>=(SELECT TOP 1 BandCStart FROM BV159HourBands 
WHERE ValidFrom <= Forms![EOTAS Area Summaries]!cmbStartFinYear ORDER BY ValidFrom) And 
[Average Hours]<(SELECT TOP 1 BandCEnd FROM BV159HourBands 
WHERE ValidFrom <= Forms![EOTAS Area Summaries]!cmbStartFinYear ORDER BY ValidFrom),1,0)) AS [Band C][/COLOR], 
[COLOR=DeepSkyBlue](IIf([Average Hours]>=(SELECT TOP 1 BandDStart FROM BV159HourBands 
WHERE ValidFrom <= Forms![EOTAS Area Summaries]!cmbStartFinYear ORDER BY ValidFrom),1,0)) AS [Band D][/COLOR]
FROM UserInfo
WHERE UserInfo.CATEGORY Like "*PE*" AND (UserInfo.[START DATE]<[Forms]![Area Summaries]![cmbEndFinYear] AND 
(UserInfo.[DATE LEFT]>=[Forms]![Area Summaries]![cmbStartFinYear] OR IsNull([DATE LEFT])))
ORDER BY UserInfo.SURNAME, UserInfo.FORENAME;

You already have the code for the query I am using to get the totals...so hopefully someone will have an idea for me.

 

[ecniv]

woah!

Right... first thought thats struck me is that you seem to be filtering the ... hummm nope...

scary. And thats using queries too...
Does this run fairly fast???

General notes...
-- These two are filtered by the start and end of financial years...
qryProvisionHours
qryProvisionHoursByStudent


I think you could make the statment smaller if you join the query for the start and end dates (SELECT TOP 1 BandAStart FROM BV159HourBands
WHERE ValidFrom <= Forms![EOTAS Area Summaries]!cmbStartFinYear ORDER BY ValidFrom) to the other queries/tables (probably has to be cartesian though :/ )

Still thinking...

 

[inspectorgadjet]

It does actually run fairly fast...fortunately its not a huge database...there are just seven different sites using it and each of them will never really have more than a few hundred entries at a time.
I'm trying not to think about joins now...the queries for this thing have done my head in. It's Friday and I'm about ready to give up for the weekend.

 

[ecniv]

SELECT UserInfo.SURNAME, UserInfo.FORENAME, UserInfo.YR, UserInfo.[E/DATE] AS [Relevant Date], 

NZ([TPHrs],0) AS [Total No Provision Hours], 
NZ([TPDs],0) AS [Total No School Days Excluded], 

FormatNumber(NZ(IIf([Total No School Days Excluded]=0,0,([Total No Provision Hours]/[Total No School Days Excluded])*5),0),2) AS [Average Hours],
 
(IIf([Average Hours]>=qryFinStEnd.[BandAStart] And [Average Hours]<qryFinStEnd.[BandAEnd],1,0)) AS [Band A], 
(IIf([Average Hours]>=qryFinStEnd.[BandBStart] And [Average Hours]<qryFinStEnd.[BandBEnd],1,0)) AS [Band B], 
(IIf([Average Hours]>=qryFinStEnd.[BandCStart] And [Average Hours]<qryFinStEnd.[BandCEnd],1,0)) AS [Band C], 
(IIf([Average Hours]>=qryFinStEnd.[BandDStart],1,0)) AS [Band D]

FROM 
UserInfo Left Join 
(SELECT qryProvisionHoursByStudent.ID, SUM(qryProvisionHoursByStudent.[TotalProvisionDays]) as TPDs FROM qryProvisionHoursByStudent Group By qryProvisionHoursByStudent.[ID]) as qryProHrsByStu On UserInfo.ID=qryProHrsByStu.ID) 
Left Join 
(SELECT qryProvisionHours.ID, SUM(TotalHours) as TPHrs FROM qryProvisionHours Group By qryProvisionHours.ID) as qryProHrs On UserInfo.ID=qryProHrs.ID,
(SELECT TOP 1 BandAStart, BandAEnd, BandBStart, BandBEnd, BandCStart, BandCEnd, BandDStart FROM BV159HourBands WHERE ValidFrom <= Forms![EOTAS Area Summaries]!cmbStartFinYear ORDER BY ValidFrom) as qryFinStEnd

WHERE 
UserInfo.CATEGORY Like "*PE*" AND 
(UserInfo.[START DATE]<[Forms]![Area Summaries]![cmbEndFinYear] AND 
(UserInfo.[DATE LEFT]>=[Forms]![Area Summaries]![cmbStartFinYear] OR 
IsNull([DATE LEFT])))

ORDER BY 
UserInfo.SURNAME, 
UserInfo.FORENAME;

This is using joins.. but I can't test it. might give you an idea or two though?? Or at least on monday morning when you come off the weekend high

The sub queries may need the filters on too.


Vince

 

[inspectorgadjet]

Thanks for all your help with the query ecniv. I really appreciate it. I think this might work, but it's not quite there yet. I've had to add filters to the hours and days subqueries to make sure they only get data relevant to the correct year.
Also, the subquery to get the band start and end dates just will not work. It says Join not supported. Any ideas?
I am thinking it might be useful to create a function that will determine if the value is in that band or not.

 

[ecniv]

Hi,

Different tack...

SELECT qryProHrsByStudent.StudentId, qryProHrsByStudent.SumOfProHrs, tblLUBands.[Band]
FROM qryProHrsByStudent LEFT JOIN tblLUBands ON (qryProHrsByStudent.SumOfProHrs < tblLUBands.BEnd) AND (qryProHrsByStudent.SumOfProHrs >= tblLUBands.BStart);

This is from a quick db (attached) which hopefully simulates a bit of this very complicated process.

Itried thinking it through again but the cartesian join just doesn't woork once a left join is used. So I thought that the banding start and end had to relate to something held. This is that attempt.

Might be a way forward?
Note when changing the join type from = to >=,<=,>,<,<> it won't display the tables visually in the query builder.


Vince