Pivot Chart ??? Help 1

[jsaliers]

I think that what I want is a Pivot Chart, but I am not sure. I have a query that supplies the data that I want, but I am not sure how to make an Access Report or Form that graphs the proper data. This is what I am looking for:

y-axis measures hours
x-axis consists of a program number
each program number broken into n project numbers
each project number broken into n disciplines

Here is a rough sketch...


7
6
5
4
3
2
1
Tech Eng CAD Tech CAD Eng CAD Maint
10 25 32
Program E137

Sorry, it is kinda cryptic. I need a way to graph the data produced by my query. I would normally export to Excel and brute force it, but it is for my boss, and he is not big on extra steps. Thanks in advance for any help!

Jon Saliers

 

[LittleSmudge]

Beware multi thread timewaster alert


thread702-528735


Thwack !

G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[GingerR]

how is your data formatted in your table(s)?

please give your table structure.

thanks--g

 

[jsaliers]

There are four fields. They are:

Program Number
Project Number
Discipline
Total Hours

Program is the highest division level. Project is the second division level. Discipline is the final division level. Thanks in advance for your help!

Jon

 

[GingerR]

ok this is long (but not that difficult) so hang on:
my assumption at this point is that you will look at one chart that depicts data for one PROGRAM NUMBER on one page. so set it up like this for now even if it's not what you want later so we can work thru the whole chart process.
1) Make a new form (FOrm1)
2) put a text box on the form (txtProgramNumber)
3) view it in form view. in the text box, type in a valid program number and hit TAB. leave the form open for now.

to make the chart:
Looks like your data is already in the correct format. click once on your table or query.
click the button to make a new report.
choose chart wizard.
go thru the wizard, doesnt matter what you pick cause we're gonna re-do it anyhow. get to where it's done making the chart and you're looking at the report in design view. i know it says North, South, East, West. We'll fix that in a minute. that's supposed to suffice as a depiction of what your chart will look like but ignore it for now.

1) right click on the chart object and choose PROPERTIES.
set Size Mode to STRETCH. later then you can select the corners and sides of the chart object and strech it to the size you want it. wait to do this until you see how your data is gonna look in there.

2) in the PROPERTIES, look at the ROW SOURCE. choose your table or query from the dropdown box. then hit the little button on the right with the three dots on it. it will open to your table. Make it a Crosstab Query.
ProjectNumber - Group By - Row Heading
Discipline - Group By - Column Heading
Hours - First - Value
ProgramNumber - WHERE, criteria put

 forms!Form1!txtProgramNumber

.

this will select the data only for the program number you have entered on Form1.
2b) run the query you just made (red ! up top). the other three fields' worth of data should appear for the program number you entered: ProjectNumber, Discipline and Hours. Select all records and copy (ctrl+c). close the query and choose YES if it asks you to save changes.
2c) double-click on the chart. now we're gonna get rid of the north-south-east-west thing and put in some 'real' data so you can see how the chart is really gonna look and work with its format: so couble-click on it, and if you don't see it, go VIEW DATASHEET. it looks like an excel sheet. click once in the upper-left-most grey cell so that the whole sheet turns black (selected). go PASTE (ctrl+v). answer YES when it says it won't fit. dont' know why it says that but who cares.
3) this is now serving as your 'template' so you can adjust colors, titles etc. chose menu CHART+OPTIONS to add data table (data below the x-axis), gridlines, etc. choose each data chunk and go CHART+CHART TYPE to make it a column chart, line chart, etc. chose each chunk once and select FORMAT + FORMAT SELECTED DATA SERIES to format the colors, etc.
hope this is enuf to get you started in the wonderful under-documented world of Access Charts )
write back so i know how it's going--g

 

[jsaliers]

WOW, I don't know how to thank you enough. I finally got the chart I was looking for. Thank you again. I do have a couple of questions though.

1. Is there a way to pareto-ize the chart? That is, get the greatest to smallest number of hours for each project number from left to right?

2. I am having slight difficulty with the query. When I set up the query, an input box popped up asking for a start date, an end date, and the project number. Is there a way to keep these? When I tried to run the query after making it a crosstab, it says, "The Microsoft Jet Database Engine does not recognize '[Enter Start Date:]' as a valid field name or expression." Just some background for ya, it took some time and work to get the queries right. I was given two tables. These needed to be sorted first by date range, then merged, then sorted by Program Number. I did this in steps, as it seemed most logical and easy at the time. The last query I used looks like this:

SELECT qryUnionTablesTEST.ProgramNumber, qryUnionTablesTEST.ProgramName, qryUnionTablesTEST.ProjectNumber, qryUnionTablesTEST.ProjectName, qryUnionTablesTEST.Discipline, Sum(qryUnionTablesTEST.TotalHoursSpent) AS SumOfHours
FROM qryUnionTablesTEST
WHERE (((qryUnionTablesTEST.ProgramNumber)=[Enter Program Number:]))
GROUP BY qryUnionTablesTEST.ProgramNumber, qryUnionTablesTEST.ProgramName, qryUnionTablesTEST.ProjectNumber, qryUnionTablesTEST.ProjectName, qryUnionTablesTEST.Discipline;

This query pulls data from the merged table, sums the hours by job discipline (electrical engr., mechanical engr, etc.) and eliminates all records with the exception of the entered program number.

If at all possible, I would like to keep these input boxes, and keep the data out of extra tables. Any ideas on how to do this?

3.Also, will this report update if I want to change the program number, or if more records are added with that program number?

Thank you again for all your help, and thanks in advance for any future help!!!

Jon Saliers

 

[GingerR]

good job. charts are a bear, but you have some good basic understanding now to do what you have to do from now on.

1) i dont think so. since it's a crosstab query, the column heading are what is in order. if you make a separate chart that has only one project on it, then you can.

2) go into the crosstab query design. in the grey part up above the query grid, right-click and choose 'PARAMETERS' from the pop-up menu.

you write "If at all possible, I would like to keep these input boxes, and keep the data out of extra tables. Any ideas on how to do this?"
not sure what you mean by this.

 

[GingerR]

good job. charts are a bear, but you have some good basic understanding now to do what you have to do from now on.

1) i dont think so. since it's a crosstab query, the column heading are what is in order. if you make a separate chart that has only one project on it, then you can.

2) go into the crosstab query design. in the grey part up above the query grid, right-click and choose 'PARAMETERS' from the pop-up menu. In PARAMETER put those things exactly as you have them in your queries then set their Data Types:

[Enter Start Date:] Date/Time
[Enter End Date:] Date/Time
[Enter Program Number:] Integer (?)

you write "If at all possible, I would like to keep these input boxes, and keep the data out of extra tables. Any ideas on how to do this?"
not sure what you mean by this.

3) yes. that is the beauty of access. try it--add some new records, then run the queries or chart again. each time you run a query, it grabs whatever is in the table at the time.

let me know about my question on #2.

g

 

[jsaliers]

Ginger, Thank you so much! I got it to work correctly. What I had meant by my wanting to keep the input boxes was that the query prompted the user for a start and end date and a program number. I wanted to still have the report prompt the user, without creating another form. I got it to work perfectly now, with your wonderful help!

Thank you again for all your help! It is much appreciated!

Jon

 

[GingerR]

no problem. i know charts are a bear, but i've been working with them for years now. let me know if you want more help, or if you want to learn more bells and whistles!!
g