Graphing issues

[Jeff S via AccessMonster.com]

I am trying to graph results of a query.
There are 3 fields in the query:
1. date (allows me to select a record range)
2. categories of a certain event (coming from a category table)
3. number of cases involved (coming from my main table)

When I launch the query the results are showing perfectly:
Date # cs category
11/10/05 5 ABC
11/12/05 6 AAA
11/15/05 7 ASC
---etc

However when I try to make a graph out of this query, the field category is
not availble (not showing)on the list of fields to use for the graph

 

[Vincent Johns]

I am trying to graph results of a query.
There are 3 fields in the query:
1. date (allows me to select a record range)
2. categories of a certain event (coming from a category table)
3. number of cases involved (coming from my main table)

When I launch the query the results are showing perfectly:
Date # cs category
11/10/05 5 ABC
11/12/05 6 AAA
11/15/05 7 ASC
---etc

However when I try to make a graph out of this query, the field category is
not availble (not showing)on the list of fields to use for the graph

I just now imported your data into Access and plotted them in Access
2000, with no trouble. Now, the type of chart I'm using may be
different from yours. Are you trying to plot the category values as
numeric ones (for example, on a line chart)? That, you couldn't expect
to work very well. What I plotted was a column chart, though I admit
that the Access chart wizard required me to specify my fields before I
chose a chart type. So maybe my version of Access works differently
from yours.

You might post some more details of what you were trying to do.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Jeff S via AccessMonster.com]

I am using a bar chart.
The graph has the following code:

SELECT [QR_Categories report 1].subcategory, Sum([QR_Categories report 1].
[Case quant]) AS [SumOfCase quant] FROM [QR_Categories report 1] GROUP BY
[QR_Categories report 1].subcategory ORDER BY Sum([QR_Categories report 1].
[Case quant]) DESC;

The graph is supposed to display the sum of the number of cases for each
subcategory.

When I launch this graph the bars are showing the correct number of cases but
instead of having subcategory names in x , it is displaying "?" for each
subcategory.
I tryied creating a new query and a new graph and the query is easy to create
but the chart wizard won't let me pick the subcategory field to put in x (it
is not showing in the list of fields to choose for my graph)

I am trying to graph results of a query.
There are 3 fields in the query:

[quoted text clipped - 11 lines]

However when I try to make a graph out of this query, the field category is
not availble (not showing)on the list of fields to use for the graph

I just now imported your data into Access and plotted them in Access
2000, with no trouble. Now, the type of chart I'm using may be
different from yours. Are you trying to plot the category values as
numeric ones (for example, on a line chart)? That, you couldn't expect
to work very well. What I plotted was a column chart, though I admit
that the Access chart wizard required me to specify my fields before I
chose a chart type. So maybe my version of Access works differently
from yours.

You might post some more details of what you were trying to do.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

Assuming that what you want is not to debug the wizard but rather to get
the chart you describe, I suggest that you define a Query something like
this (I notice the names have changed, hope I identified them properly
with your original Table):

[QR_Categories report 1] Table Datasheet View:

ID Date Case quant subcategory
-- ---------- ---------- -----------
1 11/10/2005 5 ABC
2 11/12/2005 6 AAA
3 11/15/2005 7 ASC


Since there was only one record for each subcategory in your sample
Table, this example didn't exercise the Sum() function very much, but
the Query I set up was this (pretty similar to yours):

[Q_BarChart] SQL:

SELECT [QR_Categories report 1].subcategory,
Sum([QR_Categories report 1].[Case quant])
AS [SumOfCase quant]
FROM [QR_Categories report 1]
GROUP BY [QR_Categories report 1].subcategory
ORDER BY Sum([QR_Categories report 1].[Case quant]) DESC;

.... and it produced these results:

[Q_BarChart] Query Datasheet View:

subcategory SumOfCase quant
----------- ---------------
ASC 7
AAA 6
ABC 5

In the bar chart (OLE Class = Microsoft Graph 2000 Chart; Class =
MSGraph.Chart.8), I set the Row Source property = Q_BarChart and Column
Count = 0, and the result looked like this:

Cases

8 +-----------------------
| +--+
6 +--|##|---+--+----------
| |##| |##| +--+
4 +--|##|---|##|---|##|---
| |##| |##| |##|
2 +--|##|---|##|---|##|---
| |##| |##| |##|
0 +-------+------+------+-
ASC AAA ABC

If you set yours up like mine and it still doesn't work, maybe you could
use the Documenter to display all of your settings for the chart, and
post them.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

I am using a bar chart.
The graph has the following code:

SELECT [QR_Categories report 1].subcategory, Sum([QR_Categories report 1].
[Case quant]) AS [SumOfCase quant] FROM [QR_Categories report 1] GROUP BY
[QR_Categories report 1].subcategory ORDER BY Sum([QR_Categories report 1].
[Case quant]) DESC;

The graph is supposed to display the sum of the number of cases for each
subcategory.

When I launch this graph the bars are showing the correct number of cases but
instead of having subcategory names in x , it is displaying "?" for each
subcategory.
I tryied creating a new query and a new graph and the query is easy to create
but the chart wizard won't let me pick the subcategory field to put in x (it
is not showing in the list of fields to choose for my graph)

I am trying to graph results of a query.
There are 3 fields in the query:

[quoted text clipped - 11 lines]

However when I try to make a graph out of this query, the field category is
not availble (not showing)on the list of fields to use for the graph

I just now imported your data into Access and plotted them in Access
2000, with no trouble. Now, the type of chart I'm using may be
different from yours. Are you trying to plot the category values as
numeric ones (for example, on a line chart)? That, you couldn't expect
to work very well. What I plotted was a column chart, though I admit
that the Access chart wizard required me to specify my fields before I
chose a chart type. So maybe my version of Access works differently

from yours.

You might post some more details of what you were trying to do.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Jeff S via AccessMonster.com]

I still can't make it work. I am not sure what you mean by posting the
properties of the chart with the documenter.
The query is adding the number of cases for each subcategories taking the
data from a table.
Ex: In the table the data roughly as follows
recordID pallet# case quant subcategoryID
1 123 3 1
2 124 5 1
3 152 3 2
4 153 3 2
5 154 4 3

The query sums the results per subcategory:

subcategoryID case quant
1 8
2 6
3 4

The graph is supposed to display in bar chart the result of the query.

Does that make sense?
If not let me know how to use the documenter

Thanks for your help

J

Assuming that what you want is not to debug the wizard but rather to get
the chart you describe, I suggest that you define a Query something like
this (I notice the names have changed, hope I identified them properly
with your original Table):

[QR_Categories report 1] Table Datasheet View:

ID Date Case quant subcategory
-- ---------- ---------- -----------
1 11/10/2005 5 ABC
2 11/12/2005 6 AAA
3 11/15/2005 7 ASC

Since there was only one record for each subcategory in your sample
Table, this example didn't exercise the Sum() function very much, but
the Query I set up was this (pretty similar to yours):

[Q_BarChart] SQL:

SELECT [QR_Categories report 1].subcategory,
Sum([QR_Categories report 1].[Case quant])
AS [SumOfCase quant]
FROM [QR_Categories report 1]
GROUP BY [QR_Categories report 1].subcategory
ORDER BY Sum([QR_Categories report 1].[Case quant]) DESC;

... and it produced these results:

[Q_BarChart] Query Datasheet View:

subcategory SumOfCase quant
----------- ---------------
ASC 7
AAA 6
ABC 5

In the bar chart (OLE Class = Microsoft Graph 2000 Chart; Class =
MSGraph.Chart.8), I set the Row Source property = Q_BarChart and Column
Count = 0, and the result looked like this:

Cases

8 +-----------------------
| +--+
6 +--|##|---+--+----------
| |##| |##| +--+
4 +--|##|---|##|---|##|---
| |##| |##| |##|
2 +--|##|---|##|---|##|---
| |##| |##| |##|
0 +-------+------+------+-
ASC AAA ABC

If you set yours up like mine and it still doesn't work, maybe you could
use the Documenter to display all of your settings for the chart, and
post them.

I am using a bar chart.
The graph has the following code:

[quoted text clipped - 36 lines]

 

[Vincent Johns]

I still can't make it work. I am not sure what you mean by posting the
properties of the chart with the documenter.

"Documenter" is available at Tools --> Analyze --> Documenter, and it
produces a wizard that lets you specify all sorts of options for
creating a report about what's in your database, such as descriptions of
the fields in all your Tables. It's easy to display more stuff than
you'll ever use, so be careful. If you choose to use it, you may need
to play with it some to get what you want.

I suggested using it to display the parameters that you used for
displaying your chart, as I had no trouble doing that (see my previous
message for a drawing of what I got).

The query is adding the number of cases for each subcategories taking the
data from a table.
Ex: In the table the data roughly as follows
recordID pallet# case quant subcategoryID
1 123 3 1
2 124 5 1
3 152 3 2
4 153 3 2
5 154 4 3

The query sums the results per subcategory:

subcategoryID case quant
1 8
2 6
3 4

and at this point, the Table data are immaterial, as the Query results
are all that you wish to plot on your graph, I assume.

The graph is supposed to display in bar chart the result of the query.

Does that make sense?

Yes, it does. What I don't understand is what you tried to do that
didn't work, since it worked well for me. What did you try to do?

If not let me know how to use the documenter

Thanks for your help

J

Perhaps you could list (from the time you were able to display the
correct values with your Query) what you did, and what happened as a
result that didn't produce the graph you wanted.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Jeff S via AccessMonster.com]

Vincent I appreciate your help with this. I hope I can be more clear about my
problem this time...

Ok, let me try to explain better in details with the help of the documenter:

I have a table, TB_Hold Details that documents details about pallets placed
on hold. This table is linked to another table TB_Main table that is used
mainly to capture the date when these hold events occur (1 to many
relationship). Another table, TB_Categories lists the categories of hold and
the subcategories.
A form (FR_Hold report) is used a a user interface to print reports. it
captures 2 dates between which the data will be retreived from the tables

My first query, QR_Subcategories aims at selecting all the records in the
hold details table that fit within the date range specified in the FR_Hold
report and that meet the following criterion: the status of the hold is equal
to 1. The query sums the cases that are the results of this query
The sql of this query is:

