Using input parameter query for creating charts

  • Thread starter Thread starter Michelle
  • Start date Start date
M

Michelle

Hi All,

I would like to create a chart based on a query where the
user has to enter a name. For example, I would like to
track the total number of audits peformed by Company
XYZ. Because I have multiple companies, I don't want to
have a create a specific chart for each one, I want to
have one generic chart where I can enter the Company name
each time and it will pull in the data from the query
accordingly. So what I have done is create a query and
under criteria in the company name column I have =[Enter
name of company:]. But, I get an error message
indicating that it doesn't recognize my input parameter
as a field.

Please HELP!!!

Michelle
 
I would use a combo box of companies on a form rather than a parameter
prompt. Your criteria would then look like
Forms!frmSelectCompany!cboCompany
You may need to set the Query|Parameters in your chart's Row Source query.
 
Duane,

I had tried setting a form as well, not matter what I do,
it doesn't like the fact that parameters such as the one
you mentioned below is passed into the query. The only
thing that works is if I put the ciriteria as ="ABC"
where ABC is the ompany name. But I don't want to have to
manually modify every single time.

Any help would be appreciated.

Michelle
-----Original Message-----
I would use a combo box of companies on a form rather than a parameter
prompt. Your criteria would then look like
Forms!frmSelectCompany!cboCompany
You may need to set the Query|Parameters in your chart's Row Source query.

--
Duane Hookom
MS Access MVP


Michelle said:
Hi All,

I would like to create a chart based on a query where the
user has to enter a name. For example, I would like to
track the total number of audits peformed by Company
XYZ. Because I have multiple companies, I don't want to
have a create a specific chart for each one, I want to
have one generic chart where I can enter the Company name
each time and it will pull in the data from the query
accordingly. So what I have done is create a query and
under criteria in the company name column I have = [Enter
name of company:]. But, I get an error message
indicating that it doesn't recognize my input parameter
as a field.

Please HELP!!!

Michelle
Click to expand...


.
Click to expand...
 
Open the row source property of the chart. I expect that it has created a
crosstab query. You must enter the data types of your parameters ie:
Query|Parameters and then enter:

Forms!frmSelectCompany!cboCompany Text (or whatever)
--
Duane Hookom
MS Access MVP


Michelle said:
Duane,

I had tried setting a form as well, not matter what I do,
it doesn't like the fact that parameters such as the one
you mentioned below is passed into the query. The only
thing that works is if I put the ciriteria as ="ABC"
where ABC is the ompany name. But I don't want to have to
manually modify every single time.

Any help would be appreciated.

Michelle
-----Original Message-----
I would use a combo box of companies on a form rather than a parameter
prompt. Your criteria would then look like
Forms!frmSelectCompany!cboCompany
You may need to set the Query|Parameters in your chart's Row Source query.

--
Duane Hookom
MS Access MVP


Michelle said:
Hi All,

I would like to create a chart based on a query where the
user has to enter a name. For example, I would like to
track the total number of audits peformed by Company
XYZ. Because I have multiple companies, I don't want to
have a create a specific chart for each one, I want to
have one generic chart where I can enter the Company name
each time and it will pull in the data from the query
accordingly. So what I have done is create a query and
under criteria in the company name column I have = [Enter
name of company:]. But, I get an error message
indicating that it doesn't recognize my input parameter
as a field.

Please HELP!!!

Michelle
Click to expand...


.
Click to expand...
Click to expand...
 
Duane,

This worked perfectly. Thank you so much for your help!!!

Michelle
-----Original Message-----
I would use a combo box of companies on a form rather than a parameter
prompt. Your criteria would then look like
Forms!frmSelectCompany!cboCompany
You may need to set the Query|Parameters in your chart's Row Source query.

--
Duane Hookom
MS Access MVP


Michelle said:
Hi All,

I would like to create a chart based on a query where the
user has to enter a name. For example, I would like to
track the total number of audits peformed by Company
XYZ. Because I have multiple companies, I don't want to
have a create a specific chart for each one, I want to
have one generic chart where I can enter the Company name
each time and it will pull in the data from the query
accordingly. So what I have done is create a query and
under criteria in the company name column I have = [Enter
name of company:]. But, I get an error message
indicating that it doesn't recognize my input parameter
as a field.

