Make Chart From Query in Access 2007

[James Parker]

I want to make a bar chart that has specific failure codes (XXX) on the x-axis and the number of times each one occurred on the y-axis. The source query is drawn from a database that reflects each time a specific failure occurred, thus there is one column or field (Failure Code), and then one or more rows (reports) to indicate each time a specific failure occurred.

I have found various instructions which note that the Chart Wizard is accessible from either Reports or Forms (but not from Queries), so I made a report based on my query. In layout, I added a group and sort so that the Failure codes were grouped together, and then a footer below each group indicated the individual Count (that is what I need on the y axis and it has to be specificly related to the appropriate Failure Code). Unfortunately for me, the count is not in a separate field. More unfortunate is that the Chart Wizard wants to extract data from either a Query or Table, and I do not know how to group/sort and count in either of these formats.

I need some kind of step by step instruction to get me from the query to the finished chart. I tried to force the chart to work from the Report I made, but since I was unable to clearly specify an "x" and "y" axis, the result was useless (defaults to North, South, East etc, and 1st, 2nd, 3rd and 4th Quarter).

[June7]

Not enough info to offer 'step-by-step'. Building charts in Access is tricky and hard enough to describe even with detailed info. If you want to provide project I will look at.

[James Parker]

I do thank you very much for taking the time to respond.

Would it help if I sent you the SQL code for the query I am using? Otherwise, tell me if you just want me to attach the Access database I am working with and identify the query I was trying to use as a basis for the chart. Or if neither of these will be enough, specify something else.

[June7]

Would be best to have source data to test with. Copy database, remove confidential data, run Compact & Repair, zip if large, attach to post. The Attachment Manager is below the Advanced post editor.

[James Parker]

Here is a copy of the database. The Field I am trying to work with is labeled REJ. I have already done a sort.

Notice that the various three character codes repeat as they have occurred numerous times on various dates. The query uses a date range as a parameter, thus for a defined time period, I see how many times each failure code occurred.

I want a count of each code (how many time has it occurred) to be reflected on the y-axis, and I want the bars on the x-axis to reflect the three character code. Should be simple, but obviously I am not very skilled yet. Thanks again for your help.

[June7]

Step 1. Build report and chart on report. Any chart, any data, just get the object created. I started with default vertical bar chart.

Step 2. Build GROUP BY query that will aggregate the data. You can include date range criteria if you want.
SELECT REJ, Count(REJ) AS CountOfREJ FROM Table1 GROUP BY REJ;

Step 3. Refer to the saved query in the chart's RowSource property or build the query right in the property. The first field in the query will be X value, all others will be Ys.

Step 4. Format chart axis labels, titles, legend. This is not as easy as in Excel. I can't get the REJ values to show on X axis below the bars. Right click on chart > Chart Object > Edit (or Open), right click on the opened chart > Chart Options. Click Ok to close Options. Close the chart.

Step 5. Open report to preview and save and close. Open in design view and should see your settings instead of the generic stuff.

[James Parker]

Thanks very much. This will work for my purposes. I see that there is some way to indicate that an issue has been "solved", but I cannot find what to click to make that happen here. Nevertheless, the problem is now solved.