Rolling YTD Query

[John Cello]

I am trying to write a query to produce a report. Of the many fields in my
table "tblIssues", the ones I am trying to summarize are:
Restaurant [Text]
Category [Text]
Subcategory [Text]
DateOfVisit [Date\Time]
TimeOfVisit [Text]

What I am trying to do is write a query that takes a parameter from a form
for [Restaurant] and [Category] (which is working fine) will give me results
that I can use in Excel to make a bar chart. The chart will have 13 columns,
January through December, and a Total. I need to show bars for each of the
subcategories that occured each month, and a grand total at the end. I need
to extract year to date data. So I need to get a count of each subcategory
for each month.

So far, I have written enough queries in a vain attempt to get the desired
results that, if stacked, would reach Venus. I realize that this isn't going
to be a single query that returns this information. I have written queries
that count a type of subcategory for a month (12 of these) but when I try to
pull the results from these into a single query it returns nothing.

Any suggestions, ideas, examples from anyone would be greatly appreciated.

Thanks

 

[John Vinson]

I am trying to write a query to produce a report. Of the many fields in my
table "tblIssues", the ones I am trying to summarize are:
Restaurant [Text]
Category [Text]
Subcategory [Text]
DateOfVisit [Date\Time]
TimeOfVisit [Text]

What I am trying to do is write a query that takes a parameter from a form
for [Restaurant] and [Category] (which is working fine) will give me results
that I can use in Excel to make a bar chart. The chart will have 13 columns,
January through December, and a Total. I need to show bars for each of the
subcategories that occured each month, and a grand total at the end. I need
to extract year to date data. So I need to get a count of each subcategory
for each month.

So far, I have written enough queries in a vain attempt to get the desired
results that, if stacked, would reach Venus. I realize that this isn't going
to be a single query that returns this information. I have written queries
that count a type of subcategory for a month (12 of these) but when I try to
pull the results from these into a single query it returns nothing.

Um?

A single query should be just fine.

SELECT [Restaurant], [Category], Month([DateOfVisit]) AS VisitMonth,
<other fields as needed>
FROM tblIssues
WHERE DateOfVIsit BETWEEN DateAdd("yyyy", -1, Date()) AND Date()
GROUP BY Month([DateOfVisit]);

What am I missing here??

John W. Vinson[MVP]

 

[John Cello]

John:

Thank you for your prompt reply. Sorry if I didn't thoroughly explain what
I'm trying to do.
Modifying your SQL statement to read as:
SELECT tblIssues.Subcategory, Month(DateOfVisit) AS VisitMonth
FROM tblIssues
WHERE (((tblIssues.DateOfVisit) Between DateAdd("yyyy",-1,Date()) And
Date()) AND ((tblIssues.Restaurant)="anderson") AND
((tblIssues.Category)="complaint"))
GROUP BY tblIssues.Subcategory, Month(DateOfVisit);

Returned this result set:
Subcategory VisitMonth
Service 6
Service 7
Service 8
Service 9

What I am looking for is a query (or combination of queries) that will return
the number of occurences of each subcategory per month for a particular store,
giving me a result set similar to:

Subcategory January February
March etc.
Service 8 4
3
Food Quality 5 2
7
Atmosphere 9 6
3
Cleanliness 3 3
1
Other 5 7
9

Thanks again.
------------------------------------------------------------------------------------------

I am trying to write a query to produce a report. Of the many fields in my
table "tblIssues", the ones I am trying to summarize are:
Restaurant [Text]
Category [Text]
Subcategory [Text]
DateOfVisit [Date\Time]
TimeOfVisit [Text]

What I am trying to do is write a query that takes a parameter from a form
for [Restaurant] and [Category] (which is working fine) will give me results
that I can use in Excel to make a bar chart. The chart will have 13 columns,
January through December, and a Total. I need to show bars for each of the
subcategories that occured each month, and a grand total at the end. I need
to extract year to date data. So I need to get a count of each subcategory
for each month.

So far, I have written enough queries in a vain attempt to get the desired
results that, if stacked, would reach Venus. I realize that this isn't going
to be a single query that returns this information. I have written queries
that count a type of subcategory for a month (12 of these) but when I try to
pull the results from these into a single query it returns nothing.

Um?

A single query should be just fine.

SELECT [Restaurant], [Category], Month([DateOfVisit]) AS VisitMonth,
<other fields as needed>
FROM tblIssues
WHERE DateOfVIsit BETWEEN DateAdd("yyyy", -1, Date()) AND Date()
GROUP BY Month([DateOfVisit]);