Please HELP!!!

Michelle
Click to expand...


.
Click to expand...
 
Now I have another problem:

The parameter is create for getting the audit name (which
are usually company names), but I want to graph base on
the number of observation vs category per audit name.
The problem is that I have multiple categories (major,
minor, etc), so in my table that tracks the observations
I can have 1 record for ABC with minor observation of
certain area of the copmany, the another for ABC with
major observation of another area. The graph is ouputted
correctly, but the same graph showing up 2 times in the
report for this example since I have 2 records. So the
number of time it appears is dependent on the number of
records it has in the query.

Please see my SQL query below...I need help!!!!

PARAMETERS [Reports]![Statistical Analysis (Individual)]!
[Audit Name] Text ( 255 );
TRANSFORM Count([Audit Observations].[Audit Name]) AS
[CountOfObservation #]
SELECT [Audit Observations].[Audit Name]
FROM [Audit Observations]
WHERE ((([Audit Observations].[Audit Name])="Schering
Plough"))
GROUP BY [Audit Observations].[Audit Name]
PIVOT [Audit Observations].Category;
-----Original Message-----
Duane,

This worked perfectly. Thank you so much for your help!!!
Michelle
-----Original Message-----
I would use a combo box of companies on a form rather than a parameter
prompt. Your criteria would then look like
Forms!frmSelectCompany!cboCompany
You may need to set the Query|Parameters in your
Click to expand...
chart's
Row Source query.
--
Duane Hookom
MS Access MVP


Michelle said:
Hi All,

I would like to create a chart based on a query where the
user has to enter a name. For example, I would like to
track the total number of audits peformed by Company
XYZ. Because I have multiple companies, I don't want to
have a create a specific chart for each one, I want to
have one generic chart where I can enter the Company name
each time and it will pull in the data from the query
accordingly. So what I have done is create a query and
under criteria in the company name column I have = [Enter
name of company:]. But, I get an error message
indicating that it doesn't recognize my input parameter
as a field.

Please HELP!!!

Michelle
Click to expand...


.
Click to expand...
.
Click to expand...
 
If the graph shows up correctly but just repeated more than once, place the
graph in a group footer.

--
Duane Hookom
MS Access MVP


Michelle said:
Now I have another problem:

The parameter is create for getting the audit name (which
are usually company names), but I want to graph base on
the number of observation vs category per audit name.
The problem is that I have multiple categories (major,
minor, etc), so in my table that tracks the observations
I can have 1 record for ABC with minor observation of
certain area of the copmany, the another for ABC with
major observation of another area. The graph is ouputted
correctly, but the same graph showing up 2 times in the
report for this example since I have 2 records. So the
number of time it appears is dependent on the number of
records it has in the query.

Please see my SQL query below...I need help!!!!

PARAMETERS [Reports]![Statistical Analysis (Individual)]!
[Audit Name] Text ( 255 );
TRANSFORM Count([Audit Observations].[Audit Name]) AS
[CountOfObservation #]
SELECT [Audit Observations].[Audit Name]
FROM [Audit Observations]
WHERE ((([Audit Observations].[Audit Name])="Schering
Plough"))
GROUP BY [Audit Observations].[Audit Name]
PIVOT [Audit Observations].Category;
-----Original Message-----
Duane,

This worked perfectly. Thank you so much for your help!!!
Michelle
-----Original Message-----
I would use a combo box of companies on a form rather than a parameter
prompt. Your criteria would then look like
Forms!frmSelectCompany!cboCompany
You may need to set the Query|Parameters in your
Click to expand...
chart's
Row Source query.
--
Duane Hookom
MS Access MVP


Hi All,

I would like to create a chart based on a query where the
user has to enter a name. For example, I would like to
track the total number of audits peformed by Company
XYZ. Because I have multiple companies, I don't want to
have a create a specific chart for each one, I want to
have one generic chart where I can enter the Company name
each time and it will pull in the data from the query
accordingly. So what I have done is create a query and
under criteria in the company name column I have = [Enter
name of company:]. But, I get an error message
indicating that it doesn't recognize my input parameter
as a field.

Please HELP!!!

Michelle


.
Click to expand...
.
Click to expand...
Click to expand...
 
Duane,

You are awesome. I thought it was something funny with my
SQL statement! Thanks!

Michelle
-----Original Message-----
If the graph shows up correctly but just repeated more than once, place the
graph in a group footer.

--
Duane Hookom
MS Access MVP


Michelle said:
Now I have another problem:

The parameter is create for getting the audit name (which
are usually company names), but I want to graph base on
the number of observation vs category per audit name.
The problem is that I have multiple categories (major,
minor, etc), so in my table that tracks the observations
I can have 1 record for ABC with minor observation of
certain area of the copmany, the another for ABC with
major observation of another area. The graph is ouputted
correctly, but the same graph showing up 2 times in the
report for this example since I have 2 records. So the
number of time it appears is dependent on the number of
records it has in the query.

Please see my SQL query below...I need help!!!!

PARAMETERS [Reports]![Statistical Analysis (Individual)]!
[Audit Name] Text ( 255 );
TRANSFORM Count([Audit Observations].[Audit Name]) AS
[CountOfObservation #]
SELECT [Audit Observations].[Audit Name]
FROM [Audit Observations]
WHERE ((([Audit Observations].[Audit Name])="Schering
Plough"))
GROUP BY [Audit Observations].[Audit Name]
PIVOT [Audit Observations].Category;
-----Original Message-----
Duane,

This worked perfectly. Thank you so much for your help!!!

Michelle
-----Original Message-----
I would use a combo box of companies on a form rather
than a parameter
prompt. Your criteria would then look like
Forms!frmSelectCompany!cboCompany
You may need to set the Query|Parameters in your chart's
Row Source query.

--
Duane Hookom
MS Access MVP


Hi All,

I would like to create a chart based on a query where
the
user has to enter a name. For example, I would
Click to expand...
like
to
track the total number of audits peformed by Company
XYZ. Because I have multiple companies, I don't want
to
have a create a specific chart for each one, I want to
have one generic chart where I can enter the Company
name
each time and it will pull in the data from the query
accordingly. So what I have done is create a query and
under criteria in the company name column I have =
[Enter
name of company:]. But, I get an error message
indicating that it doesn't recognize my input parameter
as a field.

Please HELP!!!

Michelle


.

.
Click to expand...
Click to expand...


.
Click to expand...
 
You simply need to declare the parameter in the Query
using the option Query / Parameters from the toolbar.
Make sure that it is spelled the same as in your query
design grid and that the appropriate format is declared
and all will be well

Regards

Brenda
-----Original Message-----
Duane,

I had tried setting a form as well, not matter what I do,
it doesn't like the fact that parameters such as the one
you mentioned below is passed into the query. The only
thing that works is if I put the ciriteria as ="ABC"
where ABC is the ompany name. But I don't want to have to
manually modify every single time.

Any help would be appreciated.

Michelle
-----Original Message-----
I would use a combo box of companies on a form rather than a parameter
prompt. Your criteria would then look like
Forms!frmSelectCompany!cboCompany
You may need to set the Query|Parameters in your
Click to expand...
chart's
Row Source query.
--
Duane Hookom
MS Access MVP


Michelle said:
Hi All,

I would like to create a chart based on a query where the
user has to enter a name. For example, I would like to
track the total number of audits peformed by Company
XYZ. Because I have multiple companies, I don't want to
have a create a specific chart for each one, I want to
have one generic chart where I can enter the Company name
each time and it will pull in the data from the query
accordingly. So what I have done is create a query and
under criteria in the company name column I have = [Enter
name of company:]. But, I get an error message
indicating that it doesn't recognize my input parameter
as a field.

Please HELP!!!

Michelle
Click to expand...


.
Click to expand...
.
Click to expand...
 
Back
Top