adding result together

R

reportyemi

I want to find out the number of total number of all the types of deliveries
of babies for the preceeding month. In my sql, i got the result i wanted but
rather than getting a result like

vaginal delivery 2
c/s 5
forceps 3

i get

vag delivery 1
vag delivery 1
c/s 1
c/s 1
c/s 1
c/s 1
c/s 1

forceps 1
forceps 1
forceps 1

SELECT tblinfantone.delivery_method, Count(tblinfantone.delivery_method) AS
CountOfdelivery_method
FROM tblinfantone
GROUP BY tblinfantone.delivery_method, tblinfantone.Date_of_Birth
HAVING
(((Year([tblinfantone]![Date_of_Birth])*12+DatePart("m",[tblinfantone]![Date_of_Birth]))=Year(Date())*12+DatePart("m",Date())-1));

please help me

yemi
 
D

Dirk Goldgar

reportyemi said:
I want to find out the number of total number of all the types of
deliveries
of babies for the preceeding month. In my sql, i got the result i wanted
but
rather than getting a result like

vaginal delivery 2
c/s 5
forceps 3

i get

vag delivery 1
vag delivery 1
c/s 1
c/s 1
c/s 1
c/s 1
c/s 1

forceps 1
forceps 1
forceps 1

SELECT tblinfantone.delivery_method, Count(tblinfantone.delivery_method)
AS
CountOfdelivery_method
FROM tblinfantone
GROUP BY tblinfantone.delivery_method, tblinfantone.Date_of_Birth
HAVING
(((Year([tblinfantone]![Date_of_Birth])*12+DatePart("m",[tblinfantone]![Date_of_Birth]))=Year(Date())*12+DatePart("m",Date())-1));
Click to expand...


You need to take the Date_of_Birth field out of the GROUP BY clause, just
group by the delivery method, and change the HAVING clause to a WHERE
clause:

SELECT
tblinfantone.delivery_method,
Count(tblinfantone.delivery_method) AS CountOfdelivery_method
FROM tblinfantone
WHERE
(((Year([tblinfantone]![Date_of_Birth])*12+DatePart("m",[tblinfantone]![Date_of_Birth]))=Year(Date())*12+DatePart("m",Date())-1))
GROUP BY tblinfantone.delivery_method

I'm not sure what your WHERE/HAVING criterion is trying to do, though. It's
possible it could be done simpler.
 
R

reportyemi

Dirk,

Thank you. It worked. The question i have and i posted this previously is
how can i do this for all the fields and not one by one as i am doing it. In
other words can i get the total for all the fields for the preceeding month
That is
under delivereis- totla no of vaginal ,c/s and forceps for the preceding month
conditions - total number of alive babies, stillbirth babies, misccariage
babies
total number of male and female babies.

I have all of these but i have had to do querries for each field

Thanks

Dirk Goldgar said:
reportyemi said:
I want to find out the number of total number of all the types of
deliveries
of babies for the preceeding month. In my sql, i got the result i wanted
but
rather than getting a result like

vaginal delivery 2
c/s 5
forceps 3

i get

vag delivery 1
vag delivery 1
c/s 1
c/s 1
c/s 1
c/s 1
c/s 1

forceps 1
forceps 1
forceps 1

SELECT tblinfantone.delivery_method, Count(tblinfantone.delivery_method)
AS
CountOfdelivery_method
FROM tblinfantone
GROUP BY tblinfantone.delivery_method, tblinfantone.Date_of_Birth
HAVING
(((Year([tblinfantone]![Date_of_Birth])*12+DatePart("m",[tblinfantone]![Date_of_Birth]))=Year(Date())*12+DatePart("m",Date())-1));
Click to expand...


You need to take the Date_of_Birth field out of the GROUP BY clause, just
group by the delivery method, and change the HAVING clause to a WHERE
clause:

