Duane Hookom's Line graph by code

  • Thread starter Thread starter george 16-17
  • Start date Start date
G

george 16-17

Greetings all,

....asking a vague question...expecting a vague answer...

I have adapted Mr Hookom's line graph by code example report from
http://www.access.hookom.net/Samples.htm. It is based on a crosstab query and
it works fine. It graphs all the data on one graph without using MS graph.
Could the code be adapted to produce a separate graph for each series of data
in a group?

Any direction would be appreciated. I can post the code if needed.
Thanks in advance,
george
 
You would need to include a Record Source for the report that returns the
unique group. Then add a text box bound to the group value in the detail
section.

The DrawGraph() function would need to be modified to add an argument for
the group so the OpenRecordset() method pulls only the group data.
 
Hi Duane,

Thanks for replying.

I appreciate the direction and your expertise. I have been able to include
the record source and the bound text box.

A less vague question...how do I add the arugment? That is where I am
stumped. I apology in advance.

Thanks again,
george
 
Assuming you have a [GroupID] field in your report, your function call might
look like:
DrawGraph(Me.GroupID)
The DrawGraph function would then used this value to filter the recordset
that is used to draw the lines.
 
Hi Duane,
Thanks again for the reply. The field I want to filter the graph on is
Me.Main_code. When I place it into the DrawGraph function I get an error
"wrong number of arguments or invalid propety assignment".

The report is bound to a select query where the field Me.Main_code is and
the openrecordset opens a crosstab based on the same select query.

I am really struggling with this, as I am still a newbie. I appreciate your
patience.

george

Duane Hookom said:
Assuming you have a [GroupID] field in your report, your function call might
look like:
DrawGraph(Me.GroupID)
The DrawGraph function would then used this value to filter the recordset
that is used to draw the lines.

--
Duane Hookom
Microsoft Access MVP


george 16-17 said:
Hi Duane,

Thanks for replying.

I appreciate the direction and your expertise. I have been able to include
the record source and the bound text box.

A less vague question...how do I add the arugment? That is where I am
stumped. I apology in advance.

Thanks again,
george
Click to expand...
Click to expand...
 
Your code in the report should look something like (assuming Main_Code is a
string):
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
DrawGraph Me.Main_Code
End Sub
Sub DrawGraph(strMainCode As String)
'
'
'
Dim strSQL as String
strSQL = "SELECT * FROM qxtbGraphMe WHERE [Main_Code]='" & _
strMainCode & "'"
Set rs = db.OpenRecordset(strSQL)
'
'

--
Duane Hookom
Microsoft Access MVP


george 16-17 said:
Hi Duane,
Thanks again for the reply. The field I want to filter the graph on is
Me.Main_code. When I place it into the DrawGraph function I get an error
"wrong number of arguments or invalid propety assignment".

The report is bound to a select query where the field Me.Main_code is and
the openrecordset opens a crosstab based on the same select query.

I am really struggling with this, as I am still a newbie. I appreciate your
patience.

george

Duane Hookom said:
Assuming you have a [GroupID] field in your report, your function call might
look like:
DrawGraph(Me.GroupID)
The DrawGraph function would then used this value to filter the recordset
that is used to draw the lines.

--
Duane Hookom
Microsoft Access MVP


george 16-17 said:
Hi Duane,

Thanks for replying.

I appreciate the direction and your expertise. I have been able to include
the record source and the bound text box.

A less vague question...how do I add the arugment? That is where I am
stumped. I apology in advance.

Thanks again,
george



:

You would need to include a Record Source for the report that returns the
unique group. Then add a text box bound to the group value in the detail
section.

The DrawGraph() function would need to be modified to add an argument for
the group so the OpenRecordset() method pulls only the group data.
--
Duane Hookom
Microsoft Access MVP


:

Greetings all,

...asking a vague question...expecting a vague answer...

I have adapted Mr Hookom's line graph by code example report from
http://www.access.hookom.net/Samples.htm. It is based on a crosstab query and
it works fine. It graphs all the data on one graph without using MS graph.
Could the code be adapted to produce a separate graph for each series of data
in a group?

Any direction would be appreciated. I can post the code if needed.
Thanks in advance,
george
Click to expand...
Click to expand...
Click to expand...
 
Hi Duane,

I got to work with your help! Much appreciated!

It is almost perfect...The only problem is that the graph does not graph the
nulls (0 values) as yours did originally. I had the same issue with chart
control. I need to graphically demostrate where there are no injuries for
that month based on a particular injury code.
Here is my string:
strSQL = "TRANSFORM CDbl(Nz(Sum(qrySearchTopValues2.IncidentCount),0)) AS
[Total Of IncidentCount] SELECT qrySearchTopValues2.SortYM,
qrySearchTopValues2.InjuryQuart From qrySearchTopValues2 WHERE
((qrySearchTopValues2.Main_code) = """ & strMainCode & """) GROUP BY
qrySearchTopValues2.SortYM, qrySearchTopValues2.InjuryQuart PIVOT
qrySearchTopValues2.Main_code"

