graph/report wizard

[Guest]

I am trying to make a report using the graph wizard. What I have is the
following fields I want to use in the Graph:
1) Program Name
2) Place Found
3) Risk Severity
What I want the graph to do is this:
I want “Place Found†to be on the X axis, I want “Risk Severity†to be
counted on the Y axis. Ok so I followed the wizard and did this perfectly.
Here is the problem. I want to be able to select a “Program Name†from a
pulldown on a form and then have the graph report show the “Program Nameâ€
selected as the title and then “Place Found†and “Risk Severity†data
corresponding only to that selected “Program Name to be shown on the graph.
I have a control on a form that allows the user to select a “program nameâ€
from a pulldown and then it produces a query with just the info for that
program name but I cannot 1) get the graph to produce the “Program Name†as
either the title or text in the page header and 2) get the report to run even
though I use that query as the data for the report. I hope this question is
somewhat understandable! Any help would be great!
TIA!

 

[Guest]

Hi Jenn.

If I understand correctly, if you just open the report by itself, it shows
all records, but you want to limit it to just those records for a selected
Program Name. A good way to do this is to have a CommandButton on the form
that the user can click to open the report after selecting a Program Name
from the ComboBox, and to only allow the user to open the report in this
manner. In the Click event for the CommandButton, you would have something
like this (watch for line wrap):
DoCmd.OpenReport "ReportName",acViewPreview,,"[ProgramNameField] = " &
Chr(34) & Forms("FormName").Controls("ComboBoxName") & Chr(34)

In the above, be sure to replace ReportName, ProgramNameField, FormName, and
ComboBoxName with the names of your own objects. Also, I'm assuming that
Program Name is a string. If not, remove both instances of "& Chr(34)".

To have the selected ProgramName appear in the title of the report, insert a
TextBox in the Report Header, and set its Control Source to:
="Selected Program Name: " & [Forms]("FormName").[Controls]("ComboBoxName")

You will probably want to add additional code that enables/disables the
form's CommandButton depending on whether a Program Name has been selected,
as you may get errors or a blank report if the user attempts to open the
report with no Program Name selected.

-Michael

 

[Guest]

Hi. I followed your instructions and put my names into the places where you
had the placeholders but the report is still showing all the records, not
just the ones selected in the combobox. Any other things i might try?
TIA!

Hi Jenn.

If I understand correctly, if you just open the report by itself, it shows
all records, but you want to limit it to just those records for a selected
Program Name. A good way to do this is to have a CommandButton on the form
that the user can click to open the report after selecting a Program Name
from the ComboBox, and to only allow the user to open the report in this
manner. In the Click event for the CommandButton, you would have something
like this (watch for line wrap):
DoCmd.OpenReport "ReportName",acViewPreview,,"[ProgramNameField] = " &
Chr(34) & Forms("FormName").Controls("ComboBoxName") & Chr(34)

In the above, be sure to replace ReportName, ProgramNameField, FormName, and
ComboBoxName with the names of your own objects. Also, I'm assuming that
Program Name is a string. If not, remove both instances of "& Chr(34)".

To have the selected ProgramName appear in the title of the report, insert a
TextBox in the Report Header, and set its Control Source to:
="Selected Program Name: " & [Forms]("FormName").[Controls]("ComboBoxName")

You will probably want to add additional code that enables/disables the
form's CommandButton depending on whether a Program Name has been selected,
as you may get errors or a blank report if the user attempts to open the
report with no Program Name selected.

-Michael

I am trying to make a report using the graph wizard. What I have is the
following fields I want to use in the Graph:
1) Program Name
2) Place Found
3) Risk Severity
What I want the graph to do is this:
I want “Place Found†to be on the X axis, I want “Risk Severity†to be
counted on the Y axis. Ok so I followed the wizard and did this perfectly.
Here is the problem. I want to be able to select a “Program Name†from a
pulldown on a form and then have the graph report show the “Program Nameâ€
selected as the title and then “Place Found†and “Risk Severity†data
corresponding only to that selected “Program Name to be shown on the graph.
I have a control on a form that allows the user to select a “program nameâ€
from a pulldown and then it produces a query with just the info for that
program name but I cannot 1) get the graph to produce the “Program Name†as
either the title or text in the page header and 2) get the report to run even
though I use that query as the data for the report. I hope this question is
somewhat understandable! Any help would be great!
TIA!

 

[Guest]

Jenn,

Sorry, the info I provided would work with a standard report, but I seem to
have lost track of the fact that you were dealing with a chart. We'll have
to do this a bit differently.

First, change the code in the CommandButton's Click event to:
DoCmd.OpenReport "ReportName",acViewPreview
This will just open the Report in Preview mode, without attempting to modify
it.

Then, in the Row Source property of the Report's Chart Object, place some
SQL that gets its criteria from the form. A good way to figure out exactly
what this SQL should look like is:
1. Create a new query which duplicates the recordset containing all records
(if the Chart Object's Row Source is a query, just copy its SQL and paste
into a new query).
2. Add a criteria for ProgramNameField. Put in an actual criteria value,
and test the query so you can see that the records are indeed filtered by
that criteria.
3. Provided the query works correctly, copy the SQL and paste it into the
Row Source property of the Chart Object on the Report.
4. Replace the criteria value with a reference to the ComboBox on the Form.

As an example of Step 4, this SQL:
SELECT TableName.*, TableName.ProgramNameField
FROM TableName
WHERE (TableName.ProgramNameField)="CriteriaValue";

Should be changed to this after pasting it into the Chart object's Row
Source property (notice the only difference is in the WHERE line, after the
equals sign):
SELECT TableName.*, TableName.ProgramNameField
FROM TableName
WHERE (TableName.ProgramNameField)=
Forms("FormName").Controls("ComboBoxName");

If you prefer, this:
Forms("FormName").Controls("ComboBoxName")
can probably be replaced with something like this:
Forms!FormName!ComboBoxName

Obviously, your SQL may look quite a bit different, but I hope that gives
you an idea.

If you still have problems after making these changes, post back & provide
detailed information on your table, field, report and query names, any SQL
already in place, the current Row Source property of the Chart Object (and
the SQL if that Row Source is a query), and anything else you can think of
that I might have missed.

-Michael

Hi. I followed your instructions and put my names into the places where you
had the placeholders but the report is still showing all the records, not
just the ones selected in the combobox. Any other things i might try?
TIA!

Hi Jenn.

If I understand correctly, if you just open the report by itself, it shows
all records, but you want to limit it to just those records for a selected
Program Name. A good way to do this is to have a CommandButton on the form
that the user can click to open the report after selecting a Program Name
from the ComboBox, and to only allow the user to open the report in this
manner. In the Click event for the CommandButton, you would have something
like this (watch for line wrap):
DoCmd.OpenReport "ReportName",acViewPreview,,"[ProgramNameField] = " &
Chr(34) & Forms("FormName").Controls("ComboBoxName") & Chr(34)

In the above, be sure to replace ReportName, ProgramNameField, FormName, and
ComboBoxName with the names of your own objects. Also, I'm assuming that
Program Name is a string. If not, remove both instances of "& Chr(34)".

To have the selected ProgramName appear in the title of the report, insert a
TextBox in the Report Header, and set its Control Source to:
="Selected Program Name: " & [Forms]("FormName").[Controls]("ComboBoxName")

You will probably want to add additional code that enables/disables the
form's CommandButton depending on whether a Program Name has been selected,
as you may get errors or a blank report if the user attempts to open the
report with no Program Name selected.

-Michael

I am trying to make a report using the graph wizard. What I have is the
following fields I want to use in the Graph:
1) Program Name
2) Place Found
3) Risk Severity
What I want the graph to do is this:
I want “Place Found†to be on the X axis, I want “Risk Severity†to be
counted on the Y axis. Ok so I followed the wizard and did this perfectly.
Here is the problem. I want to be able to select a “Program Name†from a
pulldown on a form and then have the graph report show the “Program Nameâ€
selected as the title and then “Place Found†and “Risk Severity†data
corresponding only to that selected “Program Name to be shown on the graph.
I have a control on a form that allows the user to select a “program nameâ€
from a pulldown and then it produces a query with just the info for that
program name but I cannot 1) get the graph to produce the “Program Name†as
either the title or text in the page header and 2) get the report to run even
though I use that query as the data for the report. I hope this question is
somewhat understandable! Any help would be great!
TIA!

 

[Guest]

Hi Michael-

So I created and ran a query like you said and that part works perfectly.
The query is limited to what I have in the combo box.

Here is the SQL for the query that produces the result of what I want:

SELECT [General Info].[Program Name], [General Info].[Place Found], [General
Info].Severity
FROM [General Info]
GROUP BY [General Info].[Program Name], [General Info].[Place Found],
[General Info].Severity
HAVING ((([General Info].[Program Name])=[Forms]![Main]![Comboboxprogram]));

Notes:
1. “General Info†is the table I am pulling this data from.
2. “Program Nameâ€, “Place Found†and “Severity†are the fields in the table
“General Info†that I want
3. “Main†is the form the combo box is on that drives this query
4. “Comboboxprogram†is the combo box that is used as the query's critieria.


OK so the graph I have has this as the code in the row source currently:


TRANSFORM Count(*) AS [Count] SELECT [Place Found] FROM [Graph Query]
GROUP BY [Place Found] PIVOT [Severity];


"Graph Query" is the query mentioned above.

I tried replacing the current code in the record source with the SQL but I
am missing some VBA nomenclature because what it does is it makes the X axis
value = the “Program Name†and puts both “Severity†and “Place Found†on the
Y axis. And there is not data being shown.

I want the X axis to be “place foundâ€, the Y axis to be “severity†and the
graph to only show data ( data relating to a specific program name) based on
what is pulled down in the “comboboxprogramâ€. Bar chart style.

Does any of this help you in helping me? I know I am doing the VBA stuff
wrong in the row source of the graph...

Thanks again.

Hi. I followed your instructions and put my names into the places where you
had the placeholders but the report is still showing all the records, not
just the ones selected in the combobox. Any other things i might try?
TIA!

Hi Jenn.

If I understand correctly, if you just open the report by itself, it shows
all records, but you want to limit it to just those records for a selected
Program Name. A good way to do this is to have a CommandButton on the form
that the user can click to open the report after selecting a Program Name
from the ComboBox, and to only allow the user to open the report in this
manner. In the Click event for the CommandButton, you would have something
like this (watch for line wrap):
DoCmd.OpenReport "ReportName",acViewPreview,,"[ProgramNameField] = " &
Chr(34) & Forms("FormName").Controls("ComboBoxName") & Chr(34)

In the above, be sure to replace ReportName, ProgramNameField, FormName, and
ComboBoxName with the names of your own objects. Also, I'm assuming that
Program Name is a string. If not, remove both instances of "& Chr(34)".

To have the selected ProgramName appear in the title of the report, insert a
TextBox in the Report Header, and set its Control Source to:
="Selected Program Name: " & [Forms]("FormName").[Controls]("ComboBoxName")

You will probably want to add additional code that enables/disables the
form's CommandButton depending on whether a Program Name has been selected,
as you may get errors or a blank report if the user attempts to open the
report with no Program Name selected.

-Michael

I am trying to make a report using the graph wizard. What I have is the
following fields I want to use in the Graph:
1) Program Name
2) Place Found
3) Risk Severity
What I want the graph to do is this:
I want “Place Found†to be on the X axis, I want “Risk Severity†to be
counted on the Y axis. Ok so I followed the wizard and did this perfectly.
Here is the problem. I want to be able to select a “Program Name†from a
pulldown on a form and then have the graph report show the “Program Nameâ€
selected as the title and then “Place Found†and “Risk Severity†data
corresponding only to that selected “Program Name to be shown on the graph.
I have a control on a form that allows the user to select a “program nameâ€
from a pulldown and then it produces a query with just the info for that
program name but I cannot 1) get the graph to produce the “Program Name†as
either the title or text in the page header and 2) get the report to run even
though I use that query as the data for the report. I hope this question is
somewhat understandable! Any help would be great!
TIA!

 

