Combining Queries

[Guest]

I have several Queries that count field entries from last month and the prior
months. They seem to work great but I would like to combine these Queries
into one Query. It must be possible but I am failing at every turn. Anyone
have any ideas?

SQL Query One ...

SELECT Count(*) AS Name1
FROM (SELECT DISTINCT

.[field]
FROM

WHERE (((Month([Date]))=Month(DateAdd("m",-1,Now()))))
)

SQL Query Two ...

SELECT Count(*) AS Name2
FROM (SELECT DISTINCT

.[field]
FROM

WHERE (((Month([Date]))=Month(DateAdd("m",-2,Now()))))
)

SQL Query Three ...

SELECT Count(*) AS Name3
FROM (SELECT DISTINCT

.[field]
FROM

WHERE (((Month([Date]))=Month(DateAdd("m",-3,Now()))))
)

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Those queries can be converted into one query:

SELECT Month([Date]) As Mnth, [field], Count(*) As Names
FROM table
WHERE [Date] BETWEEN DateSerial(Year(DateAdd("m", -12, Date())),
Month(DateAdd("m", -12, Date)), 1) And
DateSerial(Year(Date()), Month(Date()),1)
GROUP BY Month([Date]), [field]

Data will look like this:

Mnth [field] Names
---- ------- ------
1 X 3
1 Y 4
2 X 25
2 Y 30
.... etc. ...

The WHERE clause will get the whole-month data for the previous 12
months. Change the 12 to whatever number of months you want to go back.
You could even change the interval number to a parameter to allow
varying number of months of data:

PARAMETERS [Months?] Integer;
SELECT Month([Date]) As Mnth, [field], Count(*) As Names
FROM table
WHERE [Date] BETWEEN DateSerial(Year(DateAdd("m", -[Months?], Date())),
Month(DateAdd("m", -[Months?], Date)), 1) And
DateSerial(Year(Date()), Month(Date()),1)
GROUP BY Month([Date]), [field]
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmaMmIechKqOuFEgEQLjUgCgxOXkkeMC2rUpaQQvWWXNu4hB7U8AnRlI
nnIIjpfBHpG6xhXxLnmp5aVb
=XYrM
-----END PGP SIGNATURE-----

 

[Guest]

You could also try adding this new query:

Select Field1 from Query1
UNION
Select Field1 from Query2
UNION
Select Field1 from Query3

I purposefully selected only one field from each query. Test this first and
then you can select more fields, just be careful because a UNION Query
requires each select statement to have identical number of fields.

 

[Guest]

Thanks for the input but I should have clarified, I'm trying to do this in
attempt to make obsolete the previous Queries. I have 13 Queries right now
and I think I can narrow it down to 2. Actually I have a lot more than that
but that's another story. Basically I'm trying to get to use 1 Query in place
of 12. I'm going to try the previous suggestion and see if that does it for
me. Thanks!

You could also try adding this new query:

Select Field1 from Query1
UNION
Select Field1 from Query2
UNION
Select Field1 from Query3

I purposefully selected only one field from each query. Test this first and
then you can select more fields, just be careful because a UNION Query
requires each select statement to have identical number of fields.

I have several Queries that count field entries from last month and the prior
months. They seem to work great but I would like to combine these Queries
into one Query. It must be possible but I am failing at every turn. Anyone
have any ideas?

SQL Query One ...

SELECT Count(*) AS Name1
FROM (SELECT DISTINCT

.[field]
FROM

WHERE (((Month([Date]))=Month(DateAdd("m",-1,Now()))))
)

SQL Query Two ...

SELECT Count(*) AS Name2
FROM (SELECT DISTINCT

.[field]
FROM

WHERE (((Month([Date]))=Month(DateAdd("m",-2,Now()))))
)

SQL Query Three ...

SELECT Count(*) AS Name3
FROM (SELECT DISTINCT

.[field]
FROM

WHERE (((Month([Date]))=Month(DateAdd("m",-3,Now()))))
)