What am I missing here??

John W. Vinson[MVP]

 

[John Vinson]

What I am looking for is a query (or combination of queries) that will return
the number of occurences of each subcategory per month for a particular store,
giving me a result set similar to:

This would be a Crosstab query. Rather than Month([DateOfVisit])
you'ld use Format([DateOfVisit], "mmmm") to return the month name, and
use the Crosstab query wizard to set the month name as the Column
Header and the subcategory as the Row Header. I'm a bit rusty about
creating crosstabs in my head or I'd post the SQL...


John W. Vinson[MVP]

 

[MGFoster]

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

You want a cross-tab query:

TRANSFORM Count(*) As theValue
SELECT Subcategory
FROM tblIssues
WHERE DateOfVisit Between DateAdd("yyyy",-1,Date() And Date()
AND Restaurant="anderson"
AND Category="complaint"
GROUP BY Subcategory
PIVOT Format(DateOfVisit,"mmm") IN
('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBRUBRX4echKqOuFEgEQJh3QCg/YbcpBhkSCLc+QO73SBiHvqQrAQAn2s2
Q79rxOQvKBgAccIa+gU8rf5V
=t+j5
-----END PGP SIGNATURE-----

John:

Thank you for your prompt reply. Sorry if I didn't thoroughly explain what
I'm trying to do.
Modifying your SQL statement to read as:
SELECT tblIssues.Subcategory, Month(DateOfVisit) AS VisitMonth
FROM tblIssues
WHERE (((tblIssues.DateOfVisit) Between DateAdd("yyyy",-1,Date()) And
Date()) AND ((tblIssues.Restaurant)="anderson") AND
((tblIssues.Category)="complaint"))
GROUP BY tblIssues.Subcategory, Month(DateOfVisit);

Returned this result set:
Subcategory VisitMonth
Service 6
Service 7
Service 8
Service 9

What I am looking for is a query (or combination of queries) that will return
the number of occurences of each subcategory per month for a particular store,
giving me a result set similar to:

Subcategory January February
March etc.
Service 8 4
3
Food Quality 5 2
7
Atmosphere 9 6
3
Cleanliness 3 3
1
Other 5 7
9

Thanks again.
------------------------------------------------------------------------------------------

I am trying to write a query to produce a report. Of the many fields in my
table "tblIssues", the ones I am trying to summarize are:
Restaurant [Text]
Category [Text]
Subcategory [Text]
DateOfVisit [Date\Time]
TimeOfVisit [Text]

What I am trying to do is write a query that takes a parameter from a form
for [Restaurant] and [Category] (which is working fine) will give me results
that I can use in Excel to make a bar chart. The chart will have 13 columns,
January through December, and a Total. I need to show bars for each of the
subcategories that occured each month, and a grand total at the end. I need
to extract year to date data. So I need to get a count of each subcategory
for each month.

So far, I have written enough queries in a vain attempt to get the desired
results that, if stacked, would reach Venus. I realize that this isn't going
to be a single query that returns this information. I have written queries
that count a type of subcategory for a month (12 of these) but when I try to
pull the results from these into a single query it returns nothing.

Um?

A single query should be just fine.

SELECT [Restaurant], [Category], Month([DateOfVisit]) AS VisitMonth,
<other fields as needed>
FROM tblIssues
WHERE DateOfVIsit BETWEEN DateAdd("yyyy", -1, Date()) AND Date()
GROUP BY Month([DateOfVisit]);

What am I missing here??

John W. Vinson[MVP]

 

[John Cello]

Perfect John, just what I was looking for.

Thank you.
-------------------------------------------------------------------------

What I am looking for is a query (or combination of queries) that will return
the number of occurences of each subcategory per month for a particular store,
giving me a result set similar to:

This would be a Crosstab query. Rather than Month([DateOfVisit])
you'ld use Format([DateOfVisit], "mmmm") to return the month name, and
use the Crosstab query wizard to set the month name as the Column
Header and the subcategory as the Row Header. I'm a bit rusty about
creating crosstabs in my head or I'd post the SQL...


John W. Vinson[MVP]

 

[John Vinson]

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

You want a cross-tab query:

TRANSFORM Count(*) As theValue
SELECT Subcategory
FROM tblIssues
WHERE DateOfVisit Between DateAdd("yyyy",-1,Date() And Date()
AND Restaurant="anderson"
AND Category="complaint"
GROUP BY Subcategory
PIVOT Format(DateOfVisit,"mmm") IN
('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'

Thanks MG! I sure couldn't do THAT in my head!!

John W. Vinson[MVP]