grouping for report

  • Thread starter Thread starter reportyemi
  • Start date Start date
R

reportyemi

I have a report in which i have obtained the sum of some fields i had
created. I was able to get what the sum is for previous month.

I now want to include the result from another field from table
tblmaternal. The field is called parity and has 0,1,2,3,4,5,>5 and i choose
one of these numbers in my form. What i want in my report is the total number
of "0" result and the total number of all the rest , that is anything greater
than "0"
my sql is below

SELECT tblinfantone.delivery_method, Count(tblinfantone.delivery_method) AS
theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)
GROUP BY tblinfantone.delivery_method
UNION ALL SELECT tblInfantOne.sex, Count(sex) AS theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)
GROUP BY tblinfantone.sex;
UNION ALL
SELECT tblInfantOne.Conditon_of_babies, Count(Conditon_of_babies) AS theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)
GROUP BY tblinfantone.Conditon_of_babies
UNION ALL SELECT tblInfantOne.Number_of_babies, Count(Number_of_babies) AS
theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)
GROUP BY tblinfantone.Number_of_babies;

thanks
 
The basic query would look like the following. Note that there is no
restriction by date range.

SELECT IIF(Parity="0","Zero","All Other"), Count(Parity) as The Count
FROM TblMaternal
GROUP BY IIF(Parity="0","Zero","All Other")

Is there a relationhip between tblMaternal and tblInfantOne? Assuming there
is you would need to add tblInfantOne into the above query. Something like
the following guess.

SELECT IIF(Parity="0","Zero","All Other"), Count(Parity) as The Count
FROM TblMaternal INNER JOIN tblInfantOne
ON tblMaternal.MaternalID = tblInfantOne.MaternalID
WHERE tblInfantOne.Date_of_Birth Between
DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)
GROUP BY IIF(Parity="0","Zero","All Other")

Once you have this query working your should be able to add it to your current
union query.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thank you . i was still having problem with the query reading the ID column
instead of the parity number. So i changed the type of data to text and
entered zero,one,two,three,four,five and >five for my parity.

I altered your formula and this is the sql i get back
SELECT IIf([Parity]="zero","All Other") AS Expr1, Count(TblMaternal.Parity)
AS TheCount
FROM TblMaternal
GROUP BY IIf([Parity]="zero","All Other");

The result is strange

i have the results reversed, that is, total for parity zero was 18 and all
other was 5 when it should be the other way. Also, it did not say zero in
the field . It left the zero field name blank but it did state the" All
other " name was present. I dont understand
 
You are missing part of the arguments to the IIF. There are 3 arguments -
first is a comparison that will return true or false, then the next is the
response if the comparison is true, and finally is the response if the
comparison is false.

You should be using
IIF([Parity]="ZERO","Zero","All Other")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thank you . i was still having problem with the query reading the ID column
instead of the parity number. So i changed the type of data to text and
entered zero,one,two,three,four,five and >five for my parity.

I altered your formula and this is the sql i get back
SELECT IIf([Parity]="zero","All Other") AS Expr1, Count(TblMaternal.Parity)
AS TheCount
FROM TblMaternal
GROUP BY IIf([Parity]="zero","All Other");

The result is strange

i have the results reversed, that is, total for parity zero was 18 and all
other was 5 when it should be the other way. Also, it did not say zero in
the field . It left the zero field name blank but it did state the" All
other " name was present. I dont understand



John Spencer said:
The basic query would look like the following. Note that there is no
restriction by date range.

SELECT IIF(Parity="0","Zero","All Other"), Count(Parity) as The Count
FROM TblMaternal
GROUP BY IIF(Parity="0","Zero","All Other")

Is there a relationhip between tblMaternal and tblInfantOne? Assuming there
is you would need to add tblInfantOne into the above query. Something like
the following guess.

SELECT IIF(Parity="0","Zero","All Other"), Count(Parity) as The Count
FROM TblMaternal INNER JOIN tblInfantOne
ON tblMaternal.MaternalID = tblInfantOne.MaternalID
WHERE tblInfantOne.Date_of_Birth Between
DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)
GROUP BY IIF(Parity="0","Zero","All Other")

Once you have this query working your should be able to add it to your current
union query.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
Click to expand...
 
Thank you so much John. I am learning. One more issue similar but somewhat
different
I have weight in a table (tblinfantone). I also want to summarize this into
two groups : total number of weights <2.5 and total number with weight
greater than 2.5

field is weight
table is tblinfantone

examples of weights are 1.5,2.3,4.5,2,3.3,2.1,2.9 and so on

Thanks again john

John Spencer said:
You are missing part of the arguments to the IIF. There are 3 arguments -
first is a comparison that will return true or false, then the next is the
response if the comparison is true, and finally is the response if the
comparison is false.

You should be using
IIF([Parity]="ZERO","Zero","All Other")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thank you . i was still having problem with the query reading the ID column
instead of the parity number. So i changed the type of data to text and
entered zero,one,two,three,four,five and >five for my parity.

I altered your formula and this is the sql i get back
SELECT IIf([Parity]="zero","All Other") AS Expr1, Count(TblMaternal.Parity)
AS TheCount
FROM TblMaternal
GROUP BY IIf([Parity]="zero","All Other");

The result is strange

i have the results reversed, that is, total for parity zero was 18 and all
other was 5 when it should be the other way. Also, it did not say zero in
the field . It left the zero field name blank but it did state the" All
other " name was present. I dont understand



John Spencer said:
The basic query would look like the following. Note that there is no
restriction by date range.

SELECT IIF(Parity="0","Zero","All Other"), Count(Parity) as The Count
FROM TblMaternal
GROUP BY IIF(Parity="0","Zero","All Other")

Is there a relationhip between tblMaternal and tblInfantOne? Assuming there
is you would need to add tblInfantOne into the above query. Something like
the following guess.

SELECT IIF(Parity="0","Zero","All Other"), Count(Parity) as The Count
FROM TblMaternal INNER JOIN tblInfantOne
ON tblMaternal.MaternalID = tblInfantOne.MaternalID
WHERE tblInfantOne.Date_of_Birth Between
DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)
GROUP BY IIF(Parity="0","Zero","All Other")

Once you have this query working your should be able to add it to your current
union query.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

reportyemi wrote:
I have a report in which i have obtained the sum of some fields i had
created. I was able to get what the sum is for previous month.

I now want to include the result from another field from table
tblmaternal. The field is called parity and has 0,1,2,3,4,5,>5 and i choose
one of these numbers in my form. What i want in my report is the total number
of "0" result and the total number of all the rest , that is anything greater
than "0"
my sql is below

SELECT tblinfantone.delivery_method, Count(tblinfantone.delivery_method) AS
theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)
GROUP BY tblinfantone.delivery_method
UNION ALL SELECT tblInfantOne.sex, Count(sex) AS theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)
GROUP BY tblinfantone.sex;
UNION ALL
SELECT tblInfantOne.Conditon_of_babies, Count(Conditon_of_babies) AS theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)
GROUP BY tblinfantone.Conditon_of_babies
UNION ALL SELECT tblInfantOne.Number_of_babies, Count(Number_of_babies) AS
theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)
GROUP BY tblinfantone.Number_of_babies;

thanks
Click to expand...
Click to expand...
Click to expand...
 
What type of field is weight? If it is numeric then the following expression
will give you two results.

SELECT IIF(Weight<=2.5,"LightWeight","HeavyWeight") As WeightType
, Count(Weight)
FROM tblInfantOne
GROUP BY IF(Weight<=2.5,"LightWeight","HeavyWeight")

If Weight is a text field then you will have to force a conversion to number
type, you can probably do that with the following expression.

IIF(Val(Weight & "")<=2.5,"LightWeight","HeavyWeight") As WeightType

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thank you so much John. I am learning. One more issue similar but somewhat
different
I have weight in a table (tblinfantone). I also want to summarize this into
two groups : total number of weights <2.5 and total number with weight
greater than 2.5

field is weight
table is tblinfantone

examples of weights are 1.5,2.3,4.5,2,3.3,2.1,2.9 and so on

Thanks again john

John Spencer said:
You are missing part of the arguments to the IIF. There are 3 arguments -
first is a comparison that will return true or false, then the next is the
response if the comparison is true, and finally is the response if the
comparison is false.

You should be using
IIF([Parity]="ZERO","Zero","All Other")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thank you . i was still having problem with the query reading the ID column
instead of the parity number. So i changed the type of data to text and
entered zero,one,two,three,four,five and >five for my parity.

I altered your formula and this is the sql i get back
SELECT IIf([Parity]="zero","All Other") AS Expr1, Count(TblMaternal.Parity)
AS TheCount
FROM TblMaternal
GROUP BY IIf([Parity]="zero","All Other");

The result is strange

i have the results reversed, that is, total for parity zero was 18 and all
other was 5 when it should be the other way. Also, it did not say zero in
the field . It left the zero field name blank but it did state the" All
other " name was present. I dont understand



:

The basic query would look like the following. Note that there is no
restriction by date range.

SELECT IIF(Parity="0","Zero","All Other"), Count(Parity) as The Count
FROM TblMaternal
GROUP BY IIF(Parity="0","Zero","All Other")

Is there a relationhip between tblMaternal and tblInfantOne? Assuming there
is you would need to add tblInfantOne into the above query. Something like
the following guess.

SELECT IIF(Parity="0","Zero","All Other"), Count(Parity) as The Count
FROM TblMaternal INNER JOIN tblInfantOne
ON tblMaternal.MaternalID = tblInfantOne.MaternalID
WHERE tblInfantOne.Date_of_Birth Between
DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)
GROUP BY IIF(Parity="0","Zero","All Other")

Once you have this query working your should be able to add it to your current
union query.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

reportyemi wrote:
I have a report in which i have obtained the sum of some fields i had
created. I was able to get what the sum is for previous month.

I now want to include the result from another field from table
tblmaternal. The field is called parity and has 0,1,2,3,4,5,>5 and i choose
one of these numbers in my form. What i want in my report is the total number
of "0" result and the total number of all the rest , that is anything greater
than "0"
my sql is below

SELECT tblinfantone.delivery_method, Count(tblinfantone.delivery_method) AS
theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)
GROUP BY tblinfantone.delivery_method
UNION ALL SELECT tblInfantOne.sex, Count(sex) AS theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)
GROUP BY tblinfantone.sex;
UNION ALL
SELECT tblInfantOne.Conditon_of_babies, Count(Conditon_of_babies) AS theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)
GROUP BY tblinfantone.Conditon_of_babies
UNION ALL SELECT tblInfantOne.Number_of_babies, Count(Number_of_babies) AS
theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)
GROUP BY tblinfantone.Number_of_babies;

thanks
Click to expand...
Click to expand...
Click to expand...
 
Jon,

finally got it. Made a few changes and it worked.This is my sql

SELECT IIf(Weight<=2.5,"LightWeight","HeavyWeight") AS WeightType,
Count(tblInfantOne.Weight) AS CountOfWeight
FROM tblInfantOne
WHERE
(((Year([tblinfantone]![Date_of_Birth])*12+DatePart("m",[tblinfantone]![Date_of_Birth]))=Year(Date())*12+DatePart("m",Date())-1))
GROUP BY IIf(Weight<=2.5,"LightWeight","HeavyWeight");

Thank you so much

John Spencer said:
What type of field is weight? If it is numeric then the following expression
will give you two results.

SELECT IIF(Weight<=2.5,"LightWeight","HeavyWeight") As WeightType
, Count(Weight)
FROM tblInfantOne
GROUP BY IF(Weight<=2.5,"LightWeight","HeavyWeight")

If Weight is a text field then you will have to force a conversion to number
type, you can probably do that with the following expression.

IIF(Val(Weight & "")<=2.5,"LightWeight","HeavyWeight") As WeightType

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thank you so much John. I am learning. One more issue similar but somewhat
different
I have weight in a table (tblinfantone). I also want to summarize this into
two groups : total number of weights <2.5 and total number with weight
greater than 2.5

field is weight
table is tblinfantone

examples of weights are 1.5,2.3,4.5,2,3.3,2.1,2.9 and so on

Thanks again john

John Spencer said:
You are missing part of the arguments to the IIF. There are 3 arguments -
first is a comparison that will return true or false, then the next is the
response if the comparison is true, and finally is the response if the
comparison is false.

You should be using
IIF([Parity]="ZERO","Zero","All Other")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

reportyemi wrote:
Thank you . i was still having problem with the query reading the ID column
instead of the parity number. So i changed the type of data to text and
entered zero,one,two,three,four,five and >five for my parity.

I altered your formula and this is the sql i get back
SELECT IIf([Parity]="zero","All Other") AS Expr1, Count(TblMaternal.Parity)
AS TheCount
FROM TblMaternal
GROUP BY IIf([Parity]="zero","All Other");

The result is strange

i have the results reversed, that is, total for parity zero was 18 and all
other was 5 when it should be the other way. Also, it did not say zero in
the field . It left the zero field name blank but it did state the" All
other " name was present. I dont understand



:

The basic query would look like the following. Note that there is no
restriction by date range.

SELECT IIF(Parity="0","Zero","All Other"), Count(Parity) as The Count
FROM TblMaternal
GROUP BY IIF(Parity="0","Zero","All Other")

Is there a relationhip between tblMaternal and tblInfantOne? Assuming there
is you would need to add tblInfantOne into the above query. Something like
the following guess.

SELECT IIF(Parity="0","Zero","All Other"), Count(Parity) as The Count
FROM TblMaternal INNER JOIN tblInfantOne
ON tblMaternal.MaternalID = tblInfantOne.MaternalID
WHERE tblInfantOne.Date_of_Birth Between
DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)
GROUP BY IIF(Parity="0","Zero","All Other")

Once you have this query working your should be able to add it to your current
union query.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

reportyemi wrote:
I have a report in which i have obtained the sum of some fields i had
created. I was able to get what the sum is for previous month.

I now want to include the result from another field from table
tblmaternal. The field is called parity and has 0,1,2,3,4,5,>5 and i choose
one of these numbers in my form. What i want in my report is the total number
of "0" result and the total number of all the rest , that is anything greater
than "0"
my sql is below

SELECT tblinfantone.delivery_method, Count(tblinfantone.delivery_method) AS
theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)
GROUP BY tblinfantone.delivery_method
UNION ALL SELECT tblInfantOne.sex, Count(sex) AS theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)
GROUP BY tblinfantone.sex;
UNION ALL
SELECT tblInfantOne.Conditon_of_babies, Count(Conditon_of_babies) AS theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)
GROUP BY tblinfantone.Conditon_of_babies
UNION ALL SELECT tblInfantOne.Number_of_babies, Count(Number_of_babies) AS
theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)
GROUP BY tblinfantone.Number_of_babies;

thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top