sum of two queries

[remigio]

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

 

[George]

Hi remigio,

I would like to do it by inserting each of two queries into existing
empty table called "mytable" with one field called "mycount" and then

SELECT sum(mycount)
FROM mytable;

Please tell me does it work for you.

 

[John W. Vinson]

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

A Query may have 255 fields and ten million records. It's not a single digit
number, and you can't "add" a query to another query!

If you just want to display the count, and if there is no overlap between the
records returned by the two queries, you can just combine the two queries into
one using OR logic:

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))
OR
(data_variazione <=[Forms]![interrogazioni_ADI]![fine] ) and
(data_variazione >=[Forms]![interrogazioni_ADI]![inizio]));

If there are some records which will be returned by both sets of criteria, and
you want them counted as two records rather than one, you'll need a more
complex query - post back if that's the case.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[remigio]

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

A Query may have 255 fields and ten million records. It's not a single digit
number, and you can't "add" a query to another query!

If you just want to display the count, and if there is no overlap betweenthe
records returned by the two queries, you can just combine the two queriesinto
one using OR logic:

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))
OR
(data_variazione <=[Forms]![interrogazioni_ADI]![fine] ) and
(data_variazione >=[Forms]![interrogazioni_ADI]![inizio]));

If there are some records which will be returned by both sets of criteria, and
you want them counted as two records rather than one, you'll need a more
complex query - post back if that's the case.
--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com

Sorry for my bad English.
I'm simply trying to sum the result of two queries.

(code 1)
+
(code 2)

Is there a way to do it in only one Select code instruction?
I. e.:
Select (select.... from ...) + (select ... from ...)
Thank you very much.

Remigio

 

[John W. Vinson]

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

A Query may have 255 fields and ten million records. It's not a single digit
number, and you can't "add" a query to another query!

If you just want to display the count, and if there is no overlap between the
records returned by the two queries, you can just combine the two queries into
one using OR logic:

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))
OR
(data_variazione <=[Forms]![interrogazioni_ADI]![fine] ) and
(data_variazione >=[Forms]![interrogazioni_ADI]![inizio]));

If there are some records which will be returned by both sets of criteria, and
you want them counted as two records rather than one, you'll need a more
complex query - post back if that's the case.
--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com

Sorry for my bad English.
I'm simply trying to sum the result of two queries.

(code 1)
+
(code 2)

Is there a way to do it in only one Select code instruction?
I. e.:
Select (select.... from ...) + (select ... from ...)
Thank you very much.

Remigio

You're assuming that the "result" of a query is a number. IT ISN'T! The result
of a Query is a Recordset which can contain any number of fields, and any
number of records. This particular query happens to have only one record and
one field, which happens to be a number - but the Access SQL parser has no way
to know that.

I'd just use two DCounts:

=Dcount("*", "adi", "adi.pratica_2010 Is Not Nul) And adi.stato_pratica Is
Nul) And adi.data_attivazione<=[Forms]![interrogazioni_ADI]![fine]") +
DCount("*", "adi", "stato_pratica is not null and data_variazione
<=[Forms]![interrogazioni_ADI]![fine] ) and data_variazione

=[Forms]![interrogazioni_ADI]![inizio]")

If you want to use a totals query, I'd make it one query combining the
criteria, rather than trying to use two queries and combine them:

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)))
OR
(((stato_pratica is not null) and
(data_variazione <=[Forms]![interrogazioni_ADI]![fine] ) and
(data_variazione >=[Forms]![interrogazioni_ADI]![inizio])));

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[remigio]

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
A Query may have 255 fields and ten million records. It's not a singledigit
number, and you can't "add" a query to another query!
If you just want to display the count, and if there is no overlap between the
records returned by the two queries, you can just combine the two queries into
one using OR logic:
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))
OR
(data_variazione <=[Forms]![interrogazioni_ADI]![fine] ) and
(data_variazione >=[Forms]![interrogazioni_ADI]![inizio]));
If there are some records which will be returned by both sets of criteria, and
you want them counted as two records rather than one, you'll need a more
complex query - post back if that's the case.

Sorry for my bad English.
I'm simply trying to sum the result of two queries.

(code 1)
+
(code 2)

Is there a way to do it in only one Select code instruction?
I. e.:
Select (select.... from ...) + (select ... from ...)
Thank you very much.

Remigio

You're assuming that the "result" of a query is a number. IT ISN'T! The result
of a Query is a Recordset which can contain any number of fields, and any
number of records. This particular query happens to have only one record and
one field, which happens to be a number - but the Access SQL parser has no way
to know that.

I'd just use two DCounts:

=Dcount("*", "adi", "adi.pratica_2010 Is Not Nul) And adi.stato_praticaIs
Nul) And adi.data_attivazione<=[Forms]![interrogazioni_ADI]![fine]") +
DCount("*", "adi",  "stato_pratica is not null and data_variazione
<=[Forms]![interrogazioni_ADI]![fine] ) and data_variazione

=[Forms]![interrogazioni_ADI]![inizio]")

If you want to use a totals query, I'd make it one query combining the
criteria, rather than trying to use two queries and combine them:

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)))
OR
(((stato_pratica is not null) and
(data_variazione <=[Forms]![interrogazioni_ADI]![fine] ) and
(data_variazione >=[Forms]![interrogazioni_ADI]![inizio])));

--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com

Thank you very much for answers.
Is therre an online tutorial about the Sql syntax and exampes to use
for learning more about Sql?
Thanks so much.

Remigio

 

[John W. Vinson]

Thank you very much for answers.
Is therre an online tutorial about the Sql syntax and exampes to use
for learning more about Sql?
Thanks so much.

Remigio

Glad I was able to help. You might find some useful links here:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com