Configuring MS Access Chart Graph for multiple Fields.

I have a need to expand the basic charting wizard results on my report.  I am pulling from  this query:
Row A           Row B                      Row C                      Row D                      Row E
StatusCode    SeverityCode      ServerPlatform      TicketNumber      CreateDate


I need my report to show the following summary information on the left side and a corresponding Chart to the right.  Thanks.

Open Tickets                                          
Testing thru: 3/28/06      Total Tickets            
OVERALL      09-Jan-06 to 28-Mar-06                                          
Low      26
Medium      17                                    
Severe      4                                          
Critical      3                                          
Total      50                                          
                                                
Week 1       09-Jan-06 to 13-Jan-06       M      T      W      T      F                                          
Low      8      -            1      4      3            
Medium      3      -            1      1      1            
Severe      1      -            1                        
Critical      1      -            1            
Total      13      0      0      4      5      4
roggeromAsked:
Who is Participating?
 
harfangConnect With a Mentor Commented:
Hello roggerom

If you have this data in Access, under the table name tblTickets, you can use simple totals and cross-tab queries, on which you can later base reports. These use parameters, you could provide these through a form as well.

First query "overview of open tickets"

PARAMETERS [from date:] DateTime, [to date:] DateTime;
SELECT
  SeverityCode AS code,
  Count(TicketNumber) AS total
FROM tblTickets
WHERE StatusCode='open'
  AND CreateDate Between [from date:] And [to date:]
GROUP BY
  SeverityCode Not Like 'c*',
  SeverityCode;

Second query "weekly details"

PARAMETERS [from date:] DateTime, [to date:] DateTime;
TRANSFORM Count(TicketNumber)
SELECT
  DatePart('ww',CreateDate) AS week,
  CreateDate-Weekday(CreateDate)+1 AS w_from,
  w_from+6 AS w_to,
  SeverityCode AS code,
  Count(TicketNumber) AS total
FROM tblTickets
WHERE StatusCode='open'
  AND CreateDate Between [from date:] And [to date:]
GROUP BY
  DatePart('ww',CreateDate),
  CreateDate-Weekday(CreateDate)+1,
  SeverityCode Not Like 'c*',
  SeverityCode
PIVOT Choose(Weekday(CreateDate),'Su','Mo','Tu','We','Th','Fr','Sa')
  In ('Su','Mo','Tu','We','Th','Fr','Sa');

Note that the desired output in your Excel question would be obtained through:

TRANSFORM Count(tblTickets.TicketNumber) AS CountOfTicketNumber
SELECT tblTickets.ServerPlatform AS platform, Count(tblTickets.TicketNumber) AS total
FROM tblTickets
WHERE (((tblTickets.StatusCode)='open'))
GROUP BY tblTickets.ServerPlatform
PIVOT tblTickets.SeverityCode In ('low','medium','severe','critical');

(no date range criteria here)

Cheers!
(°v°)
0
 
harfangCommented:
Hello roggerom

What exactly do you need help with? From your question, I do not understand the top paragraph (you have rows as column titles?) and even less the relation to the sample output below...

Would you care to show a bit more of table structure, existing queries, failed attempts?

(°v°)
0
 
roggeromAuthor Commented:
Please refer to the following link,  I am puttin my data in an excel spreadheet in the short term but would still like to have the access report solution work.  thanks..

http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21858799.html
0
 
harfangCommented:
Once you get these to work, tell us more about the charts...
(°v°)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.