Display (in a report) the Total for like entries in a field

[Guest]

I have a field titled "Status". The status can be "ok", "R", "NCR", etc.
I would like to display columns in the summary report that have the quantity
of records (in that group) that have a status of "ok", "R", etc. I've set up
the report with the grouping by the model. I don't understand the DCOUNT
function. Please help.

e.g.
Model OK R NCR
A 1 2 43
B 33 39 44

 

[Guest]

Try this --
TRANSFORM Count(Your_Table.Status) AS CountOfStatus
SELECT Your_Table.Model
FROM Your_Table
GROUP BY Your_Table.Model
PIVOT Your_Table.Status;

 

[Guest]

I know very little about Access. Where would I put all of the suggested "Try
this" below? Is there a way to just insert a text box with something similar
to "Avg([xx])"?

 

[Guest]

Glenda:

Yes, you can do it with an aggregate function in a control in the report,
but it needs a little mild trickery. To conditionally count rows depending
on the value of a field you do it by summing the values of an expression
which returns 1 or 0 depending on the value. The expression calls the IIf
function to return a 1 or 0 and then the Sum function to (not surprisingly)
sum them. Summing all the 1 and 0 values in effect counts all the 1s. So in
the group footer of your report put 3 text boxes side by side with the
following ControlSources:

=Sum(IIf([Status] = "OK",1,0))
=Sum(IIf([Status] = "R",1,0))
=Sum(IIf([Status] = "NCR",1,0))

This should give you the counts of each value in the group.

You will sometimes see recommendations to do it another way:

=Sum(Abs([Status] = "OK"))

or:

=Sum(([Status] = "OK")*-1)

This relies on the fact that in Access True is implemented as -1 and False
as 0, so by summing the absolute values of these (or the value * -1) you are
again summing all the 1s and 0s. Don't do it this way! It will work but its
bad programming practice to rely on the implementation in this way; its what
the head of a software company of my acquaintance once called "being unduly
chummy with the implementation".

Ken Sheridan
Stafford,

I know very little about Access. Where would I put all of the suggested "Try
this" below? Is there a way to just insert a text box with something similar
to "Avg([xx])"?
--
Glenda

Try this --
TRANSFORM Count(Your_Table.Status) AS CountOfStatus
SELECT Your_Table.Model
FROM Your_Table
GROUP BY Your_Table.Model
PIVOT Your_Table.Status;

 

[Guest]

WOW, I am so grateful. This is exactly what I was hoping for.
--
Glenda

Glenda:

Yes, you can do it with an aggregate function in a control in the report,
but it needs a little mild trickery. To conditionally count rows depending
on the value of a field you do it by summing the values of an expression
which returns 1 or 0 depending on the value. The expression calls the IIf
function to return a 1 or 0 and then the Sum function to (not surprisingly)
sum them. Summing all the 1 and 0 values in effect counts all the 1s. So in
the group footer of your report put 3 text boxes side by side with the
following ControlSources:

=Sum(IIf([Status] = "OK",1,0))
=Sum(IIf([Status] = "R",1,0))
=Sum(IIf([Status] = "NCR",1,0))

This should give you the counts of each value in the group.

You will sometimes see recommendations to do it another way:

=Sum(Abs([Status] = "OK"))

or:

=Sum(([Status] = "OK")*-1)

This relies on the fact that in Access True is implemented as -1 and False
as 0, so by summing the absolute values of these (or the value * -1) you are
again summing all the 1s and 0s. Don't do it this way! It will work but its
bad programming practice to rely on the implementation in this way; its what
the head of a software company of my acquaintance once called "being unduly
chummy with the implementation".

Ken Sheridan
Stafford,

I know very little about Access. Where would I put all of the suggested "Try
this" below? Is there a way to just insert a text box with something similar
to "Avg([xx])"?
--
Glenda

Try this --
TRANSFORM Count(Your_Table.Status) AS CountOfStatus
SELECT Your_Table.Model
FROM Your_Table
GROUP BY Your_Table.Model
PIVOT Your_Table.Status;


