Trouble with Access comparison Chart

What is the basis for comparison?  The question is not clear.

Is your question about the query not working, or the Chart not being created.

Again, we need a bit more information on the specifics of this system, and what, exactly you are trying to achieve.

JeffCoachman

Sorry.  I need to have the chart compare the number of Work Orders submitted versus the amount of Work Orders rejected.  I have never used a chart before so I am not 100% clear on creating the chart.  I need to be able to show the track line.

The chart is just showing the what is submitted on one bar but not what is rejected on the next bar.

Then it seems that you will have to post a sample of "exactly" what you need this chart to look like.
To many things are still unknown:
- It this chart for a "selected Month" or all months?
- What does your raw data look like?
- By "track line." I will presume you mean "Trend line". So you need a trend line of what? ...the Total or the Rejected?
- There are 6 different type of Trendlines, which one do you want?

In any event, ...here is a sample to get you started.

You will have to study it carefully and try to adapt it to work in your database.

JeffCoachman
Access-EEQ24915121ChartTotalAndR.mdb

The chart needs to show all months year to date.  The trend line is to show the rejected percentage compared to the submitted.  I will include the new database and the xls. sheet I was given to build the database from.  It shows the chart in xls..  
EWOtracking2.mdb
Nordam-EWO-Status.xls

This is basically what my sample does.

BTW, in your chart, what you are calling a "Track Line" is basically second chart, superimposed on the first.
So you will have to create a new column in the query that calculates the Percent.
Then create a separate chart of that series and superimpose it on the existing Chart.

;-)

JeffCoachman

I do not know how to do this.  I need the trend line for the chart as it appears in the xls. file.  I don't know how to get the chart to work the way I need so how do you superimpose the trend line?  How would you calculate the field for the percentage?

<How would you calculate the field for the percentage?>
Insert a new field that is basically Rejected/Total
(This is done for you in the new sample)

<I do not know how to do this.>
Then you must bear in mind that to simulating the Excel chart in Access *requires* that you to be fairly good at creating and manipulating Chart Objects in Access.

I have been working with Access charts for years and this took me quite a while to simulate all of the attributes present in the Excel file.
Some of these attributes like: scaling the percentages, drawing the baseline, inserting the Text "Goal<=5%", inserting a secondary Axis, and sorting by the Month Name, ...etc, are difficult for a beginner to tackle in and of themselves.  Taken all in the same chart, ...makes it even more challenging.

There are other factors as well:
- Actually displaying a less than or equal to symbol
- 1 digit percent precision means that any value from 2.5 to 3.4 will be displayed as 3

The other unknown is exactly what your Raw data for this chart will be.
Will it be the same as the data in the Excel file ("Pre-Summarized" and transposed)
Or will it be "true" raw data, in standard table structure (1 "record" per row, as in my sample)

Finally, remember that instead of me walking you through this entire project over the course of many posts, (possibly weeks in real time), ...that you have the option of simply inserting your data into my sample.
Then it should all work just fine.
Then it may be only a matter of tweaking that chart a little.

Here is a new sample that approximates the Excel file as closely as possible.

Examine it closely, try to adapt it for your needs, then tell me how you would like to proceed.

;-)

JeffCoachman
Access-EEQ24915121ChartTotalAndR.mdb
untitled.JPG

Jeff,

Thank  you so much for your patience and large amount of help.  I have been trying to make my data work in your database but it isn't working.  I keep getting an incorrect count of the EWOnumber or WOID in your example.  It is only counting the "rejected" so it shows the percentage as 100%.  I kept your query and tried to make a new one to test just to make sure the query works before I change the name but the query is named qryEWO2 for now.  If you could assist me in this problem I think we will have the problem fixed.
EWOtracking3.mdb

1. There is no query named: qryEWO2 in the DB, do you mean: "qryWO2"?

2. You have another table: "Trackingtbl" in qryWO2
I have no idea what this table is, or how, (or even if), it relates to the issue at hand.
Even if I remove this table, and try to run the query, I get errors

3. So we need to go back to the point where you simply bring my *unedited* query and table into the DB.  This appears to be "qryWO"

4. So at this point, ...why do we need qryWO2?

JeffCoachman

1. Sorry it is qryWO2.  That is the query I tried to edit like yours but I cant get it to run

2. Trackingtbl is the table with the raw data.  That is the table I need the query to run off of.  

3. I used your unedited query and renamed it.  Then I edited it to fit my actual table and my field names.  Once I get the qryWO2 to run as yours does I will rename it to go with the report you created so it will automatically update the chart in your report.

Sorry but does this make more sense?

firstlevelEWOreviewdate is the date the EWO is submitted.  EWOnumber is the primary key I was trying to get the COUNT from.  dateoffirstlevelEWOaction is the date it was approved, rejected, etc.  firstlevelEWOaction is whether it was approved, rejected, etc. This is all in the Trackingtbl.  This is the table that the end users are populating through the form I created.

I tried to just leave your query as is and change my table name and field names to work in your example and I am still getting the same results.  If the query does run it comes back and says that only 1 WO was submitted and 1 WO was rejected so the percentage is !00%.

I am very sorry to keep troubling you with this but I am on a deadline and this is all I need to finish the project.

If you are working by a deadline, then please state what the deadline is in your original question.
Normally Expert will reply on a "as time allows basis"
So if you are under a time restraint, experts will be able to figure if they can help you through to conclusion within the time parameters.

Can you state what fields in Trackingtbl correspond to the fields in tblWO?

Also again, please remember that you will ultimately be responsible for creating the chart.
As I stated in my previous post, this is an extemely complex chart you have asked for here, it took me quite a while to replicate it.
Therefore, you must be fairly knowledgeable in chart design to take what I have done and apply it to your database.
So if you are under a time limit, I suggest you simply try to import your data into my sample.
(or simply load your data into the Excel file, and use that for the time being)

But in the mean time, if you provide me with the corresponding fields, I will do my best to get the query going.

JeffCoachman

In the Trackingtbl the EWOnumber field is the WOID, the firstlevelEWOaction is the WOStatus field.  Now the field firstlevelEWOreviewdate is the date the EWO was submitted and then the dateoffirstlevelEWOaction is the date it was actually reviewed and the date the status changed

Here is the db where I tried to edit my data to fit your db.  Run your query unedited and you will see my trouble
EWOtracking4.mdb

If you want me to help then you will have to submit samples that display the issue directly.
Start up forms and hiding the Navigation Pane only complicates things, and are irrelevent to the issue at hand.

Again, please tell me the direct translation fields in Trackingtbl vs tblWO.
WOID=
WODate=
WOStatus=

WOID = EWOnumber
WODate = firstlevelEWOreviewdate
WOStatus = firstlevelEWOaction

All my forms are based off the Trackingtbl so it requires a large amount of changes to insert all my stuff into your db.

qryWO2 is your original query with my tbl name and field names.  If I could get that query to run than I could rename it to replace your qryWO to update the report.
EWOtracking3.mdb

The data in the db is just sample data and can be edited for testing.  It is not live.

Thank you so much for your help, I really appreciate it.

Actually this was a very challenging question, so I must thank you as well.
;-)

Jeff

I normally get asked to do new and challenging things to me anyway