Graph based on Report/Crosstab query

[Kevin Labore]

Hi

I have a report based on a query. The report works fine.
I guess what I would like to do is to have a graph for each group(Deptname)
I want to show the Series by year with the graph showing weeks/$ amt

Can you base a graph on the report results? Do you have to do a graph based
on query/table?

The query that the report is created from has the following.

TRANSFORM Sum([DeptsSales Query].DeptSales) AS SumOfDeptSales
SELECT (DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)) AS
Week, [DeptsSales Query].DeptDesc, Avg(([deptsales])) AS AvgWeek
FROM [DeptsSales Query]
GROUP BY (DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)),
[DeptsSales Query].DeptDesc
ORDER BY (DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)),
[DeptsSales Query].DeptDesc, DatePart("yyyy",[deptdate],1,1) DESC
PIVOT DatePart("yyyy",[deptdate],1,1);

The report works fine as I said, the query ensures that the week ends on a
Saturday.

The report then groups by: Deptdesc and Week
The years are in Cols, and weeks in rows.
I am confident with Excel graphs, and have exported queries to work with
Excel with them but this would work very well in this case.
Can someone tell me how I would go about making either a graph based on the
report or the query to get the results I desire
Do I need a different query?

I would like a graph like below:
* , @ represents years
__________________________________________
|
|
|
|
|
|
| @
|
| * @ @
|
| * * @* @
|
| * @ @
|
__________________________________________
Weeks

Do I need to place with the Rowsource or what?

Thanks for any help

Kevin

 

[Duane Hookom]

A graph has a Row Source of a query or table or sql statement. You can link
the graph to the report data using the Link Master/Child properties like you
would link a report/subreport or form/subform.

 

[Kevin Labore]

Hi Duane

I understand what you are talking about, I just have not played with the
properties much. So would it look something like this?

Row Source = SELECT [DeptDesc],Sum([2005]) AS [TYSales], Sum([2004]) AS
[LYSales] FROM [DeptSales Crosstab Week/Year Old] GROUP BY [DeptDesc];

Child Links = [Deptdesc]
Mast Links = [DeptDesc]

I know I need the deptweek in there too somehow.
With the above I am getting a blank graph with (TYSales only) not all
years.(Presently I only have data for 2004,2005.)
Can you tell me where I might find some example of similar so I can review?

thanks for the help.

Kevin

A graph has a Row Source of a query or table or sql statement. You can link
the graph to the report data using the Link Master/Child properties like
you would link a report/subreport or form/subform.

--
Duane Hookom
MS Access MVP

Hi

I have a report based on a query. The report works fine.
I guess what I would like to do is to have a graph for each
group(Deptname)
I want to show the Series by year with the graph showing weeks/$ amt

Can you base a graph on the report results? Do you have to do a graph
based on query/table?

The query that the report is created from has the following.

TRANSFORM Sum([DeptsSales Query].DeptSales) AS SumOfDeptSales
SELECT (DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1))
AS Week, [DeptsSales Query].DeptDesc, Avg(([deptsales])) AS AvgWeek
FROM [DeptsSales Query]
GROUP BY
(DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)),
[DeptsSales Query].DeptDesc
ORDER BY
(DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)),
[DeptsSales Query].DeptDesc, DatePart("yyyy",[deptdate],1,1) DESC
PIVOT DatePart("yyyy",[deptdate],1,1);

The report works fine as I said, the query ensures that the week ends on
a Saturday.

The report then groups by: Deptdesc and Week
The years are in Cols, and weeks in rows.
I am confident with Excel graphs, and have exported queries to work with
Excel with them but this would work very well in this case.
Can someone tell me how I would go about making either a graph based on
the report or the query to get the results I desire
Do I need a different query?

I would like a graph like below:
* , @ represents years
__________________________________________
| |
| |
| |
| @ |
| * @ @ |
| * * @* @ |
| * @ @ |
__________________________________________
Weeks