I feel that I am in the ballpark (but with a hockey stick).

Any thoughts on how I can introduce the nulls would be deeply appreciated?

george

Duane Hookom said:
Your code in the report should look something like (assuming Main_Code is a
string):
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
DrawGraph Me.Main_Code
End Sub
Sub DrawGraph(strMainCode As String)
'
'
'
Dim strSQL as String
strSQL = "SELECT * FROM qxtbGraphMe WHERE [Main_Code]='" & _
strMainCode & "'"
Set rs = db.OpenRecordset(strSQL)
'
'

--
Duane Hookom
Microsoft Access MVP


george 16-17 said:
Hi Duane,
Thanks again for the reply. The field I want to filter the graph on is
Me.Main_code. When I place it into the DrawGraph function I get an error
"wrong number of arguments or invalid propety assignment".

The report is bound to a select query where the field Me.Main_code is and
the openrecordset opens a crosstab based on the same select query.

I am really struggling with this, as I am still a newbie. I appreciate your
patience.

george

Duane Hookom said:
Assuming you have a [GroupID] field in your report, your function call might
look like:
DrawGraph(Me.GroupID)
The DrawGraph function would then used this value to filter the recordset
that is used to draw the lines.

--
Duane Hookom
Microsoft Access MVP


:

Hi Duane,

Thanks for replying.

I appreciate the direction and your expertise. I have been able to include
the record source and the bound text box.

A less vague question...how do I add the arugment? That is where I am
stumped. I apology in advance.

Thanks again,
george



:

You would need to include a Record Source for the report that returns the
unique group. Then add a text box bound to the group value in the detail
section.

The DrawGraph() function would need to be modified to add an argument for
the group so the OpenRecordset() method pulls only the group data.
--
Duane Hookom
Microsoft Access MVP


:

Greetings all,

...asking a vague question...expecting a vague answer...

I have adapted Mr Hookom's line graph by code example report from
http://www.access.hookom.net/Samples.htm. It is based on a crosstab query and
it works fine. It graphs all the data on one graph without using MS graph.
Could the code be adapted to produce a separate graph for each series of data
in a group?

Any direction would be appreciated. I can post the code if needed.
Thanks in advance,
george
Click to expand...
Click to expand...
Click to expand...
 
I expect you are getting 0s in all of your data but are missing either
records or columns. Is this correct? If so, you need to use a LEFT or RIGHT
JOIN to get all the SortYM and InjuryQuart values or include the column
headings values in your PIVOT clause ie:
PIVOT qrySearchTopValues2.Main_Code IN (....).
--
Duane Hookom
Microsoft Access MVP


george 16-17 said:
Hi Duane,

I got to work with your help! Much appreciated!

It is almost perfect...The only problem is that the graph does not graph the
nulls (0 values) as yours did originally. I had the same issue with chart
control. I need to graphically demostrate where there are no injuries for
that month based on a particular injury code.
Here is my string:
strSQL = "TRANSFORM CDbl(Nz(Sum(qrySearchTopValues2.IncidentCount),0)) AS
[Total Of IncidentCount] SELECT qrySearchTopValues2.SortYM,
qrySearchTopValues2.InjuryQuart From qrySearchTopValues2 WHERE
((qrySearchTopValues2.Main_code) = """ & strMainCode & """) GROUP BY
qrySearchTopValues2.SortYM, qrySearchTopValues2.InjuryQuart PIVOT
qrySearchTopValues2.Main_code"

I feel that I am in the ballpark (but with a hockey stick).

Any thoughts on how I can introduce the nulls would be deeply appreciated?

george

Duane Hookom said:
Your code in the report should look something like (assuming Main_Code is a
string):
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
DrawGraph Me.Main_Code
End Sub
Sub DrawGraph(strMainCode As String)
'
'
'
Dim strSQL as String
strSQL = "SELECT * FROM qxtbGraphMe WHERE [Main_Code]='" & _
strMainCode & "'"
Set rs = db.OpenRecordset(strSQL)
'
'

--
Duane Hookom
Microsoft Access MVP


george 16-17 said:
Hi Duane,
Thanks again for the reply. The field I want to filter the graph on is
Me.Main_code. When I place it into the DrawGraph function I get an error
"wrong number of arguments or invalid propety assignment".

The report is bound to a select query where the field Me.Main_code is and
the openrecordset opens a crosstab based on the same select query.

I am really struggling with this, as I am still a newbie. I appreciate your
patience.

george

:

Assuming you have a [GroupID] field in your report, your function call might
look like:
DrawGraph(Me.GroupID)
The DrawGraph function would then used this value to filter the recordset
that is used to draw the lines.

