Access 97 Chart

[TIDMAN]

Help need to make an access 97 graph please

Hi there all,
I hope someone can help here, I have an access 97 db and i have been asked to get it to generate a graph because the one it has right now i am not sure what it is doing all to gether here is the ROW SOURCE info of the graph

TRANSFORM Sum(tbl_HitRegion_chrt.[Hit Rate]) AS [SumOfHit Rate] SELECT tbl_HitRegion_chrt.Month FROM tbl_HitRegion_chrt WHERE (((tbl_HitRegion_chrt.Region) Is Not Null)) GROUP BY tbl_HitRegion_chrt.Month, Month([month]), Year([month]) ORDER BY Month([month]), Year([month]) PIVOT tbl_HitRegion_chrt.Region;

Here is the EVENT PRODEDURE

Private Sub Label276_Click()
On Error GoTo Err_rprt_mkt_reg_Click
If (IsNull(Me!start_date) Or IsNull(Me!end_date)) Then
MsgBox "Please specify a time interval for the Region Chart", vbOKOnly, "Time Interval Error!"
Me!start_date.SetFocus
Exit Sub
End If

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryStatus byRegionNumberGroupTemp", acViewNormal
DoCmd.SetWarnings True

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryHitRateChart1", acViewNormal
DoCmd.SetWarnings True

Dim stDocName As String

stDocName = "rpt_HitRateRegionChart"
DoCmd.OpenReport stDocName, acPreview

Exit_rprt_mkt_reg_Click:
Exit Sub

Err_rprt_mkt_reg_Click:
MsgBox Err.Description
Resume Exit_rprt_mkt_reg_Click
End Sub

What i have been asked to do is
Total = cancel + lost
HIT RATE = WON over WON + LOST + PENDING

I have taken all the data out of the db but its still over 1mb in size
I hope someone can help me here I know that you will require more info just tell me what

Thanks so very much in advance.

TIDMAN

 

[Len Boorman]

Charts can be a pain. This is a methodology that works for me

1) Use a form to collect all selection criteria

2) use queries as necessary until you end up with one query that contains all of the data you want to chart, in the correct sequence and with nothing else. This means all selections,aggregations, calculations, whatever have already been done and are not part of the final query.

3) Use the chart wizard. By this time it should be relatively straightforward because you are only going to deal with a data set that requires no manipulation.

L

 

[The_Doc_Man]

Concur with Len.

NEVER (well, hardly ever) try to do this directly. Make a query that has a column of independent data and a column of dependent data. (Or, if it is strictly a counting-the-values operation), just a column of independent data. Then build the chart off the query.

 

[TIDMAN]

Thanks for the suggestions all, I am very confused by the whole thing. I love access but really have so much to learn.
I was able to get a friend of mine to upload the database to his site for me to post, it may make it easier for you to make a suggestion.

Anyway here goes

http://www.hamilton-pc-services.com/files/quotelog.zip

PASSWARD = lvda
under REPORT CRITERIA select the radio button for 1st date interval then inter 1st date then enter a 2nd date interval then you have to click in the 1st date again to activate the 2nd date then you select REGION HIT RATE CHART this is the one that has to have the formula Total = cancel + lost
HIT RATE = WON over WON + LOST + PENDING the problem that i have is i dont know how to enter this as a formula to get the qry to work and generate the chart?????


Thanks again any help at all is much appreciated.

TIDMAN......

 

[Len Boorman]

Unfortunately controls here at work give me a problem downloading anything.
so cannot have a look at your zip.

Do not worry that you think you have so much to learn. That is why all of us use this forum. I would suggest that there is nobody who uses or contributes to this forum would say that they knew it all. Having said that there are some great people here.

Will keep an eye on the thread and be back when I can contribute.

L

 

[TIDMAN]

Thank you Len, if email is an option i dont mind sending the DB to you?

Thanks again.

 

[Len Boorman]

Have PM'd you with an address

Len B