sum, count, query in a report (1 Viewer)

[Chief]

hello,
Trying to sum up details of records between 2 dates.
Most of it i think is working.
I am trying to calculate how many [Assembly_Complete] (checkbox) are true and how many are false.

Query is such:

SQL:

SELECT Sum(JobInfoT.AssemblyMins_Estimated) AS SumOfAssemblyMins_Estimated, Sum(JobInfoT.AssemblyMins_Actual) AS SumOfAssemblyMins_Actual, JobInfoT.Scheduled, Count(JobInfoT.Assembly_Complete) AS CountOfAssembly_Complete
FROM JobInfoT
WHERE (((JobInfoT.AssemblyDue) Between [Forms]![MainMenuF]![MainMenuSubform].[Form]![TxtStartDate] And [Forms]![MainMenuF]![MainMenuSubform].[Form]![TxtFinishDate]))
GROUP BY JobInfoT.Scheduled
HAVING (((JobInfoT.Scheduled)=True));

Report:

Thank you, I've been on this for 2 days, nothing I find seems to work. :-(

 

[plog]

I am trying to calculate how many [Assembly_Complete] (checkbox) are true and how many are false.

COUNT will just tally the total number of records that do not have a NULL value in that field. So, if you simply COUNT a field that has 5 true records and 3 false records you will get 8 because that's how many are non-null.

To achieve what you want, you must seperate the Trues from the Falses:

AssemblyCompleteTrue: SUM(iif(Assembly_Complete = True , 1, 0))

 

[arnelgp]

you can include the "Not Completed" in your query also:

SELECT Sum(JobInfoT.AssemblyMins_Estimated) AS SumOfAssemblyMins_Estimated,
Sum(JobInfoT.AssemblyMins_Actual) AS SumOfAssemblyMins_Actual,
JobInfoT.Scheduled,
Count(JobInfoT.Assembly_Complete) AS CountOfAssembly_Complete,
Count(IIf(JobInfoT.Assembly_Complete, Null, 1)) As CountOfAssembly_NotComplete
FROM JobInfoT
WHERE (((JobInfoT.AssemblyDue) Between [Forms]![MainMenuF]![MainMenuSubform].[Form]![TxtStartDate] And [Forms]![MainMenuF]![MainMenuSubform].[Form]![TxtFinishDate]))
GROUP BY JobInfoT.Scheduled
HAVING (((JobInfoT.Scheduled)=True));

 

[Chief]

COUNT will just tally the total number of records that do not have a NULL value in that field. So, if you simply COUNT a field that has 5 true records and 3 false records you will get 8 because that's how many are non-null.

To achieve what you want, you must seperate the Trues from the Falses:

AssemblyCompleteTrue: SUM(iif(Assembly_Complete = True , 1, 0))

Thanks Mate

 

[Chief]

you can include the "Not Completed" in your query also:

SELECT Sum(JobInfoT.AssemblyMins_Estimated) AS SumOfAssemblyMins_Estimated,
Sum(JobInfoT.AssemblyMins_Actual) AS SumOfAssemblyMins_Actual,
JobInfoT.Scheduled,
Count(JobInfoT.Assembly_Complete) AS CountOfAssembly_Complete,
Count(IIf(JobInfoT.Assembly_Complete, Null, 1)) As CountOfAssembly_NotComplete
FROM JobInfoT
WHERE (((JobInfoT.AssemblyDue) Between [Forms]![MainMenuF]![MainMenuSubform].[Form]![TxtStartDate] And [Forms]![MainMenuF]![MainMenuSubform].[Form]![TxtFinishDate]))
GROUP BY JobInfoT.Scheduled
HAVING (((JobInfoT.Scheduled)=True));

SNAP!!!

Thanks Legend...
That's perfect

 

[Chief]

Hmmm
One more, this one is to count a specific ID being 7 "Shortage" and 8 "Rework"

SELECT JobInfoT.Scheduled, JobInfoT.JobTypeID, Sum(JobInfoT.Schedule_Mins) AS SumOfSchedule_Mins, Sum(JobInfoT.MachineShop_Mins) AS SumOfMachineShop_Mins, Sum(JobInfoT.AssemblyMins_Actual) AS SumOfAssemblyMins_Actual, Sum(JobInfoT.PaintShop_Mins) AS SumOfPaintShop_Mins, Sum(JobInfoT.Dispatch_Mins) AS SumOfDispatch_Mins, Sum(JobInfoT.Delivery_Mins) AS SumOfDelivery_Mins, Sum(JobInfoT.Install_Mins) AS SumOfInstall_Mins, Count(IIf([JobInfoT].[JobTypeID],8,1)) AS CountRW, Count(IIf([JobInfoT].[JobTypeID],7,1)) AS CountShort
FROM JobInfoT
WHERE (((JobInfoT.DateEntered) Between [Forms]![MainMenuF]![MainMenuSubform].[Form]![TxtStartDate] And [Forms]![MainMenuF]![MainMenuSubform].[Form]![TxtFinishDate]))
GROUP BY JobInfoT.Scheduled, JobInfoT.JobTypeID
HAVING (((JobInfoT.Scheduled)=True) AND ((JobInfoT.JobTypeID)=7 Or (JobInfoT.JobTypeID)=8));

