Audrey1980 - Group by

A

Audrey1980

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;
 
K

Ken Sheridan

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
 
K

KARL DEWEY

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;
 
A

Audrey1980

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 said:
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 said:
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;
Click to expand...
Click to expand...
 
A

Audrey1980

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 said:
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 said:
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;
Click to expand...
Click to expand...
 
K

KARL DEWEY

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 said:
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 said:
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 said:
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;
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

scaling of decimal value resulted in data truncation 0
Query not picking up form values 1
Average in a query after a number of rows 1
getting values from a form 2
Query question 3
Help with organising info 1
Need help gathering data from various files 1
Need help in getting the data organised so it can be used. 0

Top