Hi

I have the following query where I am getting store + input margin.
The problem is: it is printing out input margin per PG. I want to get an
average across all PGs for the branch (and year and week). I thought if I
just grouped by PG that would work but alas no. I've tried doing an average
of (([Extd_REV]-[Extd_WCEV])/[Extd_REV]) but it doesnt like this.

Any other ideas?
Many thanks

PARAMETERS [Forms]![Main Menu]![Store] Text ( 255 ), [Forms]![Main
Menu]![Year] Text ( 255 ), [Forms]![Main Menu]![Week] Text ( 255 );
SELECT dbo_Despatch_data_branch_summary.Branch,
(([Extd_REV]-[Extd_WCEV])/[Extd_REV]) AS [Input Margin]
FROM dbo_Despatch_data_branch_summary
WHERE (((dbo_Despatch_data_branch_summary.Year)=[Forms]![Main Menu]![Year])
AND ((dbo_Despatch_data_branch_summary.Week)=[Forms]![Main Menu]![Week]))
GROUP BY dbo_Despatch_data_branch_summary.Branch,
dbo_Despatch_data_branch_summary.Extd_WCEV,
dbo_Despatch_data_branch_summary.Extd_REV,
dbo_Despatch_data_branch_summary.PG;

It looks to me like you need to group by Branch and Average the computed
values. Also the declared [Forms]![Main Menu]![Store] Text parameter is not
in fact used, so can be deleted, and I'd imagine the year and week parameters
are in fact integers, not text. So try:

PARAMETERS [Forms]![Main Menu]![Year] SHORT,
[Forms]![Main Menu]![Week] SHORT;
SELECT Branch,
AVG((([Extd_REV]-[Extd_WCEV])/[Extd_REV])) AS [Input Margin]
FROM dbo_Despatch_data_branch_summary
WHERE [Year] =[Forms]![Main Menu]![Year]
AND [Week] = [Forms]![Main Menu]![Week]
GROUP BY Branch;

That should give you the results for one week. To get the results for the
whole year restrict it by the year only:

PARAMETERS [Forms]![Main Menu]![Year] SHORT;
SELECT Branch,
AVG((([Extd_REV]-[Extd_WCEV])/[Extd_REV])) AS [Input Margin]
FROM dbo_Despatch_data_branch_summary
WHERE [Year] =[Forms]![Main Menu]![Year]
GROUP BY Branch;

Ken Sheridan
Stafford, England

"Audrey1980" wrote:

> Hi
>
> I have the following query where I am getting store + input margin.
> The problem is: it is printing out input margin per PG. I want to get an
> average across all PGs for the branch (and year and week). I thought if I
> just grouped by PG that would work but alas no. I've tried doing an average
> of (([Extd_REV]-[Extd_WCEV])/[Extd_REV]) but it doesnt like this.
>
> Any other ideas?
> Many thanks
>
> PARAMETERS [Forms]![Main Menu]![Store] Text ( 255 ), [Forms]![Main
> Menu]![Year] Text ( 255 ), [Forms]![Main Menu]![Week] Text ( 255 );
> SELECT dbo_Despatch_data_branch_summary.Branch,
> (([Extd_REV]-[Extd_WCEV])/[Extd_REV]) AS [Input Margin]
> FROM dbo_Despatch_data_branch_summary
> WHERE (((dbo_Despatch_data_branch_summary.Year)=[Forms]![Main Menu]![Year])
> AND ((dbo_Despatch_data_branch_summary.Week)=[Forms]![Main Menu]![Week]))
> GROUP BY dbo_Despatch_data_branch_summary.Branch,
> dbo_Despatch_data_branch_summary.Extd_WCEV,
> dbo_Despatch_data_branch_summary.Extd_REV,
> dbo_Despatch_data_branch_summary.PG;
>

Try this --
PARAMETERS [Forms]![Main Menu]![Store] Text ( 255 ), [Forms]![Main
Menu]![Year] Text ( 255 ), [Forms]![Main Menu]![Week] Text ( 255 );
SELECT dbo_Despatch_data_branch_summary.Branch,
(([Extd_REV]-[Extd_WCEV])/[Extd_REV]) AS [Input Margin]
FROM dbo_Despatch_data_branch_summary
WHERE (((dbo_Despatch_data_branch_summary.Year)=[Forms]![Main Menu]![Year])
AND ((dbo_Despatch_data_branch_summary.Week)=[Forms]![Main Menu]![Week]))
GROUP BY dbo_Despatch_data_branch_summary.Branch;


"Audrey1980" wrote:

