Prev: Access 2010 and Sharepoint 2010 (any public sites out there)
Next: Insert/create new records using a loop
From: remigio on 22 Jul 2010 04:54 Hi, I'm trying to sum two sub-queries with this code: SELECT Count(*) AS Attivi FROM adi WHERE (((adi.pratica_2010) Is Not Null) And ((adi.stato_pratica) Is Null) And ((adi.data_attivazione)<=Forms!interrogazioni_ADI!fine)) + select count(*) from adi where ((stato_pratica is not null) and (data_variazione <=[Forms]![interrogazioni_ADI]![fine] ) and (data_variazione >=[Forms]![interrogazioni_ADI]![inizio])); but I don't know the right sintax. Can you help me, please? Thanks
From: Douglas J. Steele on 22 Jul 2010 06:40 Rather than using Count, try using an IIf statement that returns 1 if the condition is met and 0 if it isn't, and Sum that calculated field: SELECT Sum(IIf(IsNull([practica_2010]) = False AND IsNull([stato_practica]) = True AND [data_attivazione]<=Forms!interrogazioni_ADI!fine), 1, 0) AS Attivi, Sum(IsNull([stato_practica]) = False AND ([data_variazione] BETWEEN [Forms]![interrogazioni_ADI]![inizio] AND [Forms]![interrogazioni_ADI]![fine]), 1, 0) FROM adi -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele Co-author: Access 2010 Solutions, published by Wiley (no e-mails, please!) "remigio" <linoreale(a)gmail.com> wrote in message news:bdcdf756-4f53-4e60-97c5-06e33060e2fc(a)e5g2000yqn.googlegroups.com... > Hi, > I'm trying to sum two sub-queries with this code: > > SELECT Count(*) AS Attivi > FROM adi > WHERE (((adi.pratica_2010) Is Not Null) And ((adi.stato_pratica) Is > Null) And ((adi.data_attivazione)<=Forms!interrogazioni_ADI!fine)) > + > select count(*) from adi where ((stato_pratica is not null) and > (data_variazione <=[Forms]![interrogazioni_ADI]![fine] ) and > (data_variazione >=[Forms]![interrogazioni_ADI]![inizio])); > > but I don't know the right sintax. > Can you help me, please? > Thanks
From: remigio on 22 Jul 2010 09:42 On 22 Lug, 12:40, "Douglas J. Steele" <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote: > Rather than using Count, try using an IIf statement that returns 1 if the > condition is met and 0 if it isn't, and Sum that calculated field: > > SELECT Sum(IIf(IsNull([practica_2010]) = False AND IsNull([stato_practica]) > = True AND [data_attivazione]<=Forms!interrogazioni_ADI!fine), 1, 0) AS > Attivi, Sum(IsNull([stato_practica]) = False AND ([data_variazione] BETWEEN > [Forms]![interrogazioni_ADI]![inizio] AND > [Forms]![interrogazioni_ADI]![fine]), 1, 0) > FROM adi > > -- > Doug Steele, Microsoft Access MVPhttp://www.AccessMVP.com/DJSteele > Co-author: Access 2010 Solutions, published by Wiley > (no e-mails, please!) > > "remigio" <linore...(a)gmail.com> wrote in message > > news:bdcdf756-4f53-4e60-97c5-06e33060e2fc(a)e5g2000yqn.googlegroups.com... > > > Hi, > > I'm trying to sum two sub-queries with this code: > > > SELECT Count(*) AS Attivi > > FROM adi > > WHERE (((adi.pratica_2010) Is Not Null) And ((adi.stato_pratica) Is > > Null) And ((adi.data_attivazione)<=Forms!interrogazioni_ADI!fine)) > > + > > select count(*) from adi where ((stato_pratica is not null) and > > (data_variazione <=[Forms]![interrogazioni_ADI]![fine] ) and > > (data_variazione >=[Forms]![interrogazioni_ADI]![inizio])); > > > but I don't know the right sintax. > > Can you help me, please? > > Thanks This code gets an error: "wrong number of arguments used with function in query" Is it possible to sum queries each other?
From: Douglas J. Steele on 22 Jul 2010 12:11 Sorry, my fault. SELECT Sum(IIf(IsNull([practica_2010]) = False AND IsNull([stato_practica]) = True AND [data_attivazione]<=Forms!interrogazioni_ADI!fine), 1, 0)) AS Attivi, Sum(IIf(IsNull([stato_practica]) = False AND ([data_variazione] BETWEEN [Forms]![interrogazioni_ADI]![inizio] AND [Forms]![interrogazioni_ADI]![fine])), 1, 0)) FROM adi Why run two queries when one will do? -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele Co-author: Access 2010 Solutions, published by Wiley (no e-mails, please!) "remigio" <linoreale(a)gmail.com> wrote in message news:533d3a18-5041-4d24-afcf-fb3deb78d1cb(a)k19g2000yqc.googlegroups.com... > On 22 Lug, 12:40, "Douglas J. Steele" > <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote: >> Rather than using Count, try using an IIf statement that returns 1 if the >> condition is met and 0 if it isn't, and Sum that calculated field: >> >> SELECT Sum(IIf(IsNull([practica_2010]) = False AND >> IsNull([stato_practica]) >> = True AND [data_attivazione]<=Forms!interrogazioni_ADI!fine), 1, 0) AS >> Attivi, Sum(IsNull([stato_practica]) = False AND ([data_variazione] >> BETWEEN >> [Forms]![interrogazioni_ADI]![inizio] AND >> [Forms]![interrogazioni_ADI]![fine]), 1, 0) >> FROM adi >> >> -- >> Doug Steele, Microsoft Access MVPhttp://www.AccessMVP.com/DJSteele >> Co-author: Access 2010 Solutions, published by Wiley >> (no e-mails, please!) >> >> "remigio" <linore...(a)gmail.com> wrote in message >> >> news:bdcdf756-4f53-4e60-97c5-06e33060e2fc(a)e5g2000yqn.googlegroups.com... >> >> > Hi, >> > I'm trying to sum two sub-queries with this code: >> >> > SELECT Count(*) AS Attivi >> > FROM adi >> > WHERE (((adi.pratica_2010) Is Not Null) And ((adi.stato_pratica) Is >> > Null) And ((adi.data_attivazione)<=Forms!interrogazioni_ADI!fine)) >> > + >> > select count(*) from adi where ((stato_pratica is not null) and >> > (data_variazione <=[Forms]![interrogazioni_ADI]![fine] ) and >> > (data_variazione >=[Forms]![interrogazioni_ADI]![inizio])); >> >> > but I don't know the right sintax. >> > Can you help me, please? >> > Thanks > > This code gets an error: > "wrong number of arguments used with function in query" > Is it possible to sum queries each other?
From: remigio on 24 Jul 2010 02:08
On 22 Lug, 18:11, "Douglas J. Steele" <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote: > Sorry, my fault. > > SELECT Sum(IIf(IsNull([practica_2010]) = False AND IsNull([stato_practica]) > = True AND [data_attivazione]<=Forms!interrogazioni_ADI!fine), 1, 0)) AS > Attivi, Sum(IIf(IsNull([stato_practica]) = False AND ([data_variazione] > BETWEEN > [Forms]![interrogazioni_ADI]![inizio] AND > [Forms]![interrogazioni_ADI]![fine])), 1, 0)) > FROM adi > > Why run two queries when one will do? > > -- > Doug Steele, Microsoft Access MVPhttp://www.AccessMVP.com/DJSteele > Co-author: Access 2010 Solutions, published by Wiley > (no e-mails, please!) > > "remigio" <linore...(a)gmail.com> wrote in message > > news:533d3a18-5041-4d24-afcf-fb3deb78d1cb(a)k19g2000yqc.googlegroups.com... > > > On 22 Lug, 12:40, "Douglas J. Steele" > > <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote: > >> Rather than using Count, try using an IIf statement that returns 1 if the > >> condition is met and 0 if it isn't, and Sum that calculated field: > > >> SELECT Sum(IIf(IsNull([practica_2010]) = False AND > >> IsNull([stato_practica]) > >> = True AND [data_attivazione]<=Forms!interrogazioni_ADI!fine), 1, 0) AS > >> Attivi, Sum(IsNull([stato_practica]) = False AND ([data_variazione] > >> BETWEEN > >> [Forms]![interrogazioni_ADI]![inizio] AND > >> [Forms]![interrogazioni_ADI]![fine]), 1, 0) > >> FROM adi > > >> -- > >> Doug Steele, Microsoft Access MVPhttp://www.AccessMVP.com/DJSteele > >> Co-author: Access 2010 Solutions, published by Wiley > >> (no e-mails, please!) > > >> "remigio" <linore...(a)gmail.com> wrote in message > > >>news:bdcdf756-4f53-4e60-97c5-06e33060e2fc(a)e5g2000yqn.googlegroups.com... > > >> > Hi, > >> > I'm trying to sum two sub-queries with this code: > > >> > SELECT Count(*) AS Attivi > >> > FROM adi > >> > WHERE (((adi.pratica_2010) Is Not Null) And ((adi.stato_pratica) Is > >> > Null) And ((adi.data_attivazione)<=Forms!interrogazioni_ADI!fine)) > >> > + > >> > select count(*) from adi where ((stato_pratica is not null) and > >> > (data_variazione <=[Forms]![interrogazioni_ADI]![fine] ) and > >> > (data_variazione >=[Forms]![interrogazioni_ADI]![inizio])); > > >> > but I don't know the right sintax. > >> > Can you help me, please? > >> > Thanks > > > This code gets an error: > > "wrong number of arguments used with function in query" > > Is it possible to sum queries each other? Tnx! |