If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
grouping for report
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 |
#2
|
|||
|
|||
grouping for report
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 |
#3
|
|||
|
|||
grouping for report
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" wrote: 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 |
#4
|
|||
|
|||
grouping for report
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 "John Spencer" wrote: 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 |
#5
|
|||
|
|||
grouping for report
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" wrote: 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 "John Spencer" wrote: 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 |
#6
|
|||
|
|||
grouping for report
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 reportyemi wrote: 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" wrote: 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 "John Spencer" wrote: 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 |
#7
|
|||
|
|||
grouping for report
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" wrote: 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 reportyemi wrote: 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" wrote: 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 "John Spencer" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|