Just getting 1 in both.

 

[Chief]

and Would you say this is correct?

SELECT Sum(JobInfoT.CC_Sheets) AS SumOfCC_Sheets, Sum(JobInfoT.CC_Sqm) AS SumOfCC_Sqm, Sum(JobInfoT.PFB_Sheets) AS SumOfPFB_Sheets, Sum(JobInfoT.PFB_Sqm) AS SumOfPFB_Sqm, Sum(JobInfoT.WhiteSatin_Sheets) AS SumOfWhiteSatin_Sheets, Sum(JobInfoT.WhiteSatin_Sqm) AS SumOfWhiteSatin_Sqm, JobInfoT.Scheduled
FROM JobInfoT
WHERE (((JobInfoT.CarcassCut_Date) Between [Forms]![MainMenuF]![MainMenuSubform].[Form]![TxtStartDate] And [Forms]![MainMenuF]![MainMenuSubform].[Form]![TxtFinishDate])) OR (((JobInfoT.PFB_Date) Between [Forms]![MainMenuF]![MainMenuSubform].[Form]![TxtStartDate] And [Forms]![MainMenuF]![MainMenuSubform].[Form]![TxtFinishDate])) OR (((JobInfoT.WhiteSatinCut_Date) Between [Forms]![MainMenuF]![MainMenuSubform].[Form]![TxtStartDate] And [Forms]![MainMenuF]![MainMenuSubform].[Form]![TxtFinishDate]))
GROUP BY JobInfoT.Scheduled
HAVING (((JobInfoT.Scheduled)=True)) OR (((JobInfoT.Scheduled)=True)) OR (((JobInfoT.Scheduled)=True));

I'm getting increased numbers on some stats.

 

[arnelgp]

something like this:

...
Count(IIf([JobInfoT].[JobTypeID] = 8, 1, Null)) AS CountReWork, Count(IIf([JobInfoT].[JobTypeID] = 7,1, Null)) As CountShort

 

[Chief]

something like this:

...
Count(IIf([JobInfoT].[JobTypeID] = 8, 1, Null)) AS CountReWork, Count(IIf([JobInfoT].[JobTypeID] = 7,1, Null)) As CountShort

Not getting the figures mate.

This is what I have put.

SELECT JobInfoT.Scheduled, JobInfoT.JobTypeID, Sum(JobInfoT.Schedule_Mins) AS SumOfSchedule_Mins, Sum(JobInfoT.MachineShop_Mins) AS SumOfMachineShop_Mins, Sum(JobInfoT.AssemblyMins_Actual) AS SumOfAssemblyMins_Actual, Sum(JobInfoT.PaintShop_Mins) AS SumOfPaintShop_Mins, Sum(JobInfoT.Dispatch_Mins) AS SumOfDispatch_Mins, Sum(JobInfoT.Delivery_Mins) AS SumOfDelivery_Mins, Sum(JobInfoT.Install_Mins) AS SumOfInstall_Mins, Count(IIf([JobInfoT].[JobTypeID]=8,1,Null)) AS CountRW, Count(IIf([JobInfoT].[JobTypeID]=7,1,Null)) AS CountShort
FROM JobInfoT
WHERE (((JobInfoT.DateEntered) Between [Forms]![MainMenuF]![MainMenuSubform].[Form]![TxtStartDate] And [Forms]![MainMenuF]![MainMenuSubform].[Form]![TxtFinishDate]))
GROUP BY JobInfoT.Scheduled, JobInfoT.JobTypeID
HAVING (((JobInfoT.Scheduled)=True) AND ((JobInfoT.JobTypeID)=7 Or (JobInfoT.JobTypeID)=8));

thanks heaps.

 

[Pat Hartman]

To achieve what you want, you must seperate the Trues from the Falses:

You can also sum the True/False field and use Abs() to get a count of the True values

Abs(Sum(Assembly_Complete)) as Complete

HAVING (((JobInfoT.Scheduled)=True)) OR (((JobInfoT.Scheduled)=True)) OR (((JobInfoT.Scheduled)=True));

Why?