Report Calculation with Blank Fieldsd 1

[kentwoodjean]

I have checked several posts using the Search function but still have not clear on how to correct my issues. Below is the SQL on my report query which seems long due to my multiple calculations. With each date, if even one of my 6 sum fields are blank, the %field for any of the 6 fields will not populate. I tried to use a default of 0 in my table for those fields that were summed, but this does not help. Any advice would be appreciated.

SELECT DISTINCTROW [WMS REPORT DETAIL].COSTCTR, [WMS REPORT DETAIL].[DEPT NAME], [WMS REPORT DETAIL].SECT, [WMS REPORT DETAIL].SECTNAME, Left([When],4) & "/" & Right([When],2) AS [DATE], Sum([WMS REPORT DETAIL].SCHEDHRS) AS [Sum Of SCHEDHRS], Sum([WMS REPORT DETAIL].NONSCHED) AS [Sum Of NONSCHED], Sum([WMS REPORT DETAIL].ABSENCE) AS [Sum Of ABSENCE], Sum([WMS REPORT DETAIL].BREAK) AS [Sum Of BREAK], Sum([WMS REPORT DETAIL].UNION) AS [Sum Of UNION], Sum([WMS REPORT DETAIL].MEETINGS) AS [Sum Of MEETINGS], Sum([WMS REPORT DETAIL].OTHER) AS [Sum Of OTHER], Sum([WMS REPORT DETAIL].VACATION) AS [Sum Of VACATION], Sum([WMS REPORT DETAIL].TRAINING) AS [Sum Of TRAINING], ([Sum Of SCHEDHRS]+[Sum Of NONSCHED]+[Sum Of ABSENCE]+[Sum Of BREAK]+[Sum Of UNION]+[Sum Of MEETINGS]+[Sum Of OTHER]+[Sum Of VACATION]+[Sum Of TRAINING]) AS TotHrs, ([Sum OF SCHEDHRS]/[TotHrs]) AS [%Sched], ([Sum OF NONSCHED]/[TotHrs]) AS [%NSched], ([Sum OF ABSENCE]/[TotHrs]) AS [%Abs], ([Sum OF BREAK]/[TotHrs]) AS [%Brk], ([Sum Of MEETINGS]/[TotHrs]) AS [%Un], ([Sum OF OTHER]/[TotHrs]) AS [%Othr], ([Sum Of VACATION]/[TotHrs]) AS [%Vac], ([Sum OF TRAINING]/[TotHrs]) AS [%tr], ([Sum OF MEETINGS]/[TotHrs]) AS [%Mtgs]
FROM [WMS REPORT DETAIL]
GROUP BY [WMS REPORT DETAIL].COSTCTR, [WMS REPORT DETAIL].[DEPT NAME], [WMS REPORT DETAIL].SECT, [WMS REPORT DETAIL].SECTNAME, Left([When],4) & "/" & Right([When],2);

 

[dhookom]

One of the issues is a non-normalized table structure. Also, I recommend against using column alias' in other expressions in the query. I would use
Nz(Sum(SCHEDHRS),0) + Nz(Sum(NONSCHED),0)+...

Other suggests: Don't create a column named Date since Date is a function in Access and this may lead to issues.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kentwoodjean]

Thanks for your response. I am not sure that I completely understand normalized or non-normalized table structure. The data for the table is imported from a text file (csv) and contains the following fields:

CostCtr, DeptName, Sect, SectName,Date (all in Text Form and Date has now been changed to When). In number form I then have the categories - SchedHrs,NonSched,Absence,Break,Union,Meetings,Other,Vacation,Training.

What makes this non-normalized? When I did my query I summed the numbers for each category and also added expressions to get the %each category # represented of all the numbers addded together for a given date. Should these expressions have been in a 2nd query and then take a third query and comine the 1st 2?

Finally, are you saying that instead of using the field (I.e) absence, I would use the nz statement, as well as use it for all the other categories?

Just trying to understand and clarify. Thanks

 

[dhookom]

I would replace this column expression
([Sum Of SCHEDHRS]+[Sum Of NONSCHED]+...
with
Nz(Sum(SCHEDHRS),0) + Nz(Sum(NONSCHED),0)+...
Do the same for the percents.

If your structure is limited by an import than maybe you can't do much about it.
If these fields store hours ideally you would have a records with each hour value in its own record.

FKField Category Hours
123 Sched 6
123 NonSched 3
123 Absence 0
...

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kentwoodjean]

Thanks a ton, it worked perfectly and gave me exactly the result I was looking for.