Do I need to place with the Rowsource or what?

Thanks for any help

Kevin

 

[Duane Hookom]

I believe you would need to add the calculated week field and group by it.
There is a graph with link master child in the At Your Survey reports which
can be found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
Duane Hookom
MS Access MVP

Hi Duane

I understand what you are talking about, I just have not played with the
properties much. So would it look something like this?

Row Source = SELECT [DeptDesc],Sum([2005]) AS [TYSales], Sum([2004]) AS
[LYSales] FROM [DeptSales Crosstab Week/Year Old] GROUP BY [DeptDesc];

Child Links = [Deptdesc]
Mast Links = [DeptDesc]

I know I need the deptweek in there too somehow.
With the above I am getting a blank graph with (TYSales only) not all
years.(Presently I only have data for 2004,2005.)
Can you tell me where I might find some example of similar so I can
review?

thanks for the help.

Kevin

A graph has a Row Source of a query or table or sql statement. You can
link the graph to the report data using the Link Master/Child properties
like you would link a report/subreport or form/subform.

--
Duane Hookom
MS Access MVP

Hi

I have a report based on a query. The report works fine.
I guess what I would like to do is to have a graph for each
group(Deptname)
I want to show the Series by year with the graph showing weeks/$ amt

Can you base a graph on the report results? Do you have to do a graph
based on query/table?

The query that the report is created from has the following.

TRANSFORM Sum([DeptsSales Query].DeptSales) AS SumOfDeptSales
SELECT (DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1))
AS Week, [DeptsSales Query].DeptDesc, Avg(([deptsales])) AS AvgWeek
FROM [DeptsSales Query]
GROUP BY
(DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)),
[DeptsSales Query].DeptDesc
ORDER BY
(DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)),
[DeptsSales Query].DeptDesc, DatePart("yyyy",[deptdate],1,1) DESC
PIVOT DatePart("yyyy",[deptdate],1,1);

The report works fine as I said, the query ensures that the week ends on
a Saturday.

The report then groups by: Deptdesc and Week
The years are in Cols, and weeks in rows.
I am confident with Excel graphs, and have exported queries to work with
Excel with them but this would work very well in this case.
Can someone tell me how I would go about making either a graph based on
the report or the query to get the results I desire
Do I need a different query?

I would like a graph like below:
* , @ represents years
__________________________________________
| |
| |
| |
| @ |
| * @ @ |
| * * @* @ |
| * @ @ |
__________________________________________
Weeks

Do I need to place with the Rowsource or what?

Thanks for any help

Kevin

 

[Kevin Labore]

Hi Duane

I will check out the links and play with it.
I can still access the report fields or query/tables right?

Again thanks for the help.

Kevin

I believe you would need to add the calculated week field and group by it.
There is a graph with link master child in the At Your Survey reports which
can be found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
Duane Hookom
MS Access MVP

Hi Duane

I understand what you are talking about, I just have not played with the
properties much. So would it look something like this?

Row Source = SELECT [DeptDesc],Sum([2005]) AS [TYSales], Sum([2004])
AS [LYSales] FROM [DeptSales Crosstab Week/Year Old] GROUP BY [DeptDesc];

Child Links = [Deptdesc]
Mast Links = [DeptDesc]

I know I need the deptweek in there too somehow.
With the above I am getting a blank graph with (TYSales only) not all
years.(Presently I only have data for 2004,2005.)
Can you tell me where I might find some example of similar so I can
review?

thanks for the help.

Kevin

A graph has a Row Source of a query or table or sql statement. You can
link the graph to the report data using the Link Master/Child properties
like you would link a report/subreport or form/subform.

--
Duane Hookom
MS Access MVP


Hi

I have a report based on a query. The report works fine.
I guess what I would like to do is to have a graph for each
group(Deptname)
I want to show the Series by year with the graph showing weeks/$ amt

Can you base a graph on the report results? Do you have to do a graph
based on query/table?

The query that the report is created from has the following.

TRANSFORM Sum([DeptsSales Query].DeptSales) AS SumOfDeptSales
SELECT (DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1))
AS Week, [DeptsSales Query].DeptDesc, Avg(([deptsales])) AS AvgWeek
FROM [DeptsSales Query]
GROUP BY
(DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)),
[DeptsSales Query].DeptDesc
ORDER BY
(DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)),
[DeptsSales Query].DeptDesc, DatePart("yyyy",[deptdate],1,1) DESC
PIVOT DatePart("yyyy",[deptdate],1,1);

The report works fine as I said, the query ensures that the week ends
on a Saturday.

The report then groups by: Deptdesc and Week
The years are in Cols, and weeks in rows.
I am confident with Excel graphs, and have exported queries to work
with Excel with them but this would work very well in this case.
Can someone tell me how I would go about making either a graph based on
the report or the query to get the results I desire
Do I need a different query?

I would like a graph like below:
* , @ represents years
__________________________________________
| |
| |
| |
| @ |
| * @ @ |
| * * @* @ |
| * @ @ |
__________________________________________
Weeks

Do I need to place with the Rowsource or what?

Thanks for any help

Kevin

 

[Duane Hookom]

yes

--
Duane Hookom
MS Access MVP

Hi Duane

I will check out the links and play with it.
I can still access the report fields or query/tables right?

Again thanks for the help.

Kevin

I believe you would need to add the calculated week field and group by it.
There is a graph with link master child in the At Your Survey reports
which can be found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
Duane Hookom
MS Access MVP

Hi Duane

I understand what you are talking about, I just have not played with the
properties much. So would it look something like this?

Row Source = SELECT [DeptDesc],Sum([2005]) AS [TYSales], Sum([2004])
AS [LYSales] FROM [DeptSales Crosstab Week/Year Old] GROUP BY
[DeptDesc];

Child Links = [Deptdesc]
Mast Links = [DeptDesc]

I know I need the deptweek in there too somehow.
With the above I am getting a blank graph with (TYSales only) not all
years.(Presently I only have data for 2004,2005.)
Can you tell me where I might find some example of similar so I can
review?

thanks for the help.

Kevin


A graph has a Row Source of a query or table or sql statement. You can
link the graph to the report data using the Link Master/Child properties
like you would link a report/subreport or form/subform.

--
Duane Hookom
MS Access MVP


Hi

I have a report based on a query. The report works fine.
I guess what I would like to do is to have a graph for each
group(Deptname)
I want to show the Series by year with the graph showing weeks/$ amt

Can you base a graph on the report results? Do you have to do a graph
based on query/table?

The query that the report is created from has the following.

TRANSFORM Sum([DeptsSales Query].DeptSales) AS SumOfDeptSales
SELECT
(DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)) AS
Week, [DeptsSales Query].DeptDesc, Avg(([deptsales])) AS AvgWeek
FROM [DeptsSales Query]
GROUP BY
(DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)),
[DeptsSales Query].DeptDesc
ORDER BY
(DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)),
[DeptsSales Query].DeptDesc, DatePart("yyyy",[deptdate],1,1) DESC
PIVOT DatePart("yyyy",[deptdate],1,1);

The report works fine as I said, the query ensures that the week ends
on a Saturday.

The report then groups by: Deptdesc and Week
The years are in Cols, and weeks in rows.
I am confident with Excel graphs, and have exported queries to work
with Excel with them but this would work very well in this case.
Can someone tell me how I would go about making either a graph based
on the report or the query to get the results I desire
Do I need a different query?

I would like a graph like below:
* , @ represents years
__________________________________________
| |
| |
| |
| @ |
| * @ @ |
| * * @* @ |
| * @ @ |
__________________________________________
Weeks

Do I need to place with the Rowsource or what?

Thanks for any help

Kevin

 

[Kevin Labore]

Hi

ok I am still missing something.

Can some one tell me what I am doing wrong with the select statement
I am trying to access the report fields and build a graph based on the sort/group of the report
The graph in the group footer section. (So I will get a graph of each(only one) dept per report page and the graph will show series for each year/week)

The report name is SalesDeptWeekYearGRAPH based on a crosstab query named DeptSales Crosstab Week/Year Old

The report detail line has DeptWeek, and 2004,2005 (plus two calculated to show % of change, amt of change over previous year)
The graph will show changes in deptsales so you can spot changes in sales ahead of time or lacking sales trends and such

The graph in in the footer section has the folliowing (I believe I mostly need help with the row source section?

Source Type (should this be field where I am trying to access the report values?)
Row Source SELECT week, 2004,2005 FROM SalesDeptWeekYearGRAPH
(this is where I think I am having a problem the FROM is the reportname)
Child Links DeptDesc
Master Links DeptDesc

Can someone tell me what I am doing wrong and tell me how to fix it please.

Thanks

Kevin

yes

--
Duane Hookom
MS Access MVP

Hi Duane

I will check out the links and play with it.
I can still access the report fields or query/tables right?

Again thanks for the help.

Kevin

I believe you would need to add the calculated week field and group by it.
There is a graph with link master child in the At Your Survey reports
which can be found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
Duane Hookom
MS Access MVP


Hi Duane

I understand what you are talking about, I just have not played with the
properties much. So would it look something like this?

Row Source = SELECT [DeptDesc],Sum([2005]) AS [TYSales], Sum([2004])
AS [LYSales] FROM [DeptSales Crosstab Week/Year Old] GROUP BY
[DeptDesc];

Child Links = [Deptdesc]
Mast Links = [DeptDesc]

I know I need the deptweek in there too somehow.
With the above I am getting a blank graph with (TYSales only) not all
years.(Presently I only have data for 2004,2005.)
Can you tell me where I might find some example of similar so I can
review?

thanks for the help.

Kevin


A graph has a Row Source of a query or table or sql statement. You can
link the graph to the report data using the Link Master/Child properties
like you would link a report/subreport or form/subform.

--
Duane Hookom
MS Access MVP


Hi

I have a report based on a query. The report works fine.
I guess what I would like to do is to have a graph for each
group(Deptname)
I want to show the Series by year with the graph showing weeks/$ amt

Can you base a graph on the report results? Do you have to do a graph
based on query/table?

The query that the report is created from has the following.

TRANSFORM Sum([DeptsSales Query].DeptSales) AS SumOfDeptSales
SELECT
(DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)) AS
Week, [DeptsSales Query].DeptDesc, Avg(([deptsales])) AS AvgWeek
FROM [DeptsSales Query]
GROUP BY
(DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)),
[DeptsSales Query].DeptDesc
ORDER BY
(DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)),
[DeptsSales Query].DeptDesc, DatePart("yyyy",[deptdate],1,1) DESC
PIVOT DatePart("yyyy",[deptdate],1,1);

The report works fine as I said, the query ensures that the week ends
on a Saturday.

The report then groups by: Deptdesc and Week
The years are in Cols, and weeks in rows.
I am confident with Excel graphs, and have exported queries to work
with Excel with them but this would work very well in this case.
Can someone tell me how I would go about making either a graph based
on the report or the query to get the results I desire
Do I need a different query?

I would like a graph like below:
* , @ represents years
__________________________________________
| |
| |
| |
| @ |
| * @ @ |
| * * @* @ |
| * @ @ |
__________________________________________
Weeks

Do I need to place with the Rowsource or what?

Thanks for any help

Kevin

 

[Duane Hookom]

Is the DeptDesc field in the query SalesDeptWeekYearGRAPH?

--
Duane Hookom
MS Access MVP


Hi

ok I am still missing something.

Can some one tell me what I am doing wrong with the select statement
I am trying to access the report fields and build a graph based on the sort/group of the report
The graph in the group footer section. (So I will get a graph of each(only one) dept per report page and the graph will show series for each year/week)

The report name is SalesDeptWeekYearGRAPH based on a crosstab query named DeptSales Crosstab Week/Year Old

The report detail line has DeptWeek, and 2004,2005 (plus two calculated to show % of change, amt of change over previous year)
The graph will show changes in deptsales so you can spot changes in sales ahead of time or lacking sales trends and such

The graph in in the footer section has the folliowing (I believe I mostly need help with the row source section?

Source Type (should this be field where I am trying to access the report values?)
Row Source SELECT week, 2004,2005 FROM SalesDeptWeekYearGRAPH
(this is where I think I am having a problem the FROM is the reportname)
Child Links DeptDesc
Master Links DeptDesc

Can someone tell me what I am doing wrong and tell me how to fix it please.

Thanks

Kevin

yes

--
Duane Hookom
MS Access MVP

Hi Duane

I will check out the links and play with it.
I can still access the report fields or query/tables right?

Again thanks for the help.

Kevin

I believe you would need to add the calculated week field and group by it.
There is a graph with link master child in the At Your Survey reports
which can be found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
Duane Hookom
MS Access MVP


Hi Duane

I understand what you are talking about, I just have not played with the
properties much. So would it look something like this?

Row Source = SELECT [DeptDesc],Sum([2005]) AS [TYSales], Sum([2004])
AS [LYSales] FROM [DeptSales Crosstab Week/Year Old] GROUP BY
[DeptDesc];

Child Links = [Deptdesc]
Mast Links = [DeptDesc]

I know I need the deptweek in there too somehow.
With the above I am getting a blank graph with (TYSales only) not all
years.(Presently I only have data for 2004,2005.)
Can you tell me where I might find some example of similar so I can
review?

thanks for the help.

Kevin


A graph has a Row Source of a query or table or sql statement. You can
link the graph to the report data using the Link Master/Child properties
like you would link a report/subreport or form/subform.

--
Duane Hookom
MS Access MVP


Hi

I have a report based on a query. The report works fine.
I guess what I would like to do is to have a graph for each
group(Deptname)
I want to show the Series by year with the graph showing weeks/$ amt

Can you base a graph on the report results? Do you have to do a graph
based on query/table?

The query that the report is created from has the following.

TRANSFORM Sum([DeptsSales Query].DeptSales) AS SumOfDeptSales
SELECT
(DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)) AS
Week, [DeptsSales Query].DeptDesc, Avg(([deptsales])) AS AvgWeek
FROM [DeptsSales Query]
GROUP BY
(DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)),
[DeptsSales Query].DeptDesc
ORDER BY
(DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)),
[DeptsSales Query].DeptDesc, DatePart("yyyy",[deptdate],1,1) DESC
PIVOT DatePart("yyyy",[deptdate],1,1);

The report works fine as I said, the query ensures that the week ends
on a Saturday.

The report then groups by: Deptdesc and Week
The years are in Cols, and weeks in rows.
I am confident with Excel graphs, and have exported queries to work
with Excel with them but this would work very well in this case.
Can someone tell me how I would go about making either a graph based
on the report or the query to get the results I desire
Do I need a different query?

I would like a graph like below:
* , @ represents years
__________________________________________
| |
| |
| |
| @ |
| * @ @ |
| * * @* @ |
| * @ @ |
__________________________________________
Weeks

Do I need to place with the Rowsource or what?

Thanks for any help

Kevin

 

[Kevin Labore]

yes the DeptDesc is both in the query and the report but the names are as follows

Report is SalesDeptWeekYearGRAPH
Query is DeptSales Crosstab Week/Year ALL

thanks

Kevin

Is the DeptDesc field in the query SalesDeptWeekYearGRAPH?

--
Duane Hookom
MS Access MVP


Hi

ok I am still missing something.

