MS Access line chart Create Values for X-Axis

Question

I have a table in MS Access that is structured like this (example)

part number	time of testing	cleanliness class A	cleanliness class B
B	2021-06-12 15:22:22.00	20	30
A	2021-06-14 13:04:22.00	400	50
A	2021-06-14 13:28:28.00	200	60
A	2021-06-14 14:17:5.00	300	11
B	2021-06-17 09:25:7.00	18	5
B	2021-06-17 09:37:7.00	21	17
A	2021-06-25 11:53:18.00	150	70
C	2021-06-26 18:01:01.00	210	30

As you can see, the cleanliness of different parts is tested in no particular order.

My goal is to create a line chart of the cleanliness of each cleanliness class that contains the part numbers I choose of a list that contains all the part numbers while also choosing a timeframe.

time of testing

At first I have two textboxes in which you can put Start and End dates. With the following code I do a query which selects only the rows between these dates.

Private Sub Befehl11_Click()
'Dates
Dim Anfang As Variant
Dim Ende As Variant

Text5.SetFocus
Anfang = Text5.Text

Text7.SetFocus
Ende = Text7.Text

Dim dbs As DAO.Database
Dim qdf As QueryDef
Dim strSQL As String
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("DatumGefiltert")
Application.RefreshDatabaseWindow
'SELECT-Statement bauen Build Select Statement 
strSQL = "SELECT * FROM dbo_Cleanliness WHERE Format(dbo_Cleanliness.Date_of_Analysis,'yyyy-MM-dd hh:mm:ss') >= Format("""
strSQL = strSQL & Anfang & " 00:00:00"""
strSQL = strSQL & ",""yyyy-MM-dd hh:mm:ss"") AND Format(dbo_Cleanliness.Date_of_Analysis,'yyyy-MM-dd hh:mm:ss') <= Format("""
strSQL = strSQL & Ende & " 23:59:00"" , ""yyyy-MM-dd hh:mm:ss"")"
Text9.SetFocus
Text9.Text = strSQL

qdf.SQL = strSQL

End Sub

List

The next step is to create a list where a can choose several part numbers. First I created a query which only selects the column "part number" with SELECT DISTINCT

| part number |
| :--|
|A |
|B |
|C|
Then I created a listbox in a form with that query as source and enabled multiselect
Listbox With a button the following code is run to build and execute a query

Private Sub Befehl4_Click()

Dim ctlSource As Control
Dim strItems As String
Dim intCurrentRow As Integer

'My listbox
Set ctlSource = Liste2

'The objects of the WHERE clause
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then

strItems = strItems & " " & "Nummer = " & ctlSource.Column(0, intCurrentRow) & " Or "
 End If
 Next intCurrentRow
 
'Get rid of the last OR

strItems = Left(strItems, Len(strItems) - 4)

'Build the Query
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim strSQL As String

        Set dbs = CurrentDb
        Set qdf = dbs.CreateQueryDef("myQuery2")
        Application.RefreshDatabaseWindow

      strSQL = "SELECT * FROM DatumGefiltert "
      strSQL = strSQL & "WHERE "
      strSQL = strSQL & strItems
'Order By part number(Nummer) and time of testing(Datum)
      strSQL = strSQL & " ORDER BY Nummer, Datum"

'RUN Query

   qdf.SQL = strSQL

'CLEAR the variables
      '  qdf.Close
'        Set qdf = Nothing
'        Set dbs = Nothing

End Sub

If I select A and B in the listbox I want to get.

part number	time of testing	cleanliness class A	cleanliness class B
A	2021-06-14 13:04:22.00	400	50
A	2021-06-14 13:28:28.00	200	60
A	2021-06-14 14:17:5.00	300	11
A	2021-06-25 11:53:18.00	150	70
B	2021-06-12 15:22:22.00	20	30
B	2021-06-17 09:25:7.00	18	5
B	2021-06-17 09:37:7.00	21	17

Now I want to create a line chart for each of the cleanliness classes that contains both the part numbers.

Desired Chart

My problem now is:

If I would use the date of analysis as values for the x-Axis the lines wouldn´t be conected

My idea would be to add a new column with the amount of times the part has been tested.

part number	time of testing	cleanliness class A	cleanliness class B	Test number
A	2021-06-14 13:04:22.00	400	50	1
A	2021-06-14 13:28:28.00	200	60	2
A	2021-06-14 14:17:5.00	300	11	3
A	2021-06-25 11:53:18.00	150	70	4
B	2021-06-12 15:22:22.00	20	30	1
B	2021-06-17 09:25:7.00	18	5	2
B	2021-06-17 09:37:7.00	21	17	3

These values in the column test number could be used as the X-Axis.

But unfortunately I don´t know how to do this?

Or is there maybe a simpler way to achieve my Linechart overall?

Thanks in advance! If something is unclear please ask for clarification.

Greetings arijon

You used a DISTINCT query to get list of part numbers - you don't have a table of part numbers? — June7, Feb 03 '22 at 21:34
The sequential test number field can be calculated. Review https://stackoverflow.com/questions/64400911/pivot-query-in-ms-access — June7, Feb 03 '22 at 21:39
Just to be sure I understand the chart. You want a line for each part selected and for only one cleanliness category? I can do that. It won't be short and sweet, but can be done. — June7, Feb 04 '22 at 09:12
For each cleanliness category I want one graph that contains the part numbers I selected in the list. So in my exaple I want two graphs (cleanliness class A) and (cleanliness class B) that contain the clean that contain the values of part number A and part Number B — Ari, Feb 04 '22 at 10:57

June7 · Answer 1 · 2022-02-04T11:22:18.750

I have Access 2010 and can use only classic MSGraph, not ModernChart.

Following assumes there is a unique ID field (if there isn't, can easily add autonumber type) otherwise use [time of testing] field for unique identifier.

Build and save Query1 (replace Data with your table or query name):

SELECT ID, [part number], [time of testing], [cleanliness class A] AS Cleanliness, 
      "A" AS Class, 
      DCount("*","Data","[part number]='" & [part number] & "' AND ID<=" & [ID]) AS [Test Number] 
      FROM Data
UNION 
SELECT ID, [part number], [time of testing], [cleanliness class B], "B", 
      DCount("*","Data","[part number]='" & [part number] & "' AND ID<=" & [ID]) 
      FROM Data;

A correlated subquery can be used instead of DCount() to calculate group sequence number, review Access query counter per group

Build a form or report with RecordSource:
SELECT "A" AS Class FROM Data UNION SELECT "B" FROM Data;
Create a textbox named tbxClass and bind it to Class field. Label caption Cleanliness Class:

Create chart with RowSource:

PARAMETERS [tbxClass] Text ( 255 ); 
TRANSFORM Sum(Cleanliness) AS Clean 
SELECT [Test Number] FROM Query1 
WHERE [Class]=[tbxClass] 
GROUP BY [Test Number] 
PIVOT [part number];

I wasn't able to set form for ContinuousView (error "can't view a form as continuous if it contains ... a bound chart ...") - never encountered that before but never used a CROSSTAB query with dynamic parameter as RowSource. If you want the two graphs viewed at same time, then instead of dynamic parameter in WHERE clause, build two graph objects and use static criteria for Class field WHERE Class="A".

However, dynamic parameterized CROSSTAB RowSource works just fine for a report.

Strongly advise not to use spaces nor punctuation/special characters in naming convention, better would be: TimeOfTesting or TestTime.

I will try out your solution as soon as I have time for it (over the weekend) and report back. Thank you — Ari, Feb 04 '22 at 12:02

MS Access line chart Create Values for X-Axis

1 Answers1