Multi-plot single line custom grouped chart in Access Report

[EastCoast]

I have 4 columns of data in the same table (Min, Mid,Max, Salary). I am trying to create a chart that plots these 4 on the SAME line going horizontally across so basically it would look like a line with four different "X" marks. The value also needs to display. Example below - note that the salary field will sometimes be before the Mid and sometimes after the Mid depending on the data. This is what I need:

Min Mid Salary Max
$25 $40 $45 $52
x--------x--------x-----------x----

I have to do this for 1200 records so I need it to not be a one-by-one fix (to save time).

I have tried doing stacks, horizontal bar charts, overlapping, scatter plot, etc. I tried Crystal Reports as well. Any help would be greatly appreciated.

 

[Remou]

Is setting a textbox to a function an option for you?

Function Exes(min, mid, sal, max)

strGraph = String(100, "-")
Mid(strGraph, min, 1) = "x"
Mid(strGraph, mid, 1) = "x"
Mid(strGraph, sal, 1) = "x"
Mid(strGraph, max, 1) = "x"

Exes = strGraph
End Function

 

[dhookom]

You could do this without a chart control. Assuming you have 4 text boxes in your detail section for the values which you have entered "MoveMe" into the tag properties.

Add two text boxes to the report header section:
Name: txtMinMin
Control Source: =Min([Min])

Name: txtMaxMax
Control Source: =Max([Max])

Add a long horizontal line in the detail section leaving about 1/2" on each end from the edge of the report. Name this line control "lineScale".

Then add code to the On Format event of your report's Detail Section. You may need to play with the values and properties somewhat.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim dblScale As Double
    Dim ctl As Control
    'get the twips for each dollar
    dblScale = Me.lineScale.Width / (Me.txtMaxMax - Me.txtMinMin)
    For Each ctl In Me.Detail.Controls
        If ctl.Tag = "MoveMe" Then
            ctl.Left = Me.lineScale.Left + _
                (ctl.Value - Me.txtMinMin) * dblScale
            Me.CurrentY = Me.lineScale.Top - 60
            Me.CurrentX = ctl.Left
            Me.Print "X"
        End If
    Next
    
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MichaelRed]

Duane, interesting ... veeeeeeeeerry interesting ...

I have been trying to 'dream up' something like this for a while. Although my wants are for a bit more sophistication (more like the horiz stached bar chart with multiple stacks), you have hit on an approach which could both present more "stacks" and be farily easily customized in several ways "on the fly".

I hope to be in a position to give it a whirl in the (reasonably) near term future.




MichaelRed

 

[dhookom]

I have used similar code to create graphs, week-at-a-glance calendars, and gantt charts. I had a client that require a vertical bar chart where the bar width indicated another value/dimension such as volume. It takes some basic math skills.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]