SELECT
tblinfantone.delivery_method,
Count(tblinfantone.delivery_method) AS CountOfdelivery_method
FROM tblinfantone
WHERE
(((Year([tblinfantone]![Date_of_Birth])*12+DatePart("m",[tblinfantone]![Date_of_Birth]))=Year(Date())*12+DatePart("m",Date())-1))
GROUP BY tblinfantone.delivery_method

I'm not sure what your WHERE/HAVING criterion is trying to do, though. It's
possible it could be done simpler.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
J

John Spencer

You can use a union query to get many values at once. The query you have
could be rewritten as

SELECT "DeliveryMethod" as DataType
,tblinfantone.delivery_method
, Count(tblinfantone.delivery_method) AS
CountOfdelivery_method
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date())+1,0)
GROUP BY "DeliveryMethod" , tblinfantone.delivery_method

Now to get the Male and Female Count you could use
SELECT "Gender" as DataType
, tblInfantOne.Gender
, Count(Gender) as CountGender
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date())+1,0)
GROUP BY "Gender", tblinfantone.Gender

In a union query you would have something like the following (obviously I have
made up names for your fields):

SELECT "DeliveryMethod" as DataType
, tblinfantone.delivery_method as DataCategory
, 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())+1,0)
GROUP BY "DeliveryMethod" , tblinfantone.delivery_method
UNION ALL
SELECT "Gender" as DataType
, tblInfantOne.Gender as DataCategory
, Count(Gender) AS theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date())+1,0)
GROUP BY "Gender", tblinfantone.Gender
UNION ALL
SELECT "BirthStatus" as DataType
, tblInfantOne.BirthStatus as DataCategory
, Count(BirthStatus) AS theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date())+1,0)
GROUP BY "BirthStatus", tblinfantone.BirthStatus

Union queries can only be built in design view.

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

Thank you. It worked. The question i have and i posted this previously is
how can i do this for all the fields and not one by one as i am doing it. In
other words can i get the total for all the fields for the preceeding month
That is
under delivereis- totla no of vaginal ,c/s and forceps for the preceding month
conditions - total number of alive babies, stillbirth babies, misccariage
babies
total number of male and female babies.

I have all of these but i have had to do querries for each field

Thanks

Dirk Goldgar said:
reportyemi said:
I want to find out the number of total number of all the types of
deliveries
of babies for the preceeding month. In my sql, i got the result i wanted
but
rather than getting a result like

vaginal delivery 2
c/s 5
forceps 3

i get

vag delivery 1
vag delivery 1
c/s 1
c/s 1
c/s 1
c/s 1
c/s 1

forceps 1
forceps 1
forceps 1

SELECT tblinfantone.delivery_method, Count(tblinfantone.delivery_method)
AS
CountOfdelivery_method
FROM tblinfantone
GROUP BY tblinfantone.delivery_method, tblinfantone.Date_of_Birth
HAVING
(((Year([tblinfantone]![Date_of_Birth])*12+DatePart("m",[tblinfantone]![Date_of_Birth]))=Year(Date())*12+DatePart("m",Date())-1));
Click to expand...

You need to take the Date_of_Birth field out of the GROUP BY clause, just
group by the delivery method, and change the HAVING clause to a WHERE
clause:

SELECT
tblinfantone.delivery_method,
Count(tblinfantone.delivery_method) AS CountOfdelivery_method
FROM tblinfantone
WHERE
(((Year([tblinfantone]![Date_of_Birth])*12+DatePart("m",[tblinfantone]![Date_of_Birth]))=Year(Date())*12+DatePart("m",Date())-1))
GROUP BY tblinfantone.delivery_method

I'm not sure what your WHERE/HAVING criterion is trying to do, though. It's
possible it could be done simpler.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
R

reportyemi

John,

EXCELLENT news. It is working . The result gives me all the field totals
from the whole database. I will paste it below. i looked at the dateserial
and i thought it should only give me results for the previous month only but
instead it gives me result for the whole database. What adjustment do i make
to get previous month only results? Thank you so much

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())+1,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())+1,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())+1,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())+1,0)
GROUP BY tblinfantone.Number_of_babies;


yemi


John Spencer said:
You can use a union query to get many values at once. The query you have
could be rewritten as

SELECT "DeliveryMethod" as DataType
,tblinfantone.delivery_method
, Count(tblinfantone.delivery_method) AS
CountOfdelivery_method
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date())+1,0)
GROUP BY "DeliveryMethod" , tblinfantone.delivery_method

Now to get the Male and Female Count you could use
SELECT "Gender" as DataType
, tblInfantOne.Gender
, Count(Gender) as CountGender
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date())+1,0)
GROUP BY "Gender", tblinfantone.Gender

In a union query you would have something like the following (obviously I have
made up names for your fields):

SELECT "DeliveryMethod" as DataType
, tblinfantone.delivery_method as DataCategory
, 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())+1,0)
GROUP BY "DeliveryMethod" , tblinfantone.delivery_method
UNION ALL
SELECT "Gender" as DataType
, tblInfantOne.Gender as DataCategory
, Count(Gender) AS theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date())+1,0)
GROUP BY "Gender", tblinfantone.Gender
UNION ALL
SELECT "BirthStatus" as DataType
, tblInfantOne.BirthStatus as DataCategory
, Count(BirthStatus) AS theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date())+1,0)
GROUP BY "BirthStatus", tblinfantone.BirthStatus

Union queries can only be built in design view.

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

Thank you. It worked. The question i have and i posted this previously is
how can i do this for all the fields and not one by one as i am doing it. In
other words can i get the total for all the fields for the preceeding month
That is
under delivereis- totla no of vaginal ,c/s and forceps for the preceding month
conditions - total number of alive babies, stillbirth babies, misccariage
babies
total number of male and female babies.

I have all of these but i have had to do querries for each field

Thanks

Dirk Goldgar said:
I want to find out the number of total number of all the types of
deliveries
of babies for the preceeding month. In my sql, i got the result i wanted
but
rather than getting a result like

vaginal delivery 2
c/s 5
forceps 3

i get

vag delivery 1
vag delivery 1
c/s 1
c/s 1
c/s 1
c/s 1
c/s 1

forceps 1
forceps 1
forceps 1

SELECT tblinfantone.delivery_method, Count(tblinfantone.delivery_method)
AS
CountOfdelivery_method
FROM tblinfantone
GROUP BY tblinfantone.delivery_method, tblinfantone.Date_of_Birth
HAVING
(((Year([tblinfantone]![Date_of_Birth])*12+DatePart("m",[tblinfantone]![Date_of_Birth]))=Year(Date())*12+DatePart("m",Date())-1));

You need to take the Date_of_Birth field out of the GROUP BY clause, just
group by the delivery method, and change the HAVING clause to a WHERE
clause:

SELECT
tblinfantone.delivery_method,
Count(tblinfantone.delivery_method) AS CountOfdelivery_method
FROM tblinfantone
WHERE
(((Year([tblinfantone]![Date_of_Birth])*12+DatePart("m",[tblinfantone]![Date_of_Birth]))=Year(Date())*12+DatePart("m",Date())-1))
GROUP BY tblinfantone.delivery_method

I'm not sure what your WHERE/HAVING criterion is trying to do, though. It's
possible it could be done simpler.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

My error, the where clause should read as follows to get the previous month.

ALSO, It expect Date_of_Birth to be a datetime field. If it is a text field
that contains a date string then you will need to modify the where clause
further if you cannot change the field type.

WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)

If Date_of_Birth is a STRING containing DOB information then try this
modification.

WHERE IIF(IsDate(Date_of_Birth),CDate(Date_of_Birth),Null)
Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)

If the string is not in mm/dd/yyyy or yyyy/mm/dd format and is being handled
incorrectly, post back for further explanation on how to convert dd/mm/yyyy
format to a date that will be handled correctly.

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

EXCELLENT news. It is working . The result gives me all the field totals
from the whole database. I will paste it below. i looked at the dateserial
and i thought it should only give me results for the previous month only but
instead it gives me result for the whole database. What adjustment do i make
to get previous month only results? Thank you so much

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())+1,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())+1,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())+1,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())+1,0)
GROUP BY tblinfantone.Number_of_babies;


yemi


John Spencer said:
You can use a union query to get many values at once. The query you have
could be rewritten as

SELECT "DeliveryMethod" as DataType
,tblinfantone.delivery_method
, Count(tblinfantone.delivery_method) AS
CountOfdelivery_method
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date())+1,0)
GROUP BY "DeliveryMethod" , tblinfantone.delivery_method

Now to get the Male and Female Count you could use
SELECT "Gender" as DataType
, tblInfantOne.Gender
, Count(Gender) as CountGender
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date())+1,0)
GROUP BY "Gender", tblinfantone.Gender

In a union query you would have something like the following (obviously I have
made up names for your fields):

SELECT "DeliveryMethod" as DataType
, tblinfantone.delivery_method as DataCategory
, 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())+1,0)
GROUP BY "DeliveryMethod" , tblinfantone.delivery_method
UNION ALL
SELECT "Gender" as DataType
, tblInfantOne.Gender as DataCategory
, Count(Gender) AS theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date())+1,0)
GROUP BY "Gender", tblinfantone.Gender
UNION ALL
SELECT "BirthStatus" as DataType
, tblInfantOne.BirthStatus as DataCategory
, Count(BirthStatus) AS theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date())+1,0)
GROUP BY "BirthStatus", tblinfantone.BirthStatus

Union queries can only be built in design view.

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

Thank you. It worked. The question i have and i posted this previously is
how can i do this for all the fields and not one by one as i am doing it. In
other words can i get the total for all the fields for the preceeding month
That is
under delivereis- totla no of vaginal ,c/s and forceps for the preceding month
conditions - total number of alive babies, stillbirth babies, misccariage
babies
total number of male and female babies.

I have all of these but i have had to do querries for each field

Thanks

:

I want to find out the number of total number of all the types of
deliveries
of babies for the preceeding month. In my sql, i got the result i wanted
but
rather than getting a result like

vaginal delivery 2
c/s 5
forceps 3

i get

vag delivery 1
vag delivery 1
c/s 1
c/s 1
c/s 1
c/s 1
c/s 1

forceps 1
forceps 1
forceps 1

SELECT tblinfantone.delivery_method, Count(tblinfantone.delivery_method)
AS
CountOfdelivery_method
FROM tblinfantone
GROUP BY tblinfantone.delivery_method, tblinfantone.Date_of_Birth
HAVING
(((Year([tblinfantone]![Date_of_Birth])*12+DatePart("m",[tblinfantone]![Date_of_Birth]))=Year(Date())*12+DatePart("m",Date())-1));
You need to take the Date_of_Birth field out of the GROUP BY clause, just
group by the delivery method, and change the HAVING clause to a WHERE
clause:

SELECT
tblinfantone.delivery_method,
Count(tblinfantone.delivery_method) AS CountOfdelivery_method
FROM tblinfantone
WHERE
(((Year([tblinfantone]![Date_of_Birth])*12+DatePart("m",[tblinfantone]![Date_of_Birth]))=Year(Date())*12+DatePart("m",Date())-1))
GROUP BY tblinfantone.delivery_method

I'm not sure what your WHERE/HAVING criterion is trying to do, though. It's
possible it could be done simpler.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
Click to expand...
 
R

reportyemi

john, It woked!!!!! I am very grateful

Thank you

yemi

John Spencer said:
My error, the where clause should read as follows to get the previous month.

ALSO, It expect Date_of_Birth to be a datetime field. If it is a text field
that contains a date string then you will need to modify the where clause
further if you cannot change the field type.

WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)

If Date_of_Birth is a STRING containing DOB information then try this
modification.

WHERE IIF(IsDate(Date_of_Birth),CDate(Date_of_Birth),Null)
Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)

If the string is not in mm/dd/yyyy or yyyy/mm/dd format and is being handled
incorrectly, post back for further explanation on how to convert dd/mm/yyyy
format to a date that will be handled correctly.

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

EXCELLENT news. It is working . The result gives me all the field totals
from the whole database. I will paste it below. i looked at the dateserial
and i thought it should only give me results for the previous month only but
instead it gives me result for the whole database. What adjustment do i make
to get previous month only results? Thank you so much

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())+1,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())+1,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())+1,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())+1,0)
GROUP BY tblinfantone.Number_of_babies;


yemi


John Spencer said:
You can use a union query to get many values at once. The query you have
could be rewritten as

SELECT "DeliveryMethod" as DataType
,tblinfantone.delivery_method
, Count(tblinfantone.delivery_method) AS
CountOfdelivery_method
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date())+1,0)
GROUP BY "DeliveryMethod" , tblinfantone.delivery_method

Now to get the Male and Female Count you could use
SELECT "Gender" as DataType
, tblInfantOne.Gender
, Count(Gender) as CountGender
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date())+1,0)
GROUP BY "Gender", tblinfantone.Gender

In a union query you would have something like the following (obviously I have
made up names for your fields):

SELECT "DeliveryMethod" as DataType
, tblinfantone.delivery_method as DataCategory
, 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())+1,0)
GROUP BY "DeliveryMethod" , tblinfantone.delivery_method
UNION ALL
SELECT "Gender" as DataType
, tblInfantOne.Gender as DataCategory
, Count(Gender) AS theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date())+1,0)
GROUP BY "Gender", tblinfantone.Gender
UNION ALL
SELECT "BirthStatus" as DataType
, tblInfantOne.BirthStatus as DataCategory
, Count(BirthStatus) AS theCount
FROM tblinfantone
WHERE Date_of_Birth Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date())+1,0)
GROUP BY "BirthStatus", tblinfantone.BirthStatus

Union queries can only be built in design view.

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

reportyemi wrote:
Dirk,

Thank you. It worked. The question i have and i posted this previously is
how can i do this for all the fields and not one by one as i am doing it. In
other words can i get the total for all the fields for the preceeding month
That is
under delivereis- totla no of vaginal ,c/s and forceps for the preceding month
conditions - total number of alive babies, stillbirth babies, misccariage
babies
total number of male and female babies.

I have all of these but i have had to do querries for each field

Thanks

:

I want to find out the number of total number of all the types of
deliveries
of babies for the preceeding month. In my sql, i got the result i wanted
but
rather than getting a result like

vaginal delivery 2
c/s 5
forceps 3

i get

vag delivery 1
vag delivery 1
c/s 1
c/s 1
c/s 1
c/s 1
c/s 1

forceps 1
forceps 1
forceps 1

SELECT tblinfantone.delivery_method, Count(tblinfantone.delivery_method)
AS
CountOfdelivery_method
FROM tblinfantone
GROUP BY tblinfantone.delivery_method, tblinfantone.Date_of_Birth
HAVING
(((Year([tblinfantone]![Date_of_Birth])*12+DatePart("m",[tblinfantone]![Date_of_Birth]))=Year(Date())*12+DatePart("m",Date())-1));
You need to take the Date_of_Birth field out of the GROUP BY clause, just
group by the delivery method, and change the HAVING clause to a WHERE
clause:

SELECT
tblinfantone.delivery_method,
Count(tblinfantone.delivery_method) AS CountOfdelivery_method
FROM tblinfantone
WHERE
(((Year([tblinfantone]![Date_of_Birth])*12+DatePart("m",[tblinfantone]![Date_of_Birth]))=Year(Date())*12+DatePart("m",Date())-1))
GROUP BY tblinfantone.delivery_method

I'm not sure what your WHERE/HAVING criterion is trying to do, though. It's
possible it could be done simpler.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

grouping for report 6
querry sql problem for previous month 4
PivotTable 1
Get date for start of week 3
Help counting multiple Criteria 6
#Name? error - Question on Dates 6
type mismatch error in macro in excel 0
Invoice number 7

Top