:

I have a field titled "Status". The status can be "ok", "R", "NCR", etc.
I would like to display columns in the summary report that have the quantity
of records (in that group) that have a status of "ok", "R", etc. I've set up
the report with the grouping by the model. I don't understand the DCOUNT
function. Please help.

e.g.
Model OK R NCR
A 1 2 43
B 33 39 44

 

[Guest]

Sorry spoke too soon...darn...

I need a total at each group. Is there a way to get the totals for each
group (in the footer area - where I'm totalling the average, getting the min
and max). The formula suggested is giving me a running total.

Thanks again.
--
Glenda

Glenda:

Yes, you can do it with an aggregate function in a control in the report,
but it needs a little mild trickery. To conditionally count rows depending
on the value of a field you do it by summing the values of an expression
which returns 1 or 0 depending on the value. The expression calls the IIf
function to return a 1 or 0 and then the Sum function to (not surprisingly)
sum them. Summing all the 1 and 0 values in effect counts all the 1s. So in
the group footer of your report put 3 text boxes side by side with the
following ControlSources:

=Sum(IIf([Status] = "OK",1,0))
=Sum(IIf([Status] = "R",1,0))
=Sum(IIf([Status] = "NCR",1,0))

This should give you the counts of each value in the group.

You will sometimes see recommendations to do it another way:

=Sum(Abs([Status] = "OK"))

or:

=Sum(([Status] = "OK")*-1)

This relies on the fact that in Access True is implemented as -1 and False
as 0, so by summing the absolute values of these (or the value * -1) you are
again summing all the 1s and 0s. Don't do it this way! It will work but its
bad programming practice to rely on the implementation in this way; its what
the head of a software company of my acquaintance once called "being unduly
chummy with the implementation".

Ken Sheridan
Stafford,

I know very little about Access. Where would I put all of the suggested "Try
this" below? Is there a way to just insert a text box with something similar
to "Avg([xx])"?
--
Glenda

Try this --
TRANSFORM Count(Your_Table.Status) AS CountOfStatus
SELECT Your_Table.Model
FROM Your_Table
GROUP BY Your_Table.Model
PIVOT Your_Table.Status;


:

I have a field titled "Status". The status can be "ok", "R", "NCR", etc.
I would like to display columns in the summary report that have the quantity
of records (in that group) that have a status of "ok", "R", etc. I've set up
the report with the grouping by the model. I don't understand the DCOUNT
function. Please help.

e.g.
Model OK R NCR
A 1 2 43
B 33 39 44

 

[Guest]

What I posted was for a separate query, not to be put into an existing one.
Use the new query as record source for a new report and add that new report
in the footer of your present report.

 

[Guest]

I'm not explaining myself very well. The report has +15 columns, and +20
rows. I would like the data as an additional column, next to the other data
about the group. Any way to do that?

 

[Guest]

I've never used the SQL View. With your help, I've found a new tool. THANKS,

 

[Guest]

Post an example of what you want it to look like and the SQL for what you now
have.

 

[Guest]

I want it to look like an Excel report;

Model Avgflow spec .... NCR OK P R
xx 2500 2600 1 3 4 1
uu 320 320 5
yy 400 450 5 55 3

The sql for the current query is:

SELECT MainTable.InputTime, MainTable.ElementPN, MainTable.ElementDesc,
MainTable.Serial, MainTable.FLOW, IIf([area] Like 0 Or [GFD_A] Like
0,0,([flow]/[Area])/[GFD_A]) AS [Elem%Eff], MainTable.REJ, MainTable.Status,
dbo_L_K_ELEM_TYPE1.MAX_GFD, dbo_L_K_ELEM_TYPE1.MIN_GFD,
dbo_L_K_ELEM_TYPE1.MIN_REJ, dbo_D_K_AIRTEST.GFD_A, dbo_D_K_AIRTEST.REJ_A,
dbo_L_K_ELEM_TYPE1.AREA
FROM (MainTable LEFT JOIN dbo_L_K_ELEM_TYPE1 ON MainTable.ElementDesc =
dbo_L_K_ELEM_TYPE1.[_DESC_]) LEFT JOIN dbo_D_K_AIRTEST ON MainTable.Serial =
dbo_D_K_AIRTEST.SERIAL_NO;

