Trouble with Access comparison Chart

MattDylan
MattDylan used Ask the Experts™
on
I am trying to make a comparison chart for all WO's submitted each month to all the WO's that are rejected each month.  But Access comes up with some different type of error every time I try a different alternative.  I have been trying to do this in a cross tab query but it is not working.

SELECT EWOSubmittedqry.EWOnumber, EWOSubmittedqry.firstlevelEWOaction
FROM EWOSubmittedqry;

This query will return the results but not convert into the chart.  If I combine this query and one that just shows the rejected EWO's than it shows all the EWO's as being rejected.  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
What is the basis for comparison?  The question is not clear.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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

Author

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
The chart is just showing the what is submitted on one bar but not what is rejected on the next bar.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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

Author

Commented:
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
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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

Author

Commented:
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?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<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

Author

Commented:
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
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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


Author

Commented:
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.

Author

Commented:
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.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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

Author

Commented:
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

Author

Commented:
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
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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=

Author

Commented:
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

Author

Commented:
The data in the db is just sample data and can be edited for testing.  It is not live.
MIS Liason
Most Valuable Expert 2012
Commented:
Here is the modified file with the working query.

Again, all you had to do was open the two queries, on top of each other, and substitute the field names.
(See the screenshot)
EWOtracking3-3-24915121.zip
untitled.JPG

Author

Commented:
Thank you so much for your help, I really appreciate it.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

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

Jeff

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial