Combining two fileds of information in the legend of a bar chart.

J

Jeff

I have a database which includes a report with bar charts which works and
displays just fine but I need to combine the information in the legend for
each bar with data from two seperate fields.

Currently, the row source for one of my bar charts is as follows:
SELECT TOP 10 ([Assembly Number]) AS Expr1, (Sum([Pass Quantity])/(Sum([Pass
Quantity])+Sum([Fail Quantity]))*100) AS [FPY by Operation] FROM [metrics
problem qry] GROUP BY [metrics problem qry].[Assembly Number] HAVING
(((Sum([metrics problem qry].[Pass Quantity]))>0)) ORDER BY ((Sum([Pass
Quantity])/(Sum([Pass Quantity])+Sum([Fail Quantity]))*100)), [Assembly
Number];
Click to expand...

I have tried the following by adding the first 10 characters of the field
IDESC as follows:

SELECT TOP 10 ([Assembly Number] &"-"& Left([IDESC],10)) AS Expr1,
(Sum([Pass Quantity])/(Sum([Pass Quantity])+Sum([Fail Quantity]))*100) AS
[FPY by Operation] FROM [metrics problem qry] GROUP BY [metrics problem
qry].[Assembly Number] HAVING (((Sum([metrics problem qry].[Pass
Quantity]))>0)) ORDER BY ((Sum([Pass Quantity])/(Sum([Pass
Quantity])+Sum([Fail Quantity]))*100)), [Assembly Number];
Click to expand...

But I get an error message which states:

You tried to execute a query that does not include the specified
expression'[metrics problem qry].[Assembly Number] & "-"&Left([IDESC],10' as
part of an aggregate function
Click to expand...

The SQL of my query is:
SELECT [FPY Table].Date, [FPY Table].ID, [FPY Table].Operator, [FPY
Table].[Serial Number], [FPY Table].Operation, [FPY Table].[Assembly Number],
[FPY Table].[Pass Quantity], [FPY Table].LatePass, [FPY Table].[Fail
Quantity], [FPY Table].[Total Failure], [FPY Table].ProblemCode1, [FPY
Table].Rework, [FPY Table].[Part Number], [FPY Table].[Description of
Problem], [FPY Table].PassYield, [FPY Table].FailYield, [FPY Table].depos,
[FPY Table].multiprobs, [FPY Table].RejectNbr, [FPY Table].DTS, [FPY
Table].TermID, ONEBPCSF_IIM.IDESC, ([Assembly Number] & " - " &
Left([IDESC],10)) AS Expr2
FROM [FPY Table] INNER JOIN ONEBPCSF_IIM ON [FPY Table].[Assembly Number] =
ONEBPCSF_IIM.IPROD
GROUP BY [FPY Table].Date, [FPY Table].ID, [FPY Table].Operator, [FPY
Table].[Serial Number], [FPY Table].Operation, [FPY Table].[Assembly Number],
[FPY Table].[Pass Quantity], [FPY Table].LatePass, [FPY Table].[Fail
Quantity], [FPY Table].[Total Failure], [FPY Table].ProblemCode1, [FPY
Table].Rework, [FPY Table].[Part Number], [FPY Table].[Description of
Problem], [FPY Table].PassYield, [FPY Table].FailYield, [FPY Table].depos,
[FPY Table].multiprobs, [FPY Table].RejectNbr, [FPY Table].DTS, [FPY
Table].TermID, ONEBPCSF_IIM.IDESC, ([Assembly Number] & " - " &
Left([IDESC],10))
HAVING ((([FPY Table].Date) Between [forms]![print or view
reports]![StartDate] And [forms]![print or view reports]![EndDate]));
Click to expand...

What am I doing wrong and how can I get these two fields to be displayed for
each bar of my bar chart?

thank you.
 
K

KARL DEWEY

Try it this way ---
SELECT TOP 10 ([Assembly Number] &"-"& Left([IDESC],10)) AS Expr1,
(Sum([Pass Quantity])/(Sum([Pass Quantity])+Sum([Fail Quantity]))*100) AS
[FPY by Operation]
FROM [metrics problem qry]
GROUP BY ([Assembly Number] &"-"& Left([IDESC],10))
HAVING (((Sum([metrics problem qry].[Pass Quantity]))>0))
ORDER BY ((Sum([Pass Quantity])/(Sum([Pass Quantity])+ Sum([Fail
Quantity]))*100)), [Assembly Number];

--
KARL DEWEY
Build a little - Test a little


Jeff said:
I have a database which includes a report with bar charts which works and
displays just fine but I need to combine the information in the legend for
each bar with data from two seperate fields.

Currently, the row source for one of my bar charts is as follows:
SELECT TOP 10 ([Assembly Number]) AS Expr1, (Sum([Pass Quantity])/(Sum([Pass
Quantity])+Sum([Fail Quantity]))*100) AS [FPY by Operation] FROM [metrics
problem qry] GROUP BY [metrics problem qry].[Assembly Number] HAVING
(((Sum([metrics problem qry].[Pass Quantity]))>0)) ORDER BY ((Sum([Pass
Quantity])/(Sum([Pass Quantity])+Sum([Fail Quantity]))*100)), [Assembly
Number];
Click to expand...

I have tried the following by adding the first 10 characters of the field
IDESC as follows:

SELECT TOP 10 ([Assembly Number] &"-"& Left([IDESC],10)) AS Expr1,
(Sum([Pass Quantity])/(Sum([Pass Quantity])+Sum([Fail Quantity]))*100) AS
[FPY by Operation] FROM [metrics problem qry] GROUP BY [metrics problem
qry].[Assembly Number] HAVING (((Sum([metrics problem qry].[Pass
Quantity]))>0)) ORDER BY ((Sum([Pass Quantity])/(Sum([Pass
Quantity])+Sum([Fail Quantity]))*100)), [Assembly Number];
Click to expand...

But I get an error message which states:

You tried to execute a query that does not include the specified
expression'[metrics problem qry].[Assembly Number] & "-"&Left([IDESC],10' as
part of an aggregate function
Click to expand...

The SQL of my query is:
SELECT [FPY Table].Date, [FPY Table].ID, [FPY Table].Operator, [FPY
Table].[Serial Number], [FPY Table].Operation, [FPY Table].[Assembly Number],
[FPY Table].[Pass Quantity], [FPY Table].LatePass, [FPY Table].[Fail
Quantity], [FPY Table].[Total Failure], [FPY Table].ProblemCode1, [FPY
Table].Rework, [FPY Table].[Part Number], [FPY Table].[Description of
Problem], [FPY Table].PassYield, [FPY Table].FailYield, [FPY Table].depos,
[FPY Table].multiprobs, [FPY Table].RejectNbr, [FPY Table].DTS, [FPY
Table].TermID, ONEBPCSF_IIM.IDESC, ([Assembly Number] & " - " &
Left([IDESC],10)) AS Expr2
FROM [FPY Table] INNER JOIN ONEBPCSF_IIM ON [FPY Table].[Assembly Number] =
ONEBPCSF_IIM.IPROD
GROUP BY [FPY Table].Date, [FPY Table].ID, [FPY Table].Operator, [FPY
Table].[Serial Number], [FPY Table].Operation, [FPY Table].[Assembly Number],
[FPY Table].[Pass Quantity], [FPY Table].LatePass, [FPY Table].[Fail
Quantity], [FPY Table].[Total Failure], [FPY Table].ProblemCode1, [FPY
Table].Rework, [FPY Table].[Part Number], [FPY Table].[Description of
Problem], [FPY Table].PassYield, [FPY Table].FailYield, [FPY Table].depos,
[FPY Table].multiprobs, [FPY Table].RejectNbr, [FPY Table].DTS, [FPY
Table].TermID, ONEBPCSF_IIM.IDESC, ([Assembly Number] & " - " &
Left([IDESC],10))
HAVING ((([FPY Table].Date) Between [forms]![print or view
reports]![StartDate] And [forms]![print or view reports]![EndDate]));
Click to expand...

What am I doing wrong and how can I get these two fields to be displayed for
each bar of my bar chart?

thank you.
Click to expand...
 
J

Jeff

Thanks Karl- You got me on the right track and it is now working just great!

I tried what you suggested and I got the following error:

You tried to execute a query that does not include the specified expression
'[Assembly Number]' as part of an aggregate function.

so I modified the last variable in the SQL slightly to this:

SELECT TOP 10 ([Assembly Number] &"-"& Left([IDESC],10)) AS Expr1,
(Sum([Pass Quantity])/(Sum([Pass Quantity])+Sum([Fail Quantity]))*100) AS
[FPY by Operation] FROM [metrics problem qry] GROUP BY ([Assembly Number]
&"-"& Left([IDESC],10)) HAVING (((Sum([metrics problem qry].[Pass
Quantity]))>0)) ORDER BY ((Sum([Pass Quantity])/(Sum([Pass Quantity])+
Sum([Fail Quantity]))*100)), ([Assembly Number] &"-"& Left([IDESC],10));

Thank you very much!

Jeff P

KARL DEWEY said:
Try it this way ---
SELECT TOP 10 ([Assembly Number] &"-"& Left([IDESC],10)) AS Expr1,
(Sum([Pass Quantity])/(Sum([Pass Quantity])+Sum([Fail Quantity]))*100) AS
[FPY by Operation]
FROM [metrics problem qry]
GROUP BY ([Assembly Number] &"-"& Left([IDESC],10))
HAVING (((Sum([metrics problem qry].[Pass Quantity]))>0))
ORDER BY ((Sum([Pass Quantity])/(Sum([Pass Quantity])+ Sum([Fail
Quantity]))*100)), [Assembly Number];

--
KARL DEWEY
Build a little - Test a little


Jeff said:
I have a database which includes a report with bar charts which works and
displays just fine but I need to combine the information in the legend for
each bar with data from two seperate fields.

Currently, the row source for one of my bar charts is as follows:

SELECT TOP 10 ([Assembly Number]) AS Expr1, (Sum([Pass Quantity])/(Sum([Pass
Quantity])+Sum([Fail Quantity]))*100) AS [FPY by Operation] FROM [metrics
problem qry] GROUP BY [metrics problem qry].[Assembly Number] HAVING
(((Sum([metrics problem qry].[Pass Quantity]))>0)) ORDER BY ((Sum([Pass
Quantity])/(Sum([Pass Quantity])+Sum([Fail Quantity]))*100)), [Assembly
Number];

I have tried the following by adding the first 10 characters of the field
IDESC as follows:


SELECT TOP 10 ([Assembly Number] &"-"& Left([IDESC],10)) AS Expr1,
(Sum([Pass Quantity])/(Sum([Pass Quantity])+Sum([Fail Quantity]))*100) AS
[FPY by Operation] FROM [metrics problem qry] GROUP BY [metrics problem
qry].[Assembly Number] HAVING (((Sum([metrics problem qry].[Pass
Quantity]))>0)) ORDER BY ((Sum([Pass Quantity])/(Sum([Pass
Quantity])+Sum([Fail Quantity]))*100)), [Assembly Number];


But I get an error message which states:


You tried to execute a query that does not include the specified
expression'[metrics problem qry].[Assembly Number] & "-"&Left([IDESC],10' as
part of an aggregate function


The SQL of my query is:

SELECT [FPY Table].Date, [FPY Table].ID, [FPY Table].Operator, [FPY
Table].[Serial Number], [FPY Table].Operation, [FPY Table].[Assembly Number],
[FPY Table].[Pass Quantity], [FPY Table].LatePass, [FPY Table].[Fail
Quantity], [FPY Table].[Total Failure], [FPY Table].ProblemCode1, [FPY
Table].Rework, [FPY Table].[Part Number], [FPY Table].[Description of
Problem], [FPY Table].PassYield, [FPY Table].FailYield, [FPY Table].depos,
[FPY Table].multiprobs, [FPY Table].RejectNbr, [FPY Table].DTS, [FPY
Table].TermID, ONEBPCSF_IIM.IDESC, ([Assembly Number] & " - " &
Left([IDESC],10)) AS Expr2
FROM [FPY Table] INNER JOIN ONEBPCSF_IIM ON [FPY Table].[Assembly Number] =
ONEBPCSF_IIM.IPROD
GROUP BY [FPY Table].Date, [FPY Table].ID, [FPY Table].Operator, [FPY
Table].[Serial Number], [FPY Table].Operation, [FPY Table].[Assembly Number],
[FPY Table].[Pass Quantity], [FPY Table].LatePass, [FPY Table].[Fail
Quantity], [FPY Table].[Total Failure], [FPY Table].ProblemCode1, [FPY
Table].Rework, [FPY Table].[Part Number], [FPY Table].[Description of
Problem], [FPY Table].PassYield, [FPY Table].FailYield, [FPY Table].depos,
[FPY Table].multiprobs, [FPY Table].RejectNbr, [FPY Table].DTS, [FPY
Table].TermID, ONEBPCSF_IIM.IDESC, ([Assembly Number] & " - " &
Left([IDESC],10))
HAVING ((([FPY Table].Date) Between [forms]![print or view
reports]![StartDate] And [forms]![print or view reports]![EndDate]));


What am I doing wrong and how can I get these two fields to be displayed for
each bar of my bar chart?

thank you.
Click to expand...
Click to expand...
 
J

Jeff

Incidently, is there any way to insert a carriage return line feed in here
so that the legend under each bar is on two lines? I am finding having all
this info is useful but it gets cut off or the bar chart gets squished on my
report.

thanks



Jeff said:
Thanks Karl- You got me on the right track and it is now working just great!

I tried what you suggested and I got the following error:

You tried to execute a query that does not include the specified expression
'[Assembly Number]' as part of an aggregate function.

so I modified the last variable in the SQL slightly to this:

SELECT TOP 10 ([Assembly Number] &"-"& Left([IDESC],10)) AS Expr1,
(Sum([Pass Quantity])/(Sum([Pass Quantity])+Sum([Fail Quantity]))*100) AS
[FPY by Operation] FROM [metrics problem qry] GROUP BY ([Assembly Number]
&"-"& Left([IDESC],10)) HAVING (((Sum([metrics problem qry].[Pass
Quantity]))>0)) ORDER BY ((Sum([Pass Quantity])/(Sum([Pass Quantity])+
Sum([Fail Quantity]))*100)), ([Assembly Number] &"-"& Left([IDESC],10));

Thank you very much!

Jeff P

KARL DEWEY said:
Try it this way ---
SELECT TOP 10 ([Assembly Number] &"-"& Left([IDESC],10)) AS Expr1,
(Sum([Pass Quantity])/(Sum([Pass Quantity])+Sum([Fail Quantity]))*100) AS
[FPY by Operation]
FROM [metrics problem qry]
GROUP BY ([Assembly Number] &"-"& Left([IDESC],10))
HAVING (((Sum([metrics problem qry].[Pass Quantity]))>0))
ORDER BY ((Sum([Pass Quantity])/(Sum([Pass Quantity])+ Sum([Fail
Quantity]))*100)), [Assembly Number];

--
KARL DEWEY
Build a little - Test a little


Jeff said:
I have a database which includes a report with bar charts which works and
displays just fine but I need to combine the information in the legend for
each bar with data from two seperate fields.

Currently, the row source for one of my bar charts is as follows:

SELECT TOP 10 ([Assembly Number]) AS Expr1, (Sum([Pass Quantity])/(Sum([Pass
Quantity])+Sum([Fail Quantity]))*100) AS [FPY by Operation] FROM [metrics
problem qry] GROUP BY [metrics problem qry].[Assembly Number] HAVING
(((Sum([metrics problem qry].[Pass Quantity]))>0)) ORDER BY ((Sum([Pass
Quantity])/(Sum([Pass Quantity])+Sum([Fail Quantity]))*100)), [Assembly
Number];

I have tried the following by adding the first 10 characters of the field
IDESC as follows:


SELECT TOP 10 ([Assembly Number] &"-"& Left([IDESC],10)) AS Expr1,
(Sum([Pass Quantity])/(Sum([Pass Quantity])+Sum([Fail Quantity]))*100) AS
[FPY by Operation] FROM [metrics problem qry] GROUP BY [metrics problem
qry].[Assembly Number] HAVING (((Sum([metrics problem qry].[Pass
Quantity]))>0)) ORDER BY ((Sum([Pass Quantity])/(Sum([Pass
Quantity])+Sum([Fail Quantity]))*100)), [Assembly Number];


But I get an error message which states:


You tried to execute a query that does not include the specified
expression'[metrics problem qry].[Assembly Number] & "-"&Left([IDESC],10' as
part of an aggregate function


The SQL of my query is:

SELECT [FPY Table].Date, [FPY Table].ID, [FPY Table].Operator, [FPY
Table].[Serial Number], [FPY Table].Operation, [FPY Table].[Assembly Number],
[FPY Table].[Pass Quantity], [FPY Table].LatePass, [FPY Table].[Fail
Quantity], [FPY Table].[Total Failure], [FPY Table].ProblemCode1, [FPY
Table].Rework, [FPY Table].[Part Number], [FPY Table].[Description of
Problem], [FPY Table].PassYield, [FPY Table].FailYield, [FPY Table].depos,
[FPY Table].multiprobs, [FPY Table].RejectNbr, [FPY Table].DTS, [FPY
Table].TermID, ONEBPCSF_IIM.IDESC, ([Assembly Number] & " - " &
Left([IDESC],10)) AS Expr2
FROM [FPY Table] INNER JOIN ONEBPCSF_IIM ON [FPY Table].[Assembly Number] =
ONEBPCSF_IIM.IPROD
GROUP BY [FPY Table].Date, [FPY Table].ID, [FPY Table].Operator, [FPY
Table].[Serial Number], [FPY Table].Operation, [FPY Table].[Assembly Number],
[FPY Table].[Pass Quantity], [FPY Table].LatePass, [FPY Table].[Fail
Quantity], [FPY Table].[Total Failure], [FPY Table].ProblemCode1, [FPY
Table].Rework, [FPY Table].[Part Number], [FPY Table].[Description of
Problem], [FPY Table].PassYield, [FPY Table].FailYield, [FPY Table].depos,
[FPY Table].multiprobs, [FPY Table].RejectNbr, [FPY Table].DTS, [FPY
Table].TermID, ONEBPCSF_IIM.IDESC, ([Assembly Number] & " - " &
Left([IDESC],10))
HAVING ((([FPY Table].Date) Between [forms]![print or view
reports]![StartDate] And [forms]![print or view reports]![EndDate]));


What am I doing wrong and how can I get these two fields to be displayed for
each bar of my bar chart?

thank you.
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

I run it with no error.
I suggest you copy the SQL and paste in a new query. Sometimes Access does
not clean up everything - parts not visible stay in the query.
--
KARL DEWEY
Build a little - Test a little


Jeff said:
Thanks Karl- You got me on the right track and it is now working just great!

I tried what you suggested and I got the following error:

You tried to execute a query that does not include the specified expression
'[Assembly Number]' as part of an aggregate function.

so I modified the last variable in the SQL slightly to this:

SELECT TOP 10 ([Assembly Number] &"-"& Left([IDESC],10)) AS Expr1,
(Sum([Pass Quantity])/(Sum([Pass Quantity])+Sum([Fail Quantity]))*100) AS
[FPY by Operation] FROM [metrics problem qry] GROUP BY ([Assembly Number]
&"-"& Left([IDESC],10)) HAVING (((Sum([metrics problem qry].[Pass
Quantity]))>0)) ORDER BY ((Sum([Pass Quantity])/(Sum([Pass Quantity])+
Sum([Fail Quantity]))*100)), ([Assembly Number] &"-"& Left([IDESC],10));

Thank you very much!

Jeff P

KARL DEWEY said:
Try it this way ---
SELECT TOP 10 ([Assembly Number] &"-"& Left([IDESC],10)) AS Expr1,
(Sum([Pass Quantity])/(Sum([Pass Quantity])+Sum([Fail Quantity]))*100) AS
[FPY by Operation]
FROM [metrics problem qry]
GROUP BY ([Assembly Number] &"-"& Left([IDESC],10))
HAVING (((Sum([metrics problem qry].[Pass Quantity]))>0))
ORDER BY ((Sum([Pass Quantity])/(Sum([Pass Quantity])+ Sum([Fail
Quantity]))*100)), [Assembly Number];

--
KARL DEWEY
Build a little - Test a little


Jeff said:
I have a database which includes a report with bar charts which works and
displays just fine but I need to combine the information in the legend for
each bar with data from two seperate fields.

Currently, the row source for one of my bar charts is as follows:

SELECT TOP 10 ([Assembly Number]) AS Expr1, (Sum([Pass Quantity])/(Sum([Pass
Quantity])+Sum([Fail Quantity]))*100) AS [FPY by Operation] FROM [metrics
problem qry] GROUP BY [metrics problem qry].[Assembly Number] HAVING
(((Sum([metrics problem qry].[Pass Quantity]))>0)) ORDER BY ((Sum([Pass
Quantity])/(Sum([Pass Quantity])+Sum([Fail Quantity]))*100)), [Assembly
Number];

I have tried the following by adding the first 10 characters of the field
IDESC as follows:


SELECT TOP 10 ([Assembly Number] &"-"& Left([IDESC],10)) AS Expr1,
(Sum([Pass Quantity])/(Sum([Pass Quantity])+Sum([Fail Quantity]))*100) AS
[FPY by Operation] FROM [metrics problem qry] GROUP BY [metrics problem
qry].[Assembly Number] HAVING (((Sum([metrics problem qry].[Pass
Quantity]))>0)) ORDER BY ((Sum([Pass Quantity])/(Sum([Pass
Quantity])+Sum([Fail Quantity]))*100)), [Assembly Number];


But I get an error message which states:


You tried to execute a query that does not include the specified
expression'[metrics problem qry].[Assembly Number] & "-"&Left([IDESC],10' as
part of an aggregate function


The SQL of my query is:

SELECT [FPY Table].Date, [FPY Table].ID, [FPY Table].Operator, [FPY
Table].[Serial Number], [FPY Table].Operation, [FPY Table].[Assembly Number],
[FPY Table].[Pass Quantity], [FPY Table].LatePass, [FPY Table].[Fail
Quantity], [FPY Table].[Total Failure], [FPY Table].ProblemCode1, [FPY
Table].Rework, [FPY Table].[Part Number], [FPY Table].[Description of
Problem], [FPY Table].PassYield, [FPY Table].FailYield, [FPY Table].depos,
[FPY Table].multiprobs, [FPY Table].RejectNbr, [FPY Table].DTS, [FPY
Table].TermID, ONEBPCSF_IIM.IDESC, ([Assembly Number] & " - " &
Left([IDESC],10)) AS Expr2
FROM [FPY Table] INNER JOIN ONEBPCSF_IIM ON [FPY Table].[Assembly Number] =
ONEBPCSF_IIM.IPROD
GROUP BY [FPY Table].Date, [FPY Table].ID, [FPY Table].Operator, [FPY
Table].[Serial Number], [FPY Table].Operation, [FPY Table].[Assembly Number],
[FPY Table].[Pass Quantity], [FPY Table].LatePass, [FPY Table].[Fail
Quantity], [FPY Table].[Total Failure], [FPY Table].ProblemCode1, [FPY
Table].Rework, [FPY Table].[Part Number], [FPY Table].[Description of
Problem], [FPY Table].PassYield, [FPY Table].FailYield, [FPY Table].depos,
[FPY Table].multiprobs, [FPY Table].RejectNbr, [FPY Table].DTS, [FPY
Table].TermID, ONEBPCSF_IIM.IDESC, ([Assembly Number] & " - " &
Left([IDESC],10))
HAVING ((([FPY Table].Date) Between [forms]![print or view
reports]![StartDate] And [forms]![print or view reports]![EndDate]));


What am I doing wrong and how can I get these two fields to be displayed for
each bar of my bar chart?

thank you.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

one more tweak to the bar chart legend 4
Details I have learned writing a Word Add-in 0

Top