Text Calculations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that has data by date. When I run a parameter query by date I
get all of the "colors" for each day. I want to be able to run a query that
will give me the total number of "color" occurrences for the entire date
range. So far I can only get the total number for each individual date. I
would like to run a query for March 2005 and get Yellow 600, Blue 500, ...,
.....
 
KP said:
I have a table that has data by date. When I run a parameter query by date I
get all of the "colors" for each day. I want to be able to run a query that
will give me the total number of "color" occurrences for the entire date
range. So far I can only get the total number for each individual date. I
would like to run a query for March 2005 and get Yellow 600, Blue 500, ...,
....
Click to expand...

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

Something like this:

SELECT Color, Count(*)
FROM table_name
WHERE Color_Date BETWEEN #3/1/2005# And #3/30/2005#
GROUP BY Color

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQlxNBIechKqOuFEgEQI10QCfT9NqgUrGewNC2dAOyXyEu2L/0TUAnjOQ
Vgp54iyqSRzfxWTIAKMuozUe
=WpsO
-----END PGP SIGNATURE-----
 
Hi,

If you want the result for many month, you can add a computed expression in
the GROUP BY:


SELECT Color, DateSerial( Year(ColorDate), Month(ColorDate), 1), COUNT(*)
FROM yourTable
GROUP BY Color, DateSerial( Year(ColorDate), Month(ColorDate), 1)



A crosstab query based on that query can then help to display the result in
a more interesting way, for consultation.




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top