[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Configuring MS Access Chart Graph for multiple Fields.

Posted on 2006-05-12
6
Medium Priority
?
193 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:roggerom
  • 3
4 Comments
 
LVL 58

Expert Comment

by:harfang
ID: 16717200
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
 

Author Comment

by:roggerom
ID: 16731714
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
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 16732084
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
 
LVL 58

Expert Comment

by:harfang
ID: 16732110
Once you get these to work, tell us more about the charts...
(°v°)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question