SELECT [TB_Main table].[Date], Sum([TB_Hold Details].[Case quant]) AS
[SumOfCase quant],
[TB_Categories].[subcategory], [TB_Hold Details].[StatusID]
FROM TB_Categories RIGHT JOIN ([TB_Main table] RIGHT JOIN [TB_Hold Details]
ON [TB_Main table].[RecordID]=[TB_Hold Details].[link]) ON [TB_Categories].
[categoryID]=[TB_Hold
Details].[Category]
GROUP BY [TB_Main table].[Date], [TB_Categories].[subcategory], [TB_Hold
Details].[StatusID]
HAVING ((([TB_Main table].Date) Between [Forms]![FR_Hold report].[D2] And
[Forms]![FR_Hold report].[D1]

Now I created a second query based on the 1st one: QR_Subcategories Query
that sums the case quantity in each subcategory. The sql is:

SELECT Sum([QR_Subcategories].[SumOfCase quant]) AS [SumOfSumOfCase quant],
[QR_Subcategories].[subcategory]
FROM QR_Subcategories
GROUP BY [QR_Subcategories].[subcategory];

Based on this 2nd query, I launch the chart wizart under the report creation
wizard to create a bar chart that will show the number of cases for each
subcategory.
One of the very 1st screen of the wizard lets you select the fields to be
involved in your graph. But the field subcategory is not showing in the list.
Therefore I cannot select it to be the x of my graph

I still can't make it work. I am not sure what you mean by posting the
properties of the chart with the documenter.

"Documenter" is available at Tools --> Analyze --> Documenter, and it
produces a wizard that lets you specify all sorts of options for
creating a report about what's in your database, such as descriptions of
the fields in all your Tables. It's easy to display more stuff than
you'll ever use, so be careful. If you choose to use it, you may need
to play with it some to get what you want.

I suggested using it to display the parameters that you used for
displaying your chart, as I had no trouble doing that (see my previous
message for a drawing of what I got).

The query is adding the number of cases for each subcategories taking the
data from a table.

[quoted text clipped - 12 lines]

2 6
3 4

and at this point, the Table data are immaterial, as the Query results
are all that you wish to plot on your graph, I assume.

The graph is supposed to display in bar chart the result of the query.

Does that make sense?

Yes, it does. What I don't understand is what you tried to do that
didn't work, since it worked well for me. What did you try to do?

If not let me know how to use the documenter

Thanks for your help

J

Perhaps you could list (from the time you were able to display the
correct values with your Query) what you did, and what happened as a
result that didn't produce the graph you wanted.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

Jeff,

I'm sorry about my delay in answering you, but I /still/ have not
been able to reproduce your problem -- I tried as well as I could to
reconstruct what you said you did (and in both Access 2000 and Access
2003), and I never had any trouble in generating the bar chart you said
you wanted. What I suggest is for you to try to copy what I did
(details below). If your results deviate from mine at some point, let
me know how, and that may provide insight into what happened. If you
get the same results I did, then you'll have a working bar chart showing
what you say you wanted.

Incidentally, your statement "criterion: the status of the hold is
equal to 1" doesn't have a basis in the SQL you sent. In my
reconstruction of your database, I assumed that the value of [TB_Hold
Details].[StatusID] (which I renamed to [TB_Hold Details].[Status]
because the "ID" at the end made it look like a linking field) was
always equal to 1.

Note: I had to make some inferences about the structure & contents
of your Tables, but what I came up with is, I think, consistent with
what you described. However, I once again experienced no trouble at all
in setting up the kind of Report or Chart that you described. If I
misapprehended how you have set up your Tables, that might explain the
differing results, but what I describe here is based on my best guess as
to what you did.

The Tables (my version) look like these:

[TB_Categories] Table Datasheet View:

categoryID subcategory
---------- -----------
1 ABC
2 AAA
3 ASC

[TB_Hold Details] Table Datasheet View:

TB_Hold De pallet# Case quant Status MainTableID CategoryID
tails_ID
---------- ------- ---------- ------ ----------- ----------
1 123 3 1 101 1
2 124 5 1 101 1
3 152 3 1 102 2
4 153 3 1 102 2
5 154 4 1 103 3

[TB_Main table] Table Datasheet View:

MainTableID Date
----------- ----------
101 11/10/2005
102 11/12/2005
103 11/15/2005

The Queries produce the following results.

[QR_Subcategories] SQL:

SELECT [TB_Main table].Date, TB_Categories.subcategory,
[TB_Hold Details].Status,
Sum([TB_Hold Details].[Case quant]) AS [SumOfCase quant]
FROM [TB_Main table] RIGHT JOIN
(TB_Categories RIGHT JOIN [TB_Hold Details]
ON TB_Categories.categoryID = [TB_Hold Details].CategoryID)
ON [TB_Main table].MainTableID = [TB_Hold Details].MainTableID
GROUP BY [TB_Main table].Date, TB_Categories.subcategory,
[TB_Hold Details].Status
HAVING ((([TB_Main table].Date)
Between [Forms]![FR_Hold report]![D2]
And [Forms]![FR_Hold report]![D1]));


[QR_Subcategories] Query Datasheet View:

Date subcategory Status SumOfCase quant
---------- ----------- ------ ---------------
11/10/2005 ABC 1 8
11/12/2005 AAA 1 6
11/15/2005 ASC 1 4


[Second query] SQL:

SELECT Sum(QR_Subcategories.[SumOfCase quant])
AS [SumOfSumOfCase quant],
QR_Subcategories.subcategory
FROM QR_Subcategories
GROUP BY QR_Subcategories.subcategory;


[Second query] Query Datasheet View:

SumOfSumOfCase quant subcategory
-------------------- -----------
6 AAA
8 ABC
4 ASC

I ran both the Report Wizard, on [Second query], and the Chart Wizard,
also on [Second query], and both Wizards let me select both of the
fields in [Second query].

I put the chart into the Report's page footer (so that I would get only
one Chart, not the 3 that I would have gotten by putting the Chart into
the detail section). I also de-linked the Chart from the dataset
displayed in the detail section of the Report (so that I could see all 3
categories listed in the Chart, instead of just 1 of them). The Report
looked something like this:

+--------------------
|
| Second query
|
| SumOfSumOfCase quant subcategory
| ----------------------------------------
| 6 AAA
| 8 ABC
| 4 ASC
|
....

(with the bar chart at the bottom, same as I showed you in an earlier
message)


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Vincent I appreciate your help with this. I hope I can be more clear about my
problem this time...

Ok, let me try to explain better in details with the help of the documenter:

I have a table, TB_Hold Details that documents details about pallets placed
on hold. This table is linked to another table TB_Main table that is used
mainly to capture the date when these hold events occur (1 to many
relationship). Another table, TB_Categories lists the categories of hold and
the subcategories.
A form (FR_Hold report) is used a a user interface to print reports. it
captures 2 dates between which the data will be retreived from the tables

My first query, QR_Subcategories aims at selecting all the records in the
hold details table that fit within the date range specified in the FR_Hold
report and that meet the following criterion: the status of the hold is equal
to 1. The query sums the cases that are the results of this query
The sql of this query is:

SELECT [TB_Main table].[Date], Sum([TB_Hold Details].[Case quant]) AS
[SumOfCase quant],
[TB_Categories].[subcategory], [TB_Hold Details].[StatusID]
FROM TB_Categories RIGHT JOIN ([TB_Main table] RIGHT JOIN [TB_Hold Details]
ON [TB_Main table].[RecordID]=[TB_Hold Details].[link]) ON [TB_Categories].
[categoryID]=[TB_Hold
Details].[Category]
GROUP BY [TB_Main table].[Date], [TB_Categories].[subcategory], [TB_Hold
Details].[StatusID]
HAVING ((([TB_Main table].Date) Between [Forms]![FR_Hold report].[D2] And
[Forms]![FR_Hold report].[D1]

Now I created a second query based on the 1st one: QR_Subcategories Query
that sums the case quantity in each subcategory. The sql is:

SELECT Sum([QR_Subcategories].[SumOfCase quant]) AS [SumOfSumOfCase quant],
[QR_Subcategories].[subcategory]
FROM QR_Subcategories
GROUP BY [QR_Subcategories].[subcategory];

Based on this 2nd query, I launch the chart wizart under the report creation
wizard to create a bar chart that will show the number of cases for each
subcategory.
One of the very 1st screen of the wizard lets you select the fields to be
involved in your graph. But the field subcategory is not showing in the list.
Therefore I cannot select it to be the x of my graph

I still can't make it work. I am not sure what you mean by posting the
properties of the chart with the documenter.

"Documenter" is available at Tools --> Analyze --> Documenter, and it
produces a wizard that lets you specify all sorts of options for
creating a report about what's in your database, such as descriptions of
the fields in all your Tables. It's easy to display more stuff than
you'll ever use, so be careful. If you choose to use it, you may need
to play with it some to get what you want.

I suggested using it to display the parameters that you used for
displaying your chart, as I had no trouble doing that (see my previous
message for a drawing of what I got).

The query is adding the number of cases for each subcategories taking the
data from a table.

[quoted text clipped - 12 lines]

2 6
3 4

and at this point, the Table data are immaterial, as the Query results
are all that you wish to plot on your graph, I assume.

The graph is supposed to display in bar chart the result of the query.

Does that make sense?

Yes, it does. What I don't understand is what you tried to do that
didn't work, since it worked well for me. What did you try to do?

If not let me know how to use the documenter

Thanks for your help

J

Perhaps you could list (from the time you were able to display the
correct values with your Query) what you did, and what happened as a
result that didn't produce the graph you wanted.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.