[Guest]

Jenn,

Sorry I've been unable to help you so far. I can't guarantee results, but
if you could provide the following information, it may help get us closer to
a solution:

1. What Data Type is the "Severity" field (I assume "Program Name" and
"Place Found" are String types)?

2. Sample data from the "General Info" table.

3. Sample results from the "Graph Query" query.

4. What happens if you paste the SQL of your Crosstab query into a new
query, and just run it? Do you get any data? It will be a lot easier to
diagnose the problem if we get this to work correctly as a query before
plugging the SQL into the Chart object.

5. Please describe in more detail how the chart should look. Use your own
Field Names and some sample values. Also, if by any chance you can find any
charts available on the Web that look similar to what you want, please
provide a link.

6. Why are you basing a Crosstab query on a query that is grouped?
Shouldn't the Crosstab query be doing the grouping for you, rather than the
"Graph Query" query?


-Michael

Hi Michael-

So I created and ran a query like you said and that part works perfectly.
The query is limited to what I have in the combo box.

Here is the SQL for the query that produces the result of what I want:

SELECT [General Info].[Program Name], [General Info].[Place Found], [General
Info].Severity
FROM [General Info]
GROUP BY [General Info].[Program Name], [General Info].[Place Found],
[General Info].Severity
HAVING ((([General Info].[Program Name])=[Forms]![Main]![Comboboxprogram]));

Notes:
1. “General Info†is the table I am pulling this data from.
2. “Program Nameâ€, “Place Found†and “Severity†are the fields in the table
“General Info†that I want
3. “Main†is the form the combo box is on that drives this query
4. “Comboboxprogram†is the combo box that is used as the query's critieria.


OK so the graph I have has this as the code in the row source currently:


TRANSFORM Count(*) AS [Count] SELECT [Place Found] FROM [Graph Query]
GROUP BY [Place Found] PIVOT [Severity];


"Graph Query" is the query mentioned above.

I tried replacing the current code in the record source with the SQL but I
am missing some VBA nomenclature because what it does is it makes the X axis
value = the “Program Name†and puts both “Severity†and “Place Found†on the
Y axis. And there is not data being shown.

I want the X axis to be “place foundâ€, the Y axis to be “severity†and the
graph to only show data ( data relating to a specific program name) based on
what is pulled down in the “comboboxprogramâ€. Bar chart style.

Does any of this help you in helping me? I know I am doing the VBA stuff
wrong in the row source of the graph...

Thanks again.

Hi. I followed your instructions and put my names into the places where you
had the placeholders but the report is still showing all the records, not
just the ones selected in the combobox. Any other things i might try?
TIA!

Hi Jenn.

If I understand correctly, if you just open the report by itself, it shows
all records, but you want to limit it to just those records for a selected
Program Name. A good way to do this is to have a CommandButton on the form
that the user can click to open the report after selecting a Program Name
from the ComboBox, and to only allow the user to open the report in this
manner. In the Click event for the CommandButton, you would have something
like this (watch for line wrap):
DoCmd.OpenReport "ReportName",acViewPreview,,"[ProgramNameField] = " &
Chr(34) & Forms("FormName").Controls("ComboBoxName") & Chr(34)

In the above, be sure to replace ReportName, ProgramNameField, FormName, and
ComboBoxName with the names of your own objects. Also, I'm assuming that
Program Name is a string. If not, remove both instances of "& Chr(34)".

To have the selected ProgramName appear in the title of the report, insert a
TextBox in the Report Header, and set its Control Source to:
="Selected Program Name: " & [Forms]("FormName").[Controls]("ComboBoxName")

You will probably want to add additional code that enables/disables the
form's CommandButton depending on whether a Program Name has been selected,
as you may get errors or a blank report if the user attempts to open the
report with no Program Name selected.

-Michael


:

I am trying to make a report using the graph wizard. What I have is the
following fields I want to use in the Graph:
1) Program Name
2) Place Found
3) Risk Severity
What I want the graph to do is this:
I want “Place Found†to be on the X axis, I want “Risk Severity†to be
counted on the Y axis. Ok so I followed the wizard and did this perfectly.
Here is the problem. I want to be able to select a “Program Name†from a
pulldown on a form and then have the graph report show the “Program Nameâ€
selected as the title and then “Place Found†and “Risk Severity†data
corresponding only to that selected “Program Name to be shown on the graph.
I have a control on a form that allows the user to select a “program nameâ€
from a pulldown and then it produces a query with just the info for that
program name but I cannot 1) get the graph to produce the “Program Name†as
either the title or text in the page header and 2) get the report to run even
though I use that query as the data for the report. I hope this question is
somewhat understandable! Any help would be great!
TIA!

 

[Guest]

Jenn,

I thought of some more info I'd like to have:

7. Please post some sample data you expect to be returned by the crosstab
query.


Also, I've been having trouble getting a crosstab query to work if a
reference to a control on a form is involved. This may be why your report is
showing no data. So I did the following instead.

Place the following code in a Module (but not a Class Module or one attached
to a Form). If you're unsure how to do this, just create a new Module by
showing the Modules in the main Access Database window, clicking New, and
pasting in everything below from "Public Function" through "End Function":

Public Function GetProgramName() As String
'Returns the value of the "Comboboxprogram" control on the
'"Main" form if one exists, otherwise returns an empty string
On Error GoTo err_GetProgramName

If IsNull(Forms!Main!Comboboxprogram) Then
GetProgramName = ""
Else
GetProgramName = Forms!Main!Comboboxprogram
End If

Exit_GetProgramName:
Exit Function

err_GetProgramName:
GetProgramName = ""

End Function


Then, in your "Graph Query" query, replace the reference to the "Main"
form's Control with a reference to the Function (don't forget to include the
set of empty parenthesis after the Function name).

So, this:
HAVING ((([General Info].[Program Name])=[Forms]![Main]![Comboboxprogram]));

Becomes this:
HAVING ((([General Info].[Program Name])= GetProgramName() ));


However, I'm still not sure why your crosstab query is based on a grouping
query. So, you may want to consider changing the "Graph Query" query to this
(I removed some unnecessary parenthesis):
SELECT [General Info].[Program Name], [General Info].[Place Found], [General
Info].Severity
FROM [General Info]
WHERE [General Info].[Program Name]=GetProgramName();


I've tested this in Access 2000 under Windows 2000, and it works. By which
I mean that my graph, using your crosstab query SQL, shows only the data it
is supposed to, based on the value of the ComboBox on the "Main" form. Of
course, I'm not sure my data is representative of yours, and even if it is,
I'm not sure exactly what results you are expecting. Then, there's still the
X and Y axis issue, but I think that will be easier to fix once the rest is
functioning properly.


Over the next three days I won't be able to respond, but I hope I've given
you some more ideas to work with in the meantime. Please post back with your
progress, and I'll be able to reply on Monday (if not later today).

-Michael

Hi Michael-

So I created and ran a query like you said and that part works perfectly.
The query is limited to what I have in the combo box.

Here is the SQL for the query that produces the result of what I want:

SELECT [General Info].[Program Name], [General Info].[Place Found], [General
Info].Severity
FROM [General Info]
GROUP BY [General Info].[Program Name], [General Info].[Place Found],
[General Info].Severity
HAVING ((([General Info].[Program Name])=[Forms]![Main]![Comboboxprogram]));

Notes:
1. “General Info†is the table I am pulling this data from.
2. “Program Nameâ€, “Place Found†and “Severity†are the fields in the table
“General Info†that I want
3. “Main†is the form the combo box is on that drives this query
4. “Comboboxprogram†is the combo box that is used as the query's critieria.


OK so the graph I have has this as the code in the row source currently:


TRANSFORM Count(*) AS [Count] SELECT [Place Found] FROM [Graph Query]
GROUP BY [Place Found] PIVOT [Severity];


"Graph Query" is the query mentioned above.

I tried replacing the current code in the record source with the SQL but I
am missing some VBA nomenclature because what it does is it makes the X axis
value = the “Program Name†and puts both “Severity†and “Place Found†on the
Y axis. And there is not data being shown.

I want the X axis to be “place foundâ€, the Y axis to be “severity†and the
graph to only show data ( data relating to a specific program name) based on
what is pulled down in the “comboboxprogramâ€. Bar chart style.

Does any of this help you in helping me? I know I am doing the VBA stuff
wrong in the row source of the graph...

Thanks again.

Hi. I followed your instructions and put my names into the places where you
had the placeholders but the report is still showing all the records, not
just the ones selected in the combobox. Any other things i might try?
TIA!

Hi Jenn.

If I understand correctly, if you just open the report by itself, it shows
all records, but you want to limit it to just those records for a selected
Program Name. A good way to do this is to have a CommandButton on the form
that the user can click to open the report after selecting a Program Name
from the ComboBox, and to only allow the user to open the report in this
manner. In the Click event for the CommandButton, you would have something
like this (watch for line wrap):
DoCmd.OpenReport "ReportName",acViewPreview,,"[ProgramNameField] = " &
Chr(34) & Forms("FormName").Controls("ComboBoxName") & Chr(34)

In the above, be sure to replace ReportName, ProgramNameField, FormName, and
ComboBoxName with the names of your own objects. Also, I'm assuming that
Program Name is a string. If not, remove both instances of "& Chr(34)".

To have the selected ProgramName appear in the title of the report, insert a
TextBox in the Report Header, and set its Control Source to:
="Selected Program Name: " & [Forms]("FormName").[Controls]("ComboBoxName")

You will probably want to add additional code that enables/disables the
form's CommandButton depending on whether a Program Name has been selected,
as you may get errors or a blank report if the user attempts to open the
report with no Program Name selected.

-Michael


:

I am trying to make a report using the graph wizard. What I have is the
following fields I want to use in the Graph:
1) Program Name
2) Place Found
3) Risk Severity
What I want the graph to do is this:
I want “Place Found†to be on the X axis, I want “Risk Severity†to be
counted on the Y axis. Ok so I followed the wizard and did this perfectly.
Here is the problem. I want to be able to select a “Program Name†from a
pulldown on a form and then have the graph report show the “Program Nameâ€
selected as the title and then “Place Found†and “Risk Severity†data
corresponding only to that selected “Program Name to be shown on the graph.
I have a control on a form that allows the user to select a “program nameâ€
from a pulldown and then it produces a query with just the info for that
program name but I cannot 1) get the graph to produce the “Program Name†as
either the title or text in the page header and 2) get the report to run even
though I use that query as the data for the report. I hope this question is
somewhat understandable! Any help would be great!
TIA!

 

[Guest]

Michael,
Hi. Thanks for responding so quickly. I havent had a chance to try out the
suggestions in the last two posts so I will do that and post my
results/issues. I am by no means an access expert-just the person in the
group who knows the most about access. I would say a intermediate user. So
when you ask questions as to why I used a specific type query the answer is
basically because it seemed to work or it is what I have done in the past. I
am going to take a few days to review and try what you have posted and then
reply-I don't want to waste your time.
I really appreciate all of your help on this!
Will post in a few days.
Thanks again.

Jenn,

I thought of some more info I'd like to have:

7. Please post some sample data you expect to be returned by the crosstab
query.


Also, I've been having trouble getting a crosstab query to work if a
reference to a control on a form is involved. This may be why your report is
showing no data. So I did the following instead.

Place the following code in a Module (but not a Class Module or one attached
to a Form). If you're unsure how to do this, just create a new Module by
showing the Modules in the main Access Database window, clicking New, and
pasting in everything below from "Public Function" through "End Function":

Public Function GetProgramName() As String
'Returns the value of the "Comboboxprogram" control on the
'"Main" form if one exists, otherwise returns an empty string
On Error GoTo err_GetProgramName

If IsNull(Forms!Main!Comboboxprogram) Then
GetProgramName = ""
Else
GetProgramName = Forms!Main!Comboboxprogram
End If

Exit_GetProgramName:
Exit Function

err_GetProgramName:
GetProgramName = ""

End Function


Then, in your "Graph Query" query, replace the reference to the "Main"
form's Control with a reference to the Function (don't forget to include the
set of empty parenthesis after the Function name).

So, this:
HAVING ((([General Info].[Program Name])=[Forms]![Main]![Comboboxprogram]));

Becomes this:
HAVING ((([General Info].[Program Name])= GetProgramName() ));


However, I'm still not sure why your crosstab query is based on a grouping
query. So, you may want to consider changing the "Graph Query" query to this
(I removed some unnecessary parenthesis):
SELECT [General Info].[Program Name], [General Info].[Place Found], [General
Info].Severity
FROM [General Info]
WHERE [General Info].[Program Name]=GetProgramName();


I've tested this in Access 2000 under Windows 2000, and it works. By which
I mean that my graph, using your crosstab query SQL, shows only the data it
is supposed to, based on the value of the ComboBox on the "Main" form. Of
course, I'm not sure my data is representative of yours, and even if it is,
I'm not sure exactly what results you are expecting. Then, there's still the
X and Y axis issue, but I think that will be easier to fix once the rest is
functioning properly.


Over the next three days I won't be able to respond, but I hope I've given
you some more ideas to work with in the meantime. Please post back with your
progress, and I'll be able to reply on Monday (if not later today).

-Michael

Hi Michael-

So I created and ran a query like you said and that part works perfectly.
The query is limited to what I have in the combo box.

Here is the SQL for the query that produces the result of what I want:

SELECT [General Info].[Program Name], [General Info].[Place Found], [General
Info].Severity
FROM [General Info]
GROUP BY [General Info].[Program Name], [General Info].[Place Found],
[General Info].Severity
HAVING ((([General Info].[Program Name])=[Forms]![Main]![Comboboxprogram]));

Notes:
1. “General Info†is the table I am pulling this data from.
2. “Program Nameâ€, “Place Found†and “Severity†are the fields in the table
“General Info†that I want
3. “Main†is the form the combo box is on that drives this query
4. “Comboboxprogram†is the combo box that is used as the query's critieria.


OK so the graph I have has this as the code in the row source currently:


TRANSFORM Count(*) AS [Count] SELECT [Place Found] FROM [Graph Query]
GROUP BY [Place Found] PIVOT [Severity];


"Graph Query" is the query mentioned above.

I tried replacing the current code in the record source with the SQL but I
am missing some VBA nomenclature because what it does is it makes the X axis
value = the “Program Name†and puts both “Severity†and “Place Found†on the
Y axis. And there is not data being shown.

I want the X axis to be “place foundâ€, the Y axis to be “severity†and the
graph to only show data ( data relating to a specific program name) based on
what is pulled down in the “comboboxprogramâ€. Bar chart style.

Does any of this help you in helping me? I know I am doing the VBA stuff
wrong in the row source of the graph...

Thanks again.

Hi. I followed your instructions and put my names into the places where you
had the placeholders but the report is still showing all the records, not
just the ones selected in the combobox. Any other things i might try?
TIA!

:

Hi Jenn.

If I understand correctly, if you just open the report by itself, it shows
all records, but you want to limit it to just those records for a selected
Program Name. A good way to do this is to have a CommandButton on the form
that the user can click to open the report after selecting a Program Name
from the ComboBox, and to only allow the user to open the report in this
manner. In the Click event for the CommandButton, you would have something
like this (watch for line wrap):
DoCmd.OpenReport "ReportName",acViewPreview,,"[ProgramNameField] = " &
Chr(34) & Forms("FormName").Controls("ComboBoxName") & Chr(34)

In the above, be sure to replace ReportName, ProgramNameField, FormName, and
ComboBoxName with the names of your own objects. Also, I'm assuming that
Program Name is a string. If not, remove both instances of "& Chr(34)".

To have the selected ProgramName appear in the title of the report, insert a
TextBox in the Report Header, and set its Control Source to:
="Selected Program Name: " & [Forms]("FormName").[Controls]("ComboBoxName")

You will probably want to add additional code that enables/disables the
form's CommandButton depending on whether a Program Name has been selected,
as you may get errors or a blank report if the user attempts to open the
report with no Program Name selected.

-Michael


:

I am trying to make a report using the graph wizard. What I have is the
following fields I want to use in the Graph:
1) Program Name
2) Place Found
3) Risk Severity
What I want the graph to do is this:
I want “Place Found†to be on the X axis, I want “Risk Severity†to be
counted on the Y axis. Ok so I followed the wizard and did this perfectly.
Here is the problem. I want to be able to select a “Program Name†from a
pulldown on a form and then have the graph report show the “Program Nameâ€
selected as the title and then “Place Found†and “Risk Severity†data
corresponding only to that selected “Program Name to be shown on the graph.
I have a control on a form that allows the user to select a “program nameâ€
from a pulldown and then it produces a query with just the info for that
program name but I cannot 1) get the graph to produce the “Program Name†as
either the title or text in the page header and 2) get the report to run even
though I use that query as the data for the report. I hope this question is
somewhat understandable! Any help would be great!
TIA!

 

[Guest]

Michael,
First of all thanks for all the help! Secondly, I realized I am not using a
crosstab query. Could this be the real problem??? I don’t know how to use
these kinds of queries!

BTW- I have no problem sending you this database since I only have test data
if you want to see it. I don’t think it is that big since it is just a few
tables and queries and not much data.

OK here are the answers to your questions:


1. What Data Type is the "Severity" field (I assume "Program Name" and
"Place Found" are String types)?
Answer:
There is a main table called “General Infoâ€. “Severityâ€, “Program Nameâ€
and “Place Found†are all captured in this table. Both “Severity†and
“Program Name†are lookup values from two other tables. (To limit what a
person can put into this field). Examples are below:

Severity= text (50 character default) - “Severity†comes from a table titled
“severityâ€. It is then a lookup value in the “General Info†table.
Program Name = text (50 character default)- “Program Name†comes from a
table title “Program Nameâ€. It is then a lookup value in the “General Infoâ€
table.
Place Found = text (50 character default) in the “General Info†table

2. Sample data from the "General Info" table.

