Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

graph/report wizard

Discussion in 'Information Technology' started by Jenn, Jul 28, 2006.

  1. Jenn

    Jenn
    Expand Collapse
    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!
     
  2. Loading...

    Similar Threads Forum Date
    The wonderful wizard of Sachkhand Blogs Oct 17, 2015

  3. Michael H

    Michael H
    Expand Collapse
    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


    "Jenn" wrote:

    > 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!
    >
     
  4. Jenn

    Jenn
    Expand Collapse
    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!

    "Michael H" wrote:

    > 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
    >
    >
    > "Jenn" wrote:
    >
    > > 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!
    > >
     
  5. Michael H

    Michael H
    Expand Collapse
    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



    "Jenn" wrote:

    > 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!
    >
    > "Michael H" wrote:
    >
    > > 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
    > >
    > >
    > > "Jenn" wrote:
    > >
    > > > 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!
    > > >
     
  6. Jenn

    Jenn
    Expand Collapse
    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.


    "Jenn" wrote:

    > 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!
    >
    > "Michael H" wrote:
    >
    > > 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
    > >
    > >
    > > "Jenn" wrote:
    > >
    > > > 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!
    > > >
     
  7. Michael H

    Michael H
    Expand Collapse
    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



    "Jenn" wrote:

    > 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.
    >
    >
    > "Jenn" wrote:
    >
    > > 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!
    > >
    > > "Michael H" wrote:
    > >
    > > > 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
    > > >
    > > >
    > > > "Jenn" wrote:
    > > >
    > > > > 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!
    > > > >
     
  8. Michael H

    Michael H
    Expand Collapse
    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



    "Jenn" wrote:

    > 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.
    >
    >
    > "Jenn" wrote:
    >
    > > 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!
    > >
    > > "Michael H" wrote:
    > >
    > > > 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
    > > >
    > > >
    > > > "Jenn" wrote:
    > > >
    > > > > 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!
    > > > >
     
  9. Jenn

    Jenn
    Expand Collapse
    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.

    "Michael H" wrote:

    > 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
    >
    >
    >
    > "Jenn" wrote:
    >
    > > 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.
    > >
    > >
    > > "Jenn" wrote:
    > >
    > > > 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!
    > > >
    > > > "Michael H" wrote:
    > > >
    > > > > 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
    > > > >
    > > > >
    > > > > "Jenn" wrote:
    > > > >
    > > > > > 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!
    > > > > >
     
  10. Jenn

    Jenn
    Expand Collapse
    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...

    "Jenn" wrote:

    > 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.
    >
    > "Michael H" wrote:
    >
    > > 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
    > >
    > >
    > >
    > > "Jenn" wrote:
    > >
    > > > 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.
    > > >
    > > >
    > > > "Jenn" wrote:
    > > >
    > > > > 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!
    > > > >
    > > > > "Michael H" wrote:
    > > > >
    > > > > > 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
    > > > > >
    > > > > >
    > > > > > "Jenn" wrote:
    > > > > >
    > > > > > > 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!
    > > > > > >
     
  11. Michael H

    Michael H
    Expand Collapse
    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 deep.no-spamthought.42@hotmail.com .
    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


    "Jenn" wrote:

    > 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...
    >
    > "Jenn" wrote:
    >
    > > 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.
    > >
    > > "Michael H" wrote:
    > >
    > > > 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
    > > >
    > > >
    > > >
    > > > "Jenn" wrote:
    > > >
    > > > > 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.
    > > > >
    > > > >
    > > > > "Jenn" wrote:
    > > > >
    > > > > > 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!
    > > > > >
    > > > > > "Michael H" wrote:
    > > > > >
    > > > > > > 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
    > > > > > >
    > > > > > >
    > > > > > > "Jenn" wrote:
    > > > > > >
    > > > > > > > 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!
    > > > > > > >
     

Share This Page