> Hi
>
> I have the following query where I am getting store + input margin.
> The problem is: it is printing out input margin per PG. I want to get an
> average across all PGs for the branch (and year and week). I thought if I
> just grouped by PG that would work but alas no. I've tried doing an average
> of (([Extd_REV]-[Extd_WCEV])/[Extd_REV]) but it doesnt like this.
>
> Any other ideas?
> Many thanks
>
> PARAMETERS [Forms]![Main Menu]![Store] Text ( 255 ), [Forms]![Main
> Menu]![Year] Text ( 255 ), [Forms]![Main Menu]![Week] Text ( 255 );
> SELECT dbo_Despatch_data_branch_summary.Branch,
> (([Extd_REV]-[Extd_WCEV])/[Extd_REV]) AS [Input Margin]
> FROM dbo_Despatch_data_branch_summary
> WHERE (((dbo_Despatch_data_branch_summary.Year)=[Forms]![Main Menu]![Year])
> AND ((dbo_Despatch_data_branch_summary.Week)=[Forms]![Main Menu]![Week]))
> GROUP BY dbo_Despatch_data_branch_summary.Branch,
> dbo_Despatch_data_branch_summary.Extd_WCEV,
> dbo_Despatch_data_branch_summary.Extd_REV,
> dbo_Despatch_data_branch_summary.PG;
>

Karl,
THanks but what I got was
"The expression is typed incorrectly or is too complex to be evaluated. Try
simplifying by assigning parts of the expression to variables"

?
Thanks.

"KARL DEWEY" wrote:

> Try this --
> PARAMETERS [Forms]![Main Menu]![Store] Text ( 255 ), [Forms]![Main
> Menu]![Year] Text ( 255 ), [Forms]![Main Menu]![Week] Text ( 255 );
> SELECT dbo_Despatch_data_branch_summary.Branch,
> (([Extd_REV]-[Extd_WCEV])/[Extd_REV]) AS [Input Margin]
> FROM dbo_Despatch_data_branch_summary
> WHERE (((dbo_Despatch_data_branch_summary.Year)=[Forms]![Main Menu]![Year])
> AND ((dbo_Despatch_data_branch_summary.Week)=[Forms]![Main Menu]![Week]))
> GROUP BY dbo_Despatch_data_branch_summary.Branch;
>
>
> "Audrey1980" wrote:
>
> > Hi
> >
> > I have the following query where I am getting store + input margin.
> > The problem is: it is printing out input margin per PG. I want to get an
> > average across all PGs for the branch (and year and week). I thought if I
> > just grouped by PG that would work but alas no. I've tried doing an average
> > of (([Extd_REV]-[Extd_WCEV])/[Extd_REV]) but it doesnt like this.
> >
> > Any other ideas?
> > Many thanks
> >
> > PARAMETERS [Forms]![Main Menu]![Store] Text ( 255 ), [Forms]![Main
> > Menu]![Year] Text ( 255 ), [Forms]![Main Menu]![Week] Text ( 255 );
> > SELECT dbo_Despatch_data_branch_summary.Branch,
> > (([Extd_REV]-[Extd_WCEV])/[Extd_REV]) AS [Input Margin]
> > FROM dbo_Despatch_data_branch_summary
> > WHERE (((dbo_Despatch_data_branch_summary.Year)=[Forms]![Main Menu]![Year])
> > AND ((dbo_Despatch_data_branch_summary.Week)=[Forms]![Main Menu]![Week]))
> > GROUP BY dbo_Despatch_data_branch_summary.Branch,
> > dbo_Despatch_data_branch_summary.Extd_WCEV,
> > dbo_Despatch_data_branch_summary.Extd_REV,
> > dbo_Despatch_data_branch_summary.PG;
> >

Karl

Sorry this did work (once I included the 2 columns in the expression) but I
got the result for each product! 64000 rows. I only want one for the store.

Here's what I have now. I need to average out the input margin for the
store. 1 row only required..

PARAMETERS [Forms]![Main Menu]![Store] Text ( 255 ), [Forms]![Main
Menu]![Year] Text ( 255 ), [Forms]![Main Menu]![Week] Text ( 255 );
SELECT dbo_Despatch_data_branch_summary.Branch,
(([Extd_REV]-[Extd_WCEV])/[Extd_REV]) AS [Input Margin]
FROM dbo_Despatch_data_branch_summary
WHERE (((dbo_Despatch_data_branch_summary.Year)=[Forms]![Main Menu]![Year])
AND ((dbo_Despatch_data_branch_summary.Week)=[Forms]![Main Menu]![Week]))
GROUP BY dbo_Despatch_data_branch_summary.Branch,
dbo_Despatch_data_branch_summary.Extd_WCEV,
dbo_Despatch_data_branch_summary.Extd_REV;




"KARL DEWEY" wrote:

