Filter a chart by date range (1 Viewer)

R

RussellDeano

Registered User.
Local time
Today, 07:03
Joined
Aug 19, 2017
Messages
22
Morning all,

Again apologies for my inexperience in advance.

I have four charts for my database where users input information about incidents at work.

1. Incidents by day of the week (to identify risk periods)
2. Incidents by location
3. Incidents by timeslot
4. Incidents by type

The database now holds 18 months worth of data and therefore the charts are becoming a little difficult to analyse as trends may have changed over time.

I would like to do one of two things.

1. Create a graph that could be filtered to contain all of the above
or
2. Add a simple date between option on a form for each chart and have the chart on a subform? (I believe this is how it should be done?)

Unfortunately i do not use code and this is my first database so i am learning as i go, from here and youtube videos! But i cannot find an easy step by step guide on how to do this. Any help would be greatly appreciated.

I have added as many images as i can to hopefully evidence what i am saying.
 

Attachments

  • Chart Query.JPG
    Chart Query.JPG
    32.1 KB · Views: 141
  • Database names.JPG
    Database names.JPG
    32.2 KB · Views: 118
  • Incident list.JPG
    Incident list.JPG
    34.2 KB · Views: 123
  • Incident type chart.JPG
    Incident type chart.JPG
    69.5 KB · Views: 120
  • Incidents by day.JPG
    Incidents by day.JPG
    37.9 KB · Views: 118
M

Mark_

Longboard on the internet
Local time
Today, 07:03
Joined
Sep 12, 2017
Messages
2,111
For myself, I don't use the chart control in Access. I find it rather... unfriendly. Instead I make my own charts.

I'm attaching a sample as it does allow you to do some things the built in control doesn't.

If you can work out how to generate the initial values (hopefully with DCount, DMax, DMin, or DLookup) this can be very handy.

If you have specific questions regarding how I did any of the tricks I did ask away!
 

Attachments

  • Custom Charting.accdb
    644 KB · Views: 140
isladogs

isladogs

MVP / VIP
Local time
Today, 15:03
Joined
Jan 14, 2017
Messages
18,209
Hi

Attached are some screenshots of something very similar to what you want.
These are for pastoral incidents in a school
I've also shown the form design

As you can see the same data can be interrogated in lots of different ways
One or more filters can be applied and the date ranges modified to suit

The charts themselves are much simpler than Mark's very nice examples.

Normally I would use a subform for the charts but in this case its all done on the main form with a horizontal bar in detail section

HTH

Feel free to ask questions if you want to know more...
 

Attachments

  • IncidentChartFormDesign.PNG
    IncidentChartFormDesign.PNG
    24.7 KB · Views: 114
  • IncidentChartLocation.PNG
    IncidentChartLocation.PNG
    45.3 KB · Views: 111
  • IncidentChartMonth.PNG
    IncidentChartMonth.PNG
    51.7 KB · Views: 118
Last edited:
M

Mark_

Longboard on the internet
Local time
Today, 07:03
Joined
Sep 12, 2017
Messages
2,111
Colin,

Code: 
Me.Control.Width = (Number * Constant)

for each bar?

Fun part is you can also check you value's to see if they are within range and set the color for each bar, that way you can tell what is a good value and what isn't at a glance.
 
isladogs

isladogs

MVP / VIP
Local time
Today, 15:03
Joined
Jan 14, 2017
Messages
18,209
Pretty much that
Here's the main bit of the code

Code: 
 'set the form height greater than is needed
    'changed from +2 to +10 v4503W to overcome scaling error
    Me.Detail.Height = (rst.RecordCount * (intControlHeight + intSpacer) + 10) * TwipsNumber
    
    With rst
        'find largest bar
        .MoveFirst
        MaxCount = 0
        Do Until .EOF
            If !CountOfPastoralRecordID > MaxCount Then MaxCount = !CountOfPastoralRecordID
            .MoveNext
        Loop
        
        'Format controls
        .MoveFirst
        intC = 1
        Do Until .EOF
            Me("L" & intC).visible = True       'show controls
            Me("Bar" & intC).visible = True
            Me("L" & intC).Left = 1 * TwipsNumber
            Me("Bar" & intC).Left = 5 * TwipsNumber
            Me("Bar" & intC) = !CountOfPastoralRecordID
            Me("Bar" & intC).Width = !CountOfPastoralRecordID / MaxCount * 20 * TwipsNumber 'set width - changed from 15 v4503
            Me("L" & intC).Top = intC * (intControlHeight + intSpacer) * TwipsNumber   'set position
            Me("Bar" & intC).Top = intC * (intControlHeight + intSpacer) * TwipsNumber
            Me("L" & intC).Caption = Nz(rst.Fields(0), "BLANK")  'load data, using first field in the recordset
            'CR v4503 02/11/2008 - added Val controls to show field value
            Me("Val" & intC).visible = True
            Me("Val" & intC).Top = intC * (intControlHeight + intSpacer) * TwipsNumber  'set position
            Me("Val" & intC).Left = (5 + !CountOfPastoralRecordID / MaxCount * 20) * TwipsNumber
            Me("Val" & intC).Caption = "  (" & Me("Bar" & intC).Value & ")"
            intC = intC + 1
            .MoveNext
        Loop
    End With
    rst.Close

I've attached a couple of example reports using a similar approach.
Lots more where these came from ...
 

Attachments

  • IncidentChartReport.PNG
    IncidentChartReport.PNG
    63.8 KB · Views: 113
  • AttendanceBehaviourReport.PNG
    AttendanceBehaviourReport.PNG
    66.6 KB · Views: 128
R

RussellDeano

Registered User.
Local time
Today, 07:03
Joined
Aug 19, 2017
Messages
22
Thank you both for your responses both examples look brilliant and far better than i had envisaged mine ever looking.

Colin, i have sent you a private message :)
 
M

Mark_

Longboard on the internet
Local time
Today, 07:03
Joined
Sep 12, 2017
Messages
2,111
Colin,

Looks very very nice! So when will you start colour coding them? B-)
 
isladogs

isladogs

MVP / VIP
Local time
Today, 15:03
Joined
Jan 14, 2017
Messages
18,209
No satisfying some people .... :) :D

The serious reply is that I colour code some items (including by value) but in my view it can be distracting if overdone.
Its about getting a balance

Some more random but very colourful items attached.
Some of these might be considered over-colourful

I really hope Access graphical capabilities begin soon to catch up with those in Excel.
Even so, there is a lot that can be done with a bit of effort

NOTE: All student data is from a demo db based on a fictional school
 

Attachments

  • CalendarMonth.gif
    CalendarMonth.gif
    35.5 KB · Views: 102
  • Exam6StudentResidualGrid.jpg
    Exam6StudentResidualGrid.jpg
    61.9 KB · Views: 94
  • Exam7KS4SubjResidual.gif
    Exam7KS4SubjResidual.gif
    67.3 KB · Views: 103
  • GradesAnalysisOptions2a.jpg
    GradesAnalysisOptions2a.jpg
    72.5 KB · Views: 102
  • PastoralIncidentSummaryChartAllYears.gif
    PastoralIncidentSummaryChartAllYears.gif
    34.6 KB · Views: 99
M

Mark_

Longboard on the internet
Local time
Today, 07:03
Joined
Sep 12, 2017
Messages
2,111
Very nice... I may be stealing some for ideas though. I'm working at a school with a nursing program and there are some... interesting questions that get asked from time to time and I may need to set up similar reports.

Last time I had to colour code it was fairly easy; Green = Good, Amber = Not so good, Red = Problem, Black = WTF??? OVER!!!

There are a few on here who may recognize this style...
 
isladogs

isladogs

MVP / VIP
Local time
Today, 15:03
Joined
Jan 14, 2017
Messages
18,209
Except one school I worked with also wanted blue and orange. Starting to get tricky... and in my view messy.
 
You must log in or register to reply here.

Similar threads

R
Pie chart on form
Replies
1
Views
166
CJ_London
C
N
Solved Copy filtered records to a temporary table
Replies
16
Views
347
Gasman
Gasman
isladogs
Access Europe: Better Access Charts – The Next Generation (Thomas Moeller) – Wed 6 Mar
Replies
2
Views
251
isladogs
isladogs
murray83
Solved scrapping details from a web site
Replies
11
Views
489
johar123q
J
P
Solved IE.Quit Takes Quite Long Time to Execute
Replies
15
Views
424
Pac-Man
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom