Count of records by location monthly

[wattyl]

Hi Out there,
I have a problem in that I have a table with the following columns :
Location Client and then 9 columns which are combo boxes that input High,Medium or Low. I require to retrieve a monthly return by location of the Highs Mediums and Lows for those locations on a monthly basis, can I do this using a query and how.
all help greatly appreciated.

 

[pendragons10]

Use an aggregate query, I'm not sure exactly what information your looking for or if its in the same table or not but use a modification of
SELECT COUNT(NumberEntries) AS Total, Location, DATEPART(m, SQL_date) AS MONTH From Table1 GROUP BY Location, SQL_date.
Or the function MONTH(SQL_date) is an alternative way to extract the month out of a date - dont forget to GROUP BY SQL_date.
To You can either enter a subquery or If using a VB interface I think I'd be tempted to use separate queries initially and link via a common field (e.g.Client) to the Monthly query later
SELECT COUNT(Column1.Selected) AS 1, [or]
SUM(Column2,selected) AS 2........ect FROM Table1 WHERE selected = "Medium"
GROUP BY AnyOtherColumn
Not comprehensive but I hope you get the general Idea?