Thanks again for your help,

 

[Guest]

Is it possible for you to review the further questions below? I was so
close, it was disappointing to not get the 'correct' data.
--
Glenda

Glenda:

Yes, you can do it with an aggregate function in a control in the report,
but it needs a little mild trickery. To conditionally count rows depending
on the value of a field you do it by summing the values of an expression
which returns 1 or 0 depending on the value. The expression calls the IIf
function to return a 1 or 0 and then the Sum function to (not surprisingly)
sum them. Summing all the 1 and 0 values in effect counts all the 1s. So in
the group footer of your report put 3 text boxes side by side with the
following ControlSources:

=Sum(IIf([Status] = "OK",1,0))
=Sum(IIf([Status] = "R",1,0))
=Sum(IIf([Status] = "NCR",1,0))

This should give you the counts of each value in the group.

You will sometimes see recommendations to do it another way:

=Sum(Abs([Status] = "OK"))

or:

=Sum(([Status] = "OK")*-1)

This relies on the fact that in Access True is implemented as -1 and False
as 0, so by summing the absolute values of these (or the value * -1) you are
again summing all the 1s and 0s. Don't do it this way! It will work but its
bad programming practice to rely on the implementation in this way; its what
the head of a software company of my acquaintance once called "being unduly
chummy with the implementation".

Ken Sheridan
Stafford,

I know very little about Access. Where would I put all of the suggested "Try
this" below? Is there a way to just insert a text box with something similar
to "Avg([xx])"?
--
Glenda

Try this --
TRANSFORM Count(Your_Table.Status) AS CountOfStatus
SELECT Your_Table.Model
FROM Your_Table
GROUP BY Your_Table.Model
PIVOT Your_Table.Status;


:

I have a field titled "Status". The status can be "ok", "R", "NCR", etc.
I would like to display columns in the summary report that have the quantity
of records (in that group) that have a status of "ok", "R", etc. I've set up
the report with the grouping by the model. I don't understand the DCOUNT
function. Please help.

e.g.
Model OK R NCR
A 1 2 43
B 33 39 44

 

[Guest]

Ok, I built your tables and query but I need sample date to run it.

I want it to look like an Excel report;

Model Avgflow spec .... NCR OK P R
xx 2500 2600 1 3 4 1
uu 320 320 5
yy 400 450 5 55 3

The sql for the current query is:

SELECT MainTable.InputTime, MainTable.ElementPN, MainTable.ElementDesc,
MainTable.Serial, MainTable.FLOW, IIf([area] Like 0 Or [GFD_A] Like
0,0,([flow]/[Area])/[GFD_A]) AS [Elem%Eff], MainTable.REJ, MainTable.Status,
dbo_L_K_ELEM_TYPE1.MAX_GFD, dbo_L_K_ELEM_TYPE1.MIN_GFD,
dbo_L_K_ELEM_TYPE1.MIN_REJ, dbo_D_K_AIRTEST.GFD_A, dbo_D_K_AIRTEST.REJ_A,
dbo_L_K_ELEM_TYPE1.AREA
FROM (MainTable LEFT JOIN dbo_L_K_ELEM_TYPE1 ON MainTable.ElementDesc =
dbo_L_K_ELEM_TYPE1.[_DESC_]) LEFT JOIN dbo_D_K_AIRTEST ON MainTable.Serial =
dbo_D_K_AIRTEST.SERIAL_NO;

Thanks again for your help,


--
Glenda

Post an example of what you want it to look like and the SQL for what you now
have.