Severity= it is a selection in the general info table values are one of
three text descriptions: “highâ€, “mediumâ€, or “lowâ€. These text values are
typed into the “Severity†table and then a combobox in the “General Infoâ€
table allows the user to select one of these values.

Program Name = it is a selection in the general info table values are one of
Four text descriptions: “Program Aâ€, “Program Bâ€, “Program C†or “Program Dâ€.
These text values are typed into the “Program Name†table and then a combobox
in the “General Info†table allows the user to select one of these values.

Place Found = “Office A†(50 character default) User just types any string
in here.


3. Sample results from the "Graph Query" query.

You double click on “Graph Query†and a message box pops up and asks you to
type in a Program Name value. I type in “Program A†and get the following:
(3 columns- First column = program name, second column = place found, third
column = severity)
Result looks like:
Program Name Place Found Severity
Program A Fab/Assy High
Program A Fab/Assy Low
Program B Office 1 Low

So that works.


4. What happens if you paste the SQL of your Crosstab query into a new
query, and just run it? Do you get any data? It will be a lot easier to
diagnose the problem if we get this to work correctly as a query before
plugging the SQL into the Chart object.


Oh no! I did not use a cross tab query! Could that be the problem?
I was able to paste the SQL into a regular query and it worked the same as
above mentioned in #3.

5. Please describe in more detail how the chart should look. Use your own
Field Names and some sample values. Also, if by any chance you can find any
charts available on the Web that look similar to what you want, please
provide a link.
Can I send you an excel pivot of what I want the graph to look like?


6. Why are you basing a Crosstab query on a query that is grouped?
Shouldn't the Crosstab query be doing the grouping for you, rather than the
"Graph Query" query?
Umm I have never used a cross tab. Any help with this would be great!

Thanks again. Now I need to review your other post...

Michael,
Hi. Thanks for responding so quickly. I havent had a chance to try out the
suggestions in the last two posts so I will do that and post my
results/issues. I am by no means an access expert-just the person in the
group who knows the most about access. I would say a intermediate user. So
when you ask questions as to why I used a specific type query the answer is
basically because it seemed to work or it is what I have done in the past. I
am going to take a few days to review and try what you have posted and then
reply-I don't want to waste your time.
I really appreciate all of your help on this!
Will post in a few days.
Thanks again.

Jenn,

I thought of some more info I'd like to have:

7. Please post some sample data you expect to be returned by the crosstab
query.


Also, I've been having trouble getting a crosstab query to work if a
reference to a control on a form is involved. This may be why your report is
showing no data. So I did the following instead.

Place the following code in a Module (but not a Class Module or one attached
to a Form). If you're unsure how to do this, just create a new Module by
showing the Modules in the main Access Database window, clicking New, and
pasting in everything below from "Public Function" through "End Function":

Public Function GetProgramName() As String
'Returns the value of the "Comboboxprogram" control on the
'"Main" form if one exists, otherwise returns an empty string
On Error GoTo err_GetProgramName

If IsNull(Forms!Main!Comboboxprogram) Then
GetProgramName = ""
Else
GetProgramName = Forms!Main!Comboboxprogram
End If

Exit_GetProgramName:
Exit Function

err_GetProgramName:
GetProgramName = ""

End Function


Then, in your "Graph Query" query, replace the reference to the "Main"
form's Control with a reference to the Function (don't forget to include the
set of empty parenthesis after the Function name).

So, this:
HAVING ((([General Info].[Program Name])=[Forms]![Main]![Comboboxprogram]));

Becomes this:
HAVING ((([General Info].[Program Name])= GetProgramName() ));


However, I'm still not sure why your crosstab query is based on a grouping
query. So, you may want to consider changing the "Graph Query" query to this
(I removed some unnecessary parenthesis):
SELECT [General Info].[Program Name], [General Info].[Place Found], [General
Info].Severity
FROM [General Info]
WHERE [General Info].[Program Name]=GetProgramName();


I've tested this in Access 2000 under Windows 2000, and it works. By which
I mean that my graph, using your crosstab query SQL, shows only the data it
is supposed to, based on the value of the ComboBox on the "Main" form. Of
course, I'm not sure my data is representative of yours, and even if it is,
I'm not sure exactly what results you are expecting. Then, there's still the
X and Y axis issue, but I think that will be easier to fix once the rest is
functioning properly.


Over the next three days I won't be able to respond, but I hope I've given
you some more ideas to work with in the meantime. Please post back with your
progress, and I'll be able to reply on Monday (if not later today).

-Michael

Hi Michael-

So I created and ran a query like you said and that part works perfectly.
The query is limited to what I have in the combo box.

Here is the SQL for the query that produces the result of what I want:

SELECT [General Info].[Program Name], [General Info].[Place Found], [General
Info].Severity
FROM [General Info]
GROUP BY [General Info].[Program Name], [General Info].[Place Found],
[General Info].Severity
HAVING ((([General Info].[Program Name])=[Forms]![Main]![Comboboxprogram]));

Notes:
1. “General Info†is the table I am pulling this data from.
2. “Program Nameâ€, “Place Found†and “Severity†are the fields in the table
“General Info†that I want
3. “Main†is the form the combo box is on that drives this query
4. “Comboboxprogram†is the combo box that is used as the query's critieria.


OK so the graph I have has this as the code in the row source currently:


TRANSFORM Count(*) AS [Count] SELECT [Place Found] FROM [Graph Query]
GROUP BY [Place Found] PIVOT [Severity];


"Graph Query" is the query mentioned above.

I tried replacing the current code in the record source with the SQL but I
am missing some VBA nomenclature because what it does is it makes the X axis
value = the “Program Name†and puts both “Severity†and “Place Found†on the
Y axis. And there is not data being shown.

I want the X axis to be “place foundâ€, the Y axis to be “severity†and the
graph to only show data ( data relating to a specific program name) based on
what is pulled down in the “comboboxprogramâ€. Bar chart style.

Does any of this help you in helping me? I know I am doing the VBA stuff
wrong in the row source of the graph...

Thanks again.


:

Hi. I followed your instructions and put my names into the places where you
had the placeholders but the report is still showing all the records, not
just the ones selected in the combobox. Any other things i might try?
TIA!