Can some one tell me what I am doing wrong with the select statement
I am trying to access the report fields and build a graph based on the sort/group of the report
The graph in the group footer section. (So I will get a graph of each(only one) dept per report page and the graph will show series for each year/week)

The report name is SalesDeptWeekYearGRAPH based on a crosstab query named DeptSales Crosstab Week/Year Old

The report detail line has DeptWeek, and 2004,2005 (plus two calculated to show % of change, amt of change over previous year)
The graph will show changes in deptsales so you can spot changes in sales ahead of time or lacking sales trends and such

The graph in in the footer section has the folliowing (I believe I mostly need help with the row source section?

Source Type (should this be field where I am trying to access the report values?)
Row Source SELECT week, 2004,2005 FROM SalesDeptWeekYearGRAPH
(this is where I think I am having a problem the FROM is the reportname)
Child Links DeptDesc
Master Links DeptDesc

Can someone tell me what I am doing wrong and tell me how to fix it please.

Thanks

Kevin

yes

--
Duane Hookom
MS Access MVP

Hi Duane

I will check out the links and play with it.
I can still access the report fields or query/tables right?

Again thanks for the help.

Kevin

I believe you would need to add the calculated week field and group by it.
There is a graph with link master child in the At Your Survey reports
which can be found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
Duane Hookom
MS Access MVP


Hi Duane

I understand what you are talking about, I just have not played with the
properties much. So would it look something like this?

Row Source = SELECT [DeptDesc],Sum([2005]) AS [TYSales], Sum([2004])
AS [LYSales] FROM [DeptSales Crosstab Week/Year Old] GROUP BY
[DeptDesc];

Child Links = [Deptdesc]
Mast Links = [DeptDesc]

I know I need the deptweek in there too somehow.
With the above I am getting a blank graph with (TYSales only) not all
years.(Presently I only have data for 2004,2005.)
Can you tell me where I might find some example of similar so I can
review?

thanks for the help.

Kevin


A graph has a Row Source of a query or table or sql statement. You can
link the graph to the report data using the Link Master/Child properties
like you would link a report/subreport or form/subform.

--
Duane Hookom
MS Access MVP


Hi

I have a report based on a query. The report works fine.
I guess what I would like to do is to have a graph for each
group(Deptname)
I want to show the Series by year with the graph showing weeks/$ amt

Can you base a graph on the report results? Do you have to do a graph
based on query/table?

The query that the report is created from has the following.

TRANSFORM Sum([DeptsSales Query].DeptSales) AS SumOfDeptSales
SELECT
(DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)) AS
Week, [DeptsSales Query].DeptDesc, Avg(([deptsales])) AS AvgWeek
FROM [DeptsSales Query]
GROUP BY
(DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)),
[DeptsSales Query].DeptDesc
ORDER BY
(DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)),
[DeptsSales Query].DeptDesc, DatePart("yyyy",[deptdate],1,1) DESC
PIVOT DatePart("yyyy",[deptdate],1,1);

The report works fine as I said, the query ensures that the week ends
on a Saturday.

The report then groups by: Deptdesc and Week
The years are in Cols, and weeks in rows.
I am confident with Excel graphs, and have exported queries to work
with Excel with them but this would work very well in this case.
Can someone tell me how I would go about making either a graph based
on the report or the query to get the results I desire
Do I need a different query?

I would like a graph like below:
* , @ represents years
__________________________________________
| |
| |
| |
| @ |
| * @ @ |
| * * @* @ |
| * @ @ |
__________________________________________
Weeks

Do I need to place with the Rowsource or what?

Thanks for any help

Kevin

 

[Duane Hookom]

Your SQL contains two constants which should be converted to legitimate field names.
SELECT week, 2004,2005 FROM SalesDeptWeekYearGRAPH

--
Duane Hookom
MS Access MVP


yes the DeptDesc is both in the query and the report but the names are as follows

Report is SalesDeptWeekYearGRAPH
Query is DeptSales Crosstab Week/Year ALL

thanks

Kevin

Is the DeptDesc field in the query SalesDeptWeekYearGRAPH?

--
Duane Hookom
MS Access MVP


Hi

ok I am still missing something.

Can some one tell me what I am doing wrong with the select statement
I am trying to access the report fields and build a graph based on the sort/group of the report
The graph in the group footer section. (So I will get a graph of each(only one) dept per report page and the graph will show series for each year/week)

The report name is SalesDeptWeekYearGRAPH based on a crosstab query named DeptSales Crosstab Week/Year Old

The report detail line has DeptWeek, and 2004,2005 (plus two calculated to show % of change, amt of change over previous year)
The graph will show changes in deptsales so you can spot changes in sales ahead of time or lacking sales trends and such

The graph in in the footer section has the folliowing (I believe I mostly need help with the row source section?

Source Type (should this be field where I am trying to access the report values?)
Row Source SELECT week, 2004,2005 FROM SalesDeptWeekYearGRAPH
(this is where I think I am having a problem the FROM is the reportname)
Child Links DeptDesc
Master Links DeptDesc

Can someone tell me what I am doing wrong and tell me how to fix it please.

Thanks

Kevin

yes

--
Duane Hookom
MS Access MVP

Hi Duane

I will check out the links and play with it.
I can still access the report fields or query/tables right?

Again thanks for the help.

Kevin

I believe you would need to add the calculated week field and group by it.
There is a graph with link master child in the At Your Survey reports
which can be found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
Duane Hookom
MS Access MVP


Hi Duane

I understand what you are talking about, I just have not played with the
properties much. So would it look something like this?

Row Source = SELECT [DeptDesc],Sum([2005]) AS [TYSales], Sum([2004])
AS [LYSales] FROM [DeptSales Crosstab Week/Year Old] GROUP BY
[DeptDesc];

Child Links = [Deptdesc]
Mast Links = [DeptDesc]

I know I need the deptweek in there too somehow.
With the above I am getting a blank graph with (TYSales only) not all
years.(Presently I only have data for 2004,2005.)
Can you tell me where I might find some example of similar so I can
review?

thanks for the help.

Kevin


A graph has a Row Source of a query or table or sql statement. You can
link the graph to the report data using the Link Master/Child properties
like you would link a report/subreport or form/subform.

--
Duane Hookom
MS Access MVP


Hi

I have a report based on a query. The report works fine.
I guess what I would like to do is to have a graph for each
group(Deptname)
I want to show the Series by year with the graph showing weeks/$ amt

Can you base a graph on the report results? Do you have to do a graph
based on query/table?

The query that the report is created from has the following.

TRANSFORM Sum([DeptsSales Query].DeptSales) AS SumOfDeptSales
SELECT
(DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)) AS
Week, [DeptsSales Query].DeptDesc, Avg(([deptsales])) AS AvgWeek
FROM [DeptsSales Query]
GROUP BY
(DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)),
[DeptsSales Query].DeptDesc
ORDER BY
(DatePart("ww",([deptdate]+(7-(DatePart("w",[deptDate])))),1,1)),
[DeptsSales Query].DeptDesc, DatePart("yyyy",[deptdate],1,1) DESC
PIVOT DatePart("yyyy",[deptdate],1,1);

The report works fine as I said, the query ensures that the week ends
on a Saturday.

The report then groups by: Deptdesc and Week
The years are in Cols, and weeks in rows.
I am confident with Excel graphs, and have exported queries to work
with Excel with them but this would work very well in this case.
Can someone tell me how I would go about making either a graph based
on the report or the query to get the results I desire
Do I need a different query?

I would like a graph like below:
* , @ represents years
__________________________________________
| |
| |
| |
| @ |
| * @ @ |
| * * @* @ |
| * @ @ |
__________________________________________
Weeks

Do I need to place with the Rowsource or what?

Thanks for any help

Kevin