--
Duane Hookom
Microsoft Access MVP


:

Hi Duane,

Thanks for replying.

I appreciate the direction and your expertise. I have been able to include
the record source and the bound text box.

A less vague question...how do I add the arugment? That is where I am
stumped. I apology in advance.

Thanks again,
george



:

You would need to include a Record Source for the report that returns the
unique group. Then add a text box bound to the group value in the detail
section.

The DrawGraph() function would need to be modified to add an argument for
the group so the OpenRecordset() method pulls only the group data.
--
Duane Hookom
Microsoft Access MVP


:

Greetings all,

...asking a vague question...expecting a vague answer...

I have adapted Mr Hookom's line graph by code example report from
http://www.access.hookom.net/Samples.htm. It is based on a crosstab query and
it works fine. It graphs all the data on one graph without using MS graph.
Could the code be adapted to produce a separate graph for each series of data
in a group?

Any direction would be appreciated. I can post the code if needed.
Thanks in advance,
george
Click to expand...
Click to expand...
Click to expand...
 
You are correct. There are missing records in the select query.

I tried the PIVOT IN clause suggestion, but it did not add in the 0s.

I will add in all the attempt to join in all the InjuryQuart and SortYM
values.

I appreciate all your time, expertise, prompt and knowledgeable replys.


Duane Hookom said:
I expect you are getting 0s in all of your data but are missing either
records or columns. Is this correct? If so, you need to use a LEFT or RIGHT
JOIN to get all the SortYM and InjuryQuart values or include the column
headings values in your PIVOT clause ie:
PIVOT qrySearchTopValues2.Main_Code IN (....).
--
Duane Hookom
Microsoft Access MVP


george 16-17 said:
Hi Duane,

I got to work with your help! Much appreciated!

It is almost perfect...The only problem is that the graph does not graph the
nulls (0 values) as yours did originally. I had the same issue with chart
control. I need to graphically demostrate where there are no injuries for
that month based on a particular injury code.
Here is my string:
strSQL = "TRANSFORM CDbl(Nz(Sum(qrySearchTopValues2.IncidentCount),0)) AS
[Total Of IncidentCount] SELECT qrySearchTopValues2.SortYM,
qrySearchTopValues2.InjuryQuart From qrySearchTopValues2 WHERE
((qrySearchTopValues2.Main_code) = """ & strMainCode & """) GROUP BY
qrySearchTopValues2.SortYM, qrySearchTopValues2.InjuryQuart PIVOT
qrySearchTopValues2.Main_code"

I feel that I am in the ballpark (but with a hockey stick).

Any thoughts on how I can introduce the nulls would be deeply appreciated?

george

Duane Hookom said:
Your code in the report should look something like (assuming Main_Code is a
string):
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
DrawGraph Me.Main_Code
End Sub
Sub DrawGraph(strMainCode As String)
'
'
'
Dim strSQL as String
strSQL = "SELECT * FROM qxtbGraphMe WHERE [Main_Code]='" & _
strMainCode & "'"
Set rs = db.OpenRecordset(strSQL)
'
'

--
Duane Hookom
Microsoft Access MVP


:

Hi Duane,
Thanks again for the reply. The field I want to filter the graph on is
Me.Main_code. When I place it into the DrawGraph function I get an error
"wrong number of arguments or invalid propety assignment".

The report is bound to a select query where the field Me.Main_code is and
the openrecordset opens a crosstab based on the same select query.

I am really struggling with this, as I am still a newbie. I appreciate your
patience.

george

:

Assuming you have a [GroupID] field in your report, your function call might
look like:
DrawGraph(Me.GroupID)
The DrawGraph function would then used this value to filter the recordset
that is used to draw the lines.

--
Duane Hookom
Microsoft Access MVP


:

Hi Duane,

Thanks for replying.

I appreciate the direction and your expertise. I have been able to include
the record source and the bound text box.

A less vague question...how do I add the arugment? That is where I am
stumped. I apology in advance.

Thanks again,
george



:

You would need to include a Record Source for the report that returns the
unique group. Then add a text box bound to the group value in the detail
section.

The DrawGraph() function would need to be modified to add an argument for
the group so the OpenRecordset() method pulls only the group data.
--
Duane Hookom
Microsoft Access MVP


:

Greetings all,

...asking a vague question...expecting a vague answer...

I have adapted Mr Hookom's line graph by code example report from
http://www.access.hookom.net/Samples.htm. It is based on a crosstab query and
it works fine. It graphs all the data on one graph without using MS graph.
Could the code be adapted to produce a separate graph for each series of data
in a group?

Any direction would be appreciated. I can post the code if needed.
Thanks in advance,
george
Click to expand...
Click to expand...
Click to expand...
 
Back
Top