:

Hi Jenn.

If I understand correctly, if you just open the report by itself, it shows
all records, but you want to limit it to just those records for a selected
Program Name. A good way to do this is to have a CommandButton on the form
that the user can click to open the report after selecting a Program Name
from the ComboBox, and to only allow the user to open the report in this
manner. In the Click event for the CommandButton, you would have something
like this (watch for line wrap):
DoCmd.OpenReport "ReportName",acViewPreview,,"[ProgramNameField] = " &
Chr(34) & Forms("FormName").Controls("ComboBoxName") & Chr(34)

In the above, be sure to replace ReportName, ProgramNameField, FormName, and
ComboBoxName with the names of your own objects. Also, I'm assuming that
Program Name is a string. If not, remove both instances of "& Chr(34)".

To have the selected ProgramName appear in the title of the report, insert a
TextBox in the Report Header, and set its Control Source to:
="Selected Program Name: " & [Forms]("FormName").[Controls]("ComboBoxName")

You will probably want to add additional code that enables/disables the
form's CommandButton depending on whether a Program Name has been selected,
as you may get errors or a blank report if the user attempts to open the
report with no Program Name selected.

-Michael


:

I am trying to make a report using the graph wizard. What I have is the
following fields I want to use in the Graph:
1) Program Name
2) Place Found
3) Risk Severity
What I want the graph to do is this:
I want “Place Found†to be on the X axis, I want “Risk Severity†to be
counted on the Y axis. Ok so I followed the wizard and did this perfectly.
Here is the problem. I want to be able to select a “Program Name†from a
pulldown on a form and then have the graph report show the “Program Nameâ€
selected as the title and then “Place Found†and “Risk Severity†data
corresponding only to that selected “Program Name to be shown on the graph.
I have a control on a form that allows the user to select a “program nameâ€
from a pulldown and then it produces a query with just the info for that
program name but I cannot 1) get the graph to produce the “Program Name†as
either the title or text in the page header and 2) get the report to run even
though I use that query as the data for the report. I hope this question is
somewhat understandable! Any help would be great!
TIA!

 

[Guest]

Jenn,

Any query in which the SQL starts with "TRANSFORM" is, by definition, a
crosstab query (if you look at the design grid of your such query, you will
see that one of the rows is called "Crosstab"). I'm no expert with them, but
since they are grouping queries, they are normally (in my experience) based
on raw data. So, if possible, please provide answers to my previous
questions, numbers 4 and 7, which refer to your query that starts with
"TRANSFORM" being the crosstab query.

You can send the files in question to (e-mail address removed) .
However:
1. Remove "no-spam" from my e-mail address.
2. Be sure the .mdb file is in Access 2000 format (or earlier), as that's
the latest version I have.
3. Zip the files, or change the extension of the .mdb file to .txt (Hotmail
won't allow .mdb files to be downloaded).

-Michael

Michael,
First of all thanks for all the help! Secondly, I realized I am not using a
crosstab query. Could this be the real problem??? I don’t know how to use
these kinds of queries!

BTW- I have no problem sending you this database since I only have test data
if you want to see it. I don’t think it is that big since it is just a few
tables and queries and not much data.

OK here are the answers to your questions:


1. What Data Type is the "Severity" field (I assume "Program Name" and
"Place Found" are String types)?
Answer:
There is a main table called “General Infoâ€. “Severityâ€, “Program Nameâ€
and “Place Found†are all captured in this table. Both “Severity†and
“Program Name†are lookup values from two other tables. (To limit what a
person can put into this field). Examples are below:

Severity= text (50 character default) - “Severity†comes from a table titled
“severityâ€. It is then a lookup value in the “General Info†table.
Program Name = text (50 character default)- “Program Name†comes from a
table title “Program Nameâ€. It is then a lookup value in the “General Infoâ€
table.
Place Found = text (50 character default) in the “General Info†table

2. Sample data from the "General Info" table.

Severity= it is a selection in the general info table values are one of
three text descriptions: “highâ€, “mediumâ€, or “lowâ€. These text values are
typed into the “Severity†table and then a combobox in the “General Infoâ€
table allows the user to select one of these values.

Program Name = it is a selection in the general info table values are one of
Four text descriptions: “Program Aâ€, “Program Bâ€, “Program C†or “Program Dâ€.
These text values are typed into the “Program Name†table and then a combobox
in the “General Info†table allows the user to select one of these values.

Place Found = “Office A†(50 character default) User just types any string
in here.


3. Sample results from the "Graph Query" query.

You double click on “Graph Query†and a message box pops up and asks you to
type in a Program Name value. I type in “Program A†and get the following:
(3 columns- First column = program name, second column = place found, third
column = severity)
Result looks like:
Program Name Place Found Severity
Program A Fab/Assy High
Program A Fab/Assy Low
Program B Office 1 Low

So that works.


4. What happens if you paste the SQL of your Crosstab query into a new
query, and just run it? Do you get any data? It will be a lot easier to
diagnose the problem if we get this to work correctly as a query before
plugging the SQL into the Chart object.


Oh no! I did not use a cross tab query! Could that be the problem?
I was able to paste the SQL into a regular query and it worked the same as
above mentioned in #3.

5. Please describe in more detail how the chart should look. Use your own
Field Names and some sample values. Also, if by any chance you can find any
charts available on the Web that look similar to what you want, please
provide a link.
Can I send you an excel pivot of what I want the graph to look like?


6. Why are you basing a Crosstab query on a query that is grouped?
Shouldn't the Crosstab query be doing the grouping for you, rather than the
"Graph Query" query?
Umm I have never used a cross tab. Any help with this would be great!

Thanks again. Now I need to review your other post...

Michael,
Hi. Thanks for responding so quickly. I havent had a chance to try out the
suggestions in the last two posts so I will do that and post my
results/issues. I am by no means an access expert-just the person in the
group who knows the most about access. I would say a intermediate user. So
when you ask questions as to why I used a specific type query the answer is
basically because it seemed to work or it is what I have done in the past. I
am going to take a few days to review and try what you have posted and then
reply-I don't want to waste your time.
I really appreciate all of your help on this!
Will post in a few days.
Thanks again.

Jenn,

I thought of some more info I'd like to have:

7. Please post some sample data you expect to be returned by the crosstab
query.


Also, I've been having trouble getting a crosstab query to work if a
reference to a control on a form is involved. This may be why your report is
showing no data. So I did the following instead.

Place the following code in a Module (but not a Class Module or one attached
to a Form). If you're unsure how to do this, just create a new Module by
showing the Modules in the main Access Database window, clicking New, and
pasting in everything below from "Public Function" through "End Function":

Public Function GetProgramName() As String
'Returns the value of the "Comboboxprogram" control on the
'"Main" form if one exists, otherwise returns an empty string
On Error GoTo err_GetProgramName

If IsNull(Forms!Main!Comboboxprogram) Then
GetProgramName = ""
Else
GetProgramName = Forms!Main!Comboboxprogram
End If

Exit_GetProgramName:
Exit Function

err_GetProgramName:
GetProgramName = ""

End Function


Then, in your "Graph Query" query, replace the reference to the "Main"
form's Control with a reference to the Function (don't forget to include the
set of empty parenthesis after the Function name).

