sum of the same field,many times,with different criteria,in the same query

[ih8code]

Hi there,

I have a table [maintable] that holds print records, with the following fields : id,userlogon,printername,pagecount,color(yes/no),duplex(yes/no),timestamp and papersize.

I want to create a query that will show the following:

Sum of pagecount where papersize like 'a4' and between 2 dates but FOR EACH USERLOGON.The dates will take them from 2 fields in a form so i will use this "WHERE (((maintable.Timestamp)>=[forms]![reportsform]![frmdate] And (maintable.Timestamp)<DateAdd("d",1,[Forms]![reportsform]![todate])))" and some more..

So i want the query to show,first the userlogon then a field to show sum of a4 then a4 color prints etc..(remember,i want the between 2 dates criteria to be global,to the whole query)

here are all of the criteria fields i want:

1) - papersize=A4
2) - papersize=A4 and Color=True
3) - papersize=A4 and Color=False
4) - papersize=A4 and Duplex=True
5) - papersize=A4 and Duplex=False
6) - papersize=A4 and Color=True and Duplex=True
7) - papersize=A4 and Color=True and Duplex=False
8) - papersize=A4 and Color=False and Duplex=True
9) - papersize=A4 and Color=False and Duplex=False

I wish i could do it by myself.I always try to find stuff on the internet so i don't have to ask others.But this one too complicated for me.

Thanks in advance

 

[ih8code]

Hi there,

I have a table [maintable] that holds print records, with the following fields : id,userlogon,printername,pagecount,color(yes/no),duplex(yes/no),timestamp and papersize.

I want to create a query that will show the following:

Sum of pagecount where papersize like 'a4' and between 2 dates but FOR EACH USERLOGON.The dates will take them from 2 fields in a form so i will use this "WHERE (((maintable.Timestamp)>=[forms]![reportsform]![frmdate] And (maintable.Timestamp)<DateAdd("d",1,[Forms]![reportsform]![todate])))" and some more..

So i want the query to show,first the userlogon then a field to show sum of a4 then a4 color prints etc..(remember,i want the between 2 dates criteria to be global,to the whole query)

here are all of the criteria fields i want:

1) - papersize=A4
2) - papersize=A4 and Color=True
3) - papersize=A4 and Color=False
4) - papersize=A4 and Duplex=True
5) - papersize=A4 and Duplex=False
6) - papersize=A4 and Color=True and Duplex=True
7) - papersize=A4 and Color=True and Duplex=False
8) - papersize=A4 and Color=False and Duplex=True
9) - papersize=A4 and Color=False and Duplex=False

I wish i could do it by myself.I always try to find stuff on the internet so i don't have to ask others.But this one too complicated for me.

Thanks in advance

I have managed to find the code for number 1 only

SELECT maintable.userlogon, Sum(maintable.PageCount) AS SumOfPageCount
FROM maintable
WHERE (((maintable.Timestamp)>=[forms]![reportsform]![frmdate] And (maintable.Timestamp)<DateAdd("d",1,[Forms]![reportsform]![todate])))
GROUP BY maintable.userlogon, PageCount Like 'A4';

 

[namliam]

GROUP BY maintable.userlogon, PageCount Like 'A4';

What??? that wont do what you are looking for, I dont think

How exactly do you envision your output? IMHO best way would be a pivot in Excel.

If you want it from the query I suggest using IIF
something along the lines of

SELECT maintable.userlogon
, IIF(Pagesize = "A4", maintable.PageCount,0)  AS SumOfA4
, IIF(Pagesize = "A4" and color = True, maintable.PageCount,0)  AS SumOfA4Color
, IIF(Pagesize = "A4" and color = False, maintable.PageCount,0)  AS SumOfA4BW
...etc...

 

[ih8code]

GROUP BY maintable.userlogon, PageCount Like 'A4';

What??? that wont do what you are looking for, I dont think

How exactly do you envision your output? IMHO best way would be a pivot in Excel.

If you want it from the query I suggest using IIF
something along the lines of

SELECT maintable.userlogon
, IIF(Pagesize = "A4", maintable.PageCount,0)  AS SumOfA4
, IIF(Pagesize = "A4" and color = True, maintable.PageCount,0)  AS SumOfA4Color
, IIF(Pagesize = "A4" and color = False, maintable.PageCount,0)  AS SumOfA4BW
...etc...

thanks for the reply.
I wrote this

SELECT maintable.userlogon
, IIF(Papersize = "A4", maintable.PageCount,0) AS SumOfA4
, IIF(Papersize = "A4" and color = True, maintable.PageCount,0) AS SumOfA4Color
, IIF(Papersize = "A4" and color = False, maintable.PageCount,0) AS SumOfA4BW
from maintable
WHERE (((maintable.Timestamp)>=[forms]![reportsform]![frmdate] And (maintable.Timestamp)<DateAdd("d",1,[Forms]![reportsform]![todate])))
GROUP BY maintable.userlogon;

but i got this error: You tried to execute a query that does not include the
specified expression 'IIF(Papersize="A4",maintable.pagecount,0)' as part of an aggregate function.

i think i need to include "SumOfA4" in the group by but i dont know how.

 

[namliam]

Just me omitting a small detail
Not exactly feeling even 80% today

, SUM(IIF(Pagesize = "A4", maintable.PageCount,0) ) AS SumOfA4

Need to add the sum and bracket

 

[ih8code]

Just me omitting a small detail
Not exactly feeling even 80% today

, SUM(IIF(Pagesize = "A4", maintable.PageCount,0) ) AS SumOfA4

Need to add the sum and bracket

It works!!!You're just awesome
Thank you very much.