> Try this --
> PARAMETERS [Forms]![Main Menu]![Store] Text ( 255 ), [Forms]![Main
> Menu]![Year] Text ( 255 ), [Forms]![Main Menu]![Week] Text ( 255 );
> SELECT dbo_Despatch_data_branch_summary.Branch,
> (([Extd_REV]-[Extd_WCEV])/[Extd_REV]) AS [Input Margin]
> FROM dbo_Despatch_data_branch_summary
> WHERE (((dbo_Despatch_data_branch_summary.Year)=[Forms]![Main Menu]![Year])
> AND ((dbo_Despatch_data_branch_summary.Week)=[Forms]![Main Menu]![Week]))
> GROUP BY dbo_Despatch_data_branch_summary.Branch;
>
>
> "Audrey1980" wrote:
>
> > Hi
> >
> > I have the following query where I am getting store + input margin.
> > The problem is: it is printing out input margin per PG. I want to get an
> > average across all PGs for the branch (and year and week). I thought if I
> > just grouped by PG that would work but alas no. I've tried doing an average
> > of (([Extd_REV]-[Extd_WCEV])/[Extd_REV]) but it doesnt like this.
> >
> > Any other ideas?
> > Many thanks
> >
> > PARAMETERS [Forms]![Main Menu]![Store] Text ( 255 ), [Forms]![Main
> > Menu]![Year] Text ( 255 ), [Forms]![Main Menu]![Week] Text ( 255 );
> > SELECT dbo_Despatch_data_branch_summary.Branch,
> > (([Extd_REV]-[Extd_WCEV])/[Extd_REV]) AS [Input Margin]
> > FROM dbo_Despatch_data_branch_summary
> > WHERE (((dbo_Despatch_data_branch_summary.Year)=[Forms]![Main Menu]![Year])
> > AND ((dbo_Despatch_data_branch_summary.Week)=[Forms]![Main Menu]![Week]))
> > GROUP BY dbo_Despatch_data_branch_summary.Branch,
> > dbo_Despatch_data_branch_summary.Extd_WCEV,
> > dbo_Despatch_data_branch_summary.Extd_REV,
> > dbo_Despatch_data_branch_summary.PG;
> >

You have to remove Extd_WCEV and Extd_REV from the Group By statement. Even
though you do not have them in your select statement but in the group by your
query subdivides the results into three levels - Branch, Extd_WCEV, and
Extd_REV.


"Audrey1980" wrote:

> Karl
>
> Sorry this did work (once I included the 2 columns in the expression) but I
> got the result for each product! 64000 rows. I only want one for the store.
>
> Here's what I have now. I need to average out the input margin for the
> store. 1 row only required..
>
> PARAMETERS [Forms]![Main Menu]![Store] Text ( 255 ), [Forms]![Main
> Menu]![Year] Text ( 255 ), [Forms]![Main Menu]![Week] Text ( 255 );
> SELECT dbo_Despatch_data_branch_summary.Branch,
> (([Extd_REV]-[Extd_WCEV])/[Extd_REV]) AS [Input Margin]
> FROM dbo_Despatch_data_branch_summary
> WHERE (((dbo_Despatch_data_branch_summary.Year)=[Forms]![Main Menu]![Year])
> AND ((dbo_Despatch_data_branch_summary.Week)=[Forms]![Main Menu]![Week]))
> GROUP BY dbo_Despatch_data_branch_summary.Branch,
> dbo_Despatch_data_branch_summary.Extd_WCEV,
> dbo_Despatch_data_branch_summary.Extd_REV;
>
>
>
>
> "KARL DEWEY" wrote:
>
> > Try this --
> > PARAMETERS [Forms]![Main Menu]![Store] Text ( 255 ), [Forms]![Main
> > Menu]![Year] Text ( 255 ), [Forms]![Main Menu]![Week] Text ( 255 );
> > SELECT dbo_Despatch_data_branch_summary.Branch,
> > (([Extd_REV]-[Extd_WCEV])/[Extd_REV]) AS [Input Margin]
> > FROM dbo_Despatch_data_branch_summary
> > WHERE (((dbo_Despatch_data_branch_summary.Year)=[Forms]![Main Menu]![Year])
> > AND ((dbo_Despatch_data_branch_summary.Week)=[Forms]![Main Menu]![Week]))
> > GROUP BY dbo_Despatch_data_branch_summary.Branch;
> >
> >
> > "Audrey1980" wrote:
> >
> > > Hi
> > >
> > > I have the following query where I am getting store + input margin.
> > > The problem is: it is printing out input margin per PG. I want to get an
> > > average across all PGs for the branch (and year and week). I thought if I
> > > just grouped by PG that would work but alas no. I've tried doing an average
> > > of (([Extd_REV]-[Extd_WCEV])/[Extd_REV]) but it doesnt like this.
> > >
> > > Any other ideas?
> > > Many thanks
> > >
> > > PARAMETERS [Forms]![Main Menu]![Store] Text ( 255 ), [Forms]![Main
> > > Menu]![Year] Text ( 255 ), [Forms]![Main Menu]![Week] Text ( 255 );
> > > SELECT dbo_Despatch_data_branch_summary.Branch,
> > > (([Extd_REV]-[Extd_WCEV])/[Extd_REV]) AS [Input Margin]
> > > FROM dbo_Despatch_data_branch_summary
> > > WHERE (((dbo_Despatch_data_branch_summary.Year)=[Forms]![Main Menu]![Year])
> > > AND ((dbo_Despatch_data_branch_summary.Week)=[Forms]![Main Menu]![Week]))
> > > GROUP BY dbo_Despatch_data_branch_summary.Branch,
> > > dbo_Despatch_data_branch_summary.Extd_WCEV,
> > > dbo_Despatch_data_branch_summary.Extd_REV,
> > > dbo_Despatch_data_branch_summary.PG;
> > >