So, this:
HAVING ((([General Info].[Program Name])=[Forms]![Main]![Comboboxprogram]));

Becomes this:
HAVING ((([General Info].[Program Name])= GetProgramName() ));


However, I'm still not sure why your crosstab query is based on a grouping
query. So, you may want to consider changing the "Graph Query" query to this
(I removed some unnecessary parenthesis):
SELECT [General Info].[Program Name], [General Info].[Place Found], [General
Info].Severity
FROM [General Info]
WHERE [General Info].[Program Name]=GetProgramName();


I've tested this in Access 2000 under Windows 2000, and it works. By which
I mean that my graph, using your crosstab query SQL, shows only the data it
is supposed to, based on the value of the ComboBox on the "Main" form. Of
course, I'm not sure my data is representative of yours, and even if it is,
I'm not sure exactly what results you are expecting. Then, there's still the
X and Y axis issue, but I think that will be easier to fix once the rest is
functioning properly.


Over the next three days I won't be able to respond, but I hope I've given
you some more ideas to work with in the meantime. Please post back with your
progress, and I'll be able to reply on Monday (if not later today).

-Michael



:

Hi Michael-

So I created and ran a query like you said and that part works perfectly.
The query is limited to what I have in the combo box.

Here is the SQL for the query that produces the result of what I want:

SELECT [General Info].[Program Name], [General Info].[Place Found], [General
Info].Severity
FROM [General Info]
GROUP BY [General Info].[Program Name], [General Info].[Place Found],
[General Info].Severity
HAVING ((([General Info].[Program Name])=[Forms]![Main]![Comboboxprogram]));

Notes:
1. “General Info†is the table I am pulling this data from.
2. “Program Nameâ€, “Place Found†and “Severity†are the fields in the table
“General Info†that I want
3. “Main†is the form the combo box is on that drives this query
4. “Comboboxprogram†is the combo box that is used as the query's critieria.


OK so the graph I have has this as the code in the row source currently:


TRANSFORM Count(*) AS [Count] SELECT [Place Found] FROM [Graph Query]
GROUP BY [Place Found] PIVOT [Severity];


"Graph Query" is the query mentioned above.

I tried replacing the current code in the record source with the SQL but I
am missing some VBA nomenclature because what it does is it makes the X axis
value = the “Program Name†and puts both “Severity†and “Place Found†on the
Y axis. And there is not data being shown.

I want the X axis to be “place foundâ€, the Y axis to be “severity†and the
graph to only show data ( data relating to a specific program name) based on
what is pulled down in the “comboboxprogramâ€. Bar chart style.

Does any of this help you in helping me? I know I am doing the VBA stuff
wrong in the row source of the graph...

Thanks again.


:

Hi. I followed your instructions and put my names into the places where you
had the placeholders but the report is still showing all the records, not
just the ones selected in the combobox. Any other things i might try?
TIA!

:

Hi Jenn.

If I understand correctly, if you just open the report by itself, it shows
all records, but you want to limit it to just those records for a selected
Program Name. A good way to do this is to have a CommandButton on the form
that the user can click to open the report after selecting a Program Name
from the ComboBox, and to only allow the user to open the report in this
manner. In the Click event for the CommandButton, you would have something
like this (watch for line wrap):
DoCmd.OpenReport "ReportName",acViewPreview,,"[ProgramNameField] = " &
Chr(34) & Forms("FormName").Controls("ComboBoxName") & Chr(34)

In the above, be sure to replace ReportName, ProgramNameField, FormName, and
ComboBoxName with the names of your own objects. Also, I'm assuming that
Program Name is a string. If not, remove both instances of "& Chr(34)".

To have the selected ProgramName appear in the title of the report, insert a
TextBox in the Report Header, and set its Control Source to:
="Selected Program Name: " & [Forms]("FormName").[Controls]("ComboBoxName")

You will probably want to add additional code that enables/disables the
form's CommandButton depending on whether a Program Name has been selected,
as you may get errors or a blank report if the user attempts to open the
report with no Program Name selected.

-Michael


:

I am trying to make a report using the graph wizard. What I have is the
following fields I want to use in the Graph:
1) Program Name
2) Place Found
3) Risk Severity
What I want the graph to do is this:
I want “Place Found†to be on the X axis, I want “Risk Severity†to be
counted on the Y axis. Ok so I followed the wizard and did this perfectly.
Here is the problem. I want to be able to select a “Program Name†from a
pulldown on a form and then have the graph report show the “Program Nameâ€
selected as the title and then “Place Found†and “Risk Severity†data
corresponding only to that selected “Program Name to be shown on the graph.
I have a control on a form that allows the user to select a “program nameâ€
from a pulldown and then it produces a query with just the info for that
program name but I cannot 1) get the graph to produce the “Program Name†as
either the title or text in the page header and 2) get the report to run even
though I use that query as the data for the report. I hope this question is
somewhat understandable! Any help would be great!
TIA!