querry sql problem for previous month

R

reportyemi

I have a 2 querries totaldeliverytype and totalcondition. and wanted to find
out the previous months result of deliveries and condition of baby. I am able
to get the type of delivery result for last month using this sql but when i
try for the condition, i donot get anything. What is wrong with my code

SELECT totaldeliverytype.[Delivery Method], totaldeliverytype.[Date of
Birth], Count(totaldeliverytype.[Delivery Method]) AS [CountOfDelivery
Method1]
FROM tblInfant1 INNER JOIN totaldeliverytype ON tblInfant1.[Date of Birth] =
totaldeliverytype.[Date of Birth]
GROUP BY totaldeliverytype.[Delivery Method], totaldeliverytype.[Date of
Birth], Year([totaldeliverytype]![Date of
Birth])*12+DatePart("m",[totaldeliverytype]![Date of Birth])
HAVING (((Year([totaldeliverytype]![Date of
Birth])*12+DatePart("m",[totaldeliverytype]![Date of
Birth]))=(Year(Date())*12+DatePart("m",Date())-1)));

code for condtion is

SELECT totalcondition.[ Condition of Baby], totalcondition.[Date of Birth],
Count(totalcondition.[ Condition of Baby]) AS [CountOfCondition of Baby]
FROM tblInfant1 INNER JOIN totalcondition ON tblInfant1.[Date of Birth] =
totalcondition.[Date of Birth]
GROUP BY totalcondition.[ Condition of Baby], totalcondition.[Date of
Birth], Year([totalcondition]![Date of Birth])*12+DatePart("m",[
totalcondition]![Date of Birth])
HAVING (((Year([totalcondition]![Date of Birth])*12+DatePart("m",[
totalcondition]![Date of Birth]))=(Year(Date())*12+DatePart("m",Date())-1)));

i have tried everything to no avail

yemi
 
K

KARL DEWEY

Could it be the leading space in field name totalcondition.[ Condition of
Baby]?
 
R

reportyemi

Karl, YOU ARE THE MAN!! After 3 days work , you solved it in 5mins. It worked.

So, Can i ask another question related to all this? I am currently making a
querry for each field ( type of delivery, condition of baby, sex of baby,etc)
stating the field and the number of counts , then i make a new querry trying
to find out ONLY the prior months result. For 8 different fields, that is
too laborious doing total count in database for specific field querry then
doing prior month querry for each field. Cant i do a querry of last month's
result in one go for all my fields? So for the above example, could i not do
only one querry to get the total results of all the fields in the prior month?

KARL DEWEY said:
Could it be the leading space in field name totalcondition.[ Condition of
Baby]?

reportyemi said:
I have a 2 querries totaldeliverytype and totalcondition. and wanted to find
out the previous months result of deliveries and condition of baby. I am able
to get the type of delivery result for last month using this sql but when i
try for the condition, i donot get anything. What is wrong with my code

SELECT totaldeliverytype.[Delivery Method], totaldeliverytype.[Date of
Birth], Count(totaldeliverytype.[Delivery Method]) AS [CountOfDelivery
Method1]
FROM tblInfant1 INNER JOIN totaldeliverytype ON tblInfant1.[Date of Birth] =
totaldeliverytype.[Date of Birth]
GROUP BY totaldeliverytype.[Delivery Method], totaldeliverytype.[Date of
Birth], Year([totaldeliverytype]![Date of
Birth])*12+DatePart("m",[totaldeliverytype]![Date of Birth])
HAVING (((Year([totaldeliverytype]![Date of
Birth])*12+DatePart("m",[totaldeliverytype]![Date of
Birth]))=(Year(Date())*12+DatePart("m",Date())-1)));

code for condtion is

SELECT totalcondition.[ Condition of Baby], totalcondition.[Date of Birth],
Count(totalcondition.[ Condition of Baby]) AS [CountOfCondition of Baby]
FROM tblInfant1 INNER JOIN totalcondition ON tblInfant1.[Date of Birth] =
totalcondition.[Date of Birth]
GROUP BY totalcondition.[ Condition of Baby], totalcondition.[Date of
Birth], Year([totalcondition]![Date of Birth])*12+DatePart("m",[
totalcondition]![Date of Birth])
HAVING (((Year([totalcondition]![Date of Birth])*12+DatePart("m",[
totalcondition]![Date of Birth]))=(Year(Date())*12+DatePart("m",Date())-1)));

i have tried everything to no avail

yemi
Click to expand...
Click to expand...
 
K

KARL DEWEY

I do not follow as I don't know your data but it sounds like a crosstab query
should do you.
Post sample data and someone should be able to suggest a solution. Also an
example of how you would like the results to look.

reportyemi said:
Karl, YOU ARE THE MAN!! After 3 days work , you solved it in 5mins. It worked.

So, Can i ask another question related to all this? I am currently making a
querry for each field ( type of delivery, condition of baby, sex of baby,etc)
stating the field and the number of counts , then i make a new querry trying
to find out ONLY the prior months result. For 8 different fields, that is
too laborious doing total count in database for specific field querry then
doing prior month querry for each field. Cant i do a querry of last month's
result in one go for all my fields? So for the above example, could i not do
only one querry to get the total results of all the fields in the prior month?

KARL DEWEY said:
Could it be the leading space in field name totalcondition.[ Condition of
Baby]?

reportyemi said:
I have a 2 querries totaldeliverytype and totalcondition. and wanted to find
out the previous months result of deliveries and condition of baby. I am able
to get the type of delivery result for last month using this sql but when i
try for the condition, i donot get anything. What is wrong with my code

SELECT totaldeliverytype.[Delivery Method], totaldeliverytype.[Date of
Birth], Count(totaldeliverytype.[Delivery Method]) AS [CountOfDelivery
Method1]
FROM tblInfant1 INNER JOIN totaldeliverytype ON tblInfant1.[Date of Birth] =
totaldeliverytype.[Date of Birth]
GROUP BY totaldeliverytype.[Delivery Method], totaldeliverytype.[Date of
Birth], Year([totaldeliverytype]![Date of
Birth])*12+DatePart("m",[totaldeliverytype]![Date of Birth])
HAVING (((Year([totaldeliverytype]![Date of
Birth])*12+DatePart("m",[totaldeliverytype]![Date of
Birth]))=(Year(Date())*12+DatePart("m",Date())-1)));

code for condtion is

SELECT totalcondition.[ Condition of Baby], totalcondition.[Date of Birth],
Count(totalcondition.[ Condition of Baby]) AS [CountOfCondition of Baby]
FROM tblInfant1 INNER JOIN totalcondition ON tblInfant1.[Date of Birth] =
totalcondition.[Date of Birth]
GROUP BY totalcondition.[ Condition of Baby], totalcondition.[Date of
Birth], Year([totalcondition]![Date of Birth])*12+DatePart("m",[
totalcondition]![Date of Birth])
HAVING (((Year([totalcondition]![Date of Birth])*12+DatePart("m",[
totalcondition]![Date of Birth]))=(Year(Date())*12+DatePart("m",Date())-1)));

i have tried everything to no avail

yemi
Click to expand...
Click to expand...
Click to expand...
 
R

reportyemi

Sample data

date of birth condition number of baby mode of delivery sex
6/12/2009 alive single normal
male
7/14/2009 stillbirth twins cesarean
female
7/14/2009 stillbirth twins normal
female
2/12/2009 macerated single forceps
male
1/21/2009 alive triplets forceps
male
6/11/2009 stillbirth twin normal
female
6/30/2009 alive single cesarean
male


The above shows only 5 columns but there are more but i shouild understand
once i get the pattern of your response.

and how the result should look?


Month of june

condition total number total mode total
sex total

alive 2 single 2 normal
2 male 2
stillbirth 1 twin 1
cesarean 1 female 1
macerated 0 triplets 0 forceps 0

as you can see, only june results are shown. that is what i want to do.
Right now i am able to get these results but i have created a querry for each
of these data instead of creating one big all encompassing querry. Thanks for
future advice

yemi


KARL DEWEY said:
I do not follow as I don't know your data but it sounds like a crosstab query
should do you.
Post sample data and someone should be able to suggest a solution. Also an
example of how you would like the results to look.

reportyemi said:
Karl, YOU ARE THE MAN!! After 3 days work , you solved it in 5mins. It worked.

So, Can i ask another question related to all this? I am currently making a
querry for each field ( type of delivery, condition of baby, sex of baby,etc)
stating the field and the number of counts , then i make a new querry trying
to find out ONLY the prior months result. For 8 different fields, that is
too laborious doing total count in database for specific field querry then
doing prior month querry for each field. Cant i do a querry of last month's
result in one go for all my fields? So for the above example, could i not do
only one querry to get the total results of all the fields in the prior month?

KARL DEWEY said:
Could it be the leading space in field name totalcondition.[ Condition of
Baby]?

:

I have a 2 querries totaldeliverytype and totalcondition. and wanted to find
out the previous months result of deliveries and condition of baby. I am able
to get the type of delivery result for last month using this sql but when i
try for the condition, i donot get anything. What is wrong with my code

SELECT totaldeliverytype.[Delivery Method], totaldeliverytype.[Date of
Birth], Count(totaldeliverytype.[Delivery Method]) AS [CountOfDelivery
Method1]
FROM tblInfant1 INNER JOIN totaldeliverytype ON tblInfant1.[Date of Birth] =
totaldeliverytype.[Date of Birth]
GROUP BY totaldeliverytype.[Delivery Method], totaldeliverytype.[Date of
Birth], Year([totaldeliverytype]![Date of
Birth])*12+DatePart("m",[totaldeliverytype]![Date of Birth])
HAVING (((Year([totaldeliverytype]![Date of
Birth])*12+DatePart("m",[totaldeliverytype]![Date of
Birth]))=(Year(Date())*12+DatePart("m",Date())-1)));

code for condtion is

SELECT totalcondition.[ Condition of Baby], totalcondition.[Date of Birth],
Count(totalcondition.[ Condition of Baby]) AS [CountOfCondition of Baby]
FROM tblInfant1 INNER JOIN totalcondition ON tblInfant1.[Date of Birth] =
totalcondition.[Date of Birth]
GROUP BY totalcondition.[ Condition of Baby], totalcondition.[Date of
Birth], Year([totalcondition]![Date of Birth])*12+DatePart("m",[
totalcondition]![Date of Birth])
HAVING (((Year([totalcondition]![Date of Birth])*12+DatePart("m",[
totalcondition]![Date of Birth]))=(Year(Date())*12+DatePart("m",Date())-1)));

i have tried everything to no avail

yemi
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

adding result together 6
QueryHelp 4
DSUM, Running total problem 8
Get date for start of week 3
Cross tab - complex IIF 2
QueryHelp 5
HELP WITH QUERY BY DATE 2
Spelling or syntax problem with SQL 4

Top