help with getting to end report

[catherine2255]

Hi,
I know I have posted this to another post but any advice would help
really! Please let me know if you have any advice on an easier way to
do this:

Well, this is what I have:

Tables:

1. Tbl Customer
ID
Month (number)
Customer Code
Customer Name
Network
Product Code
Reason (the lines are A Grade, B Grade & Returns)
Quantity
Sales Value

2. Tbl Uplift
ID
Month (number)
Network
Product Code
Customer Code
Customer Name
Quantity
Uplift
Gross to Net

3. Tbl Scrap
ID
Month (number)
Network
Scrap
Rework
B Grade Quantity
(Deficit)/Surplus



I need a report to show the following by Network: but I also need it to
show the figures by month going across the report YTD so Jan = Jan
Figures, Feb = Jan + Feb Figures etc

A Returns % Calculated on the Report
(C/B)
B A Grade Sales Units From Tbl Customer Quantity WHERE
Reason = A Grade and Returns
C=A*B Budget Returns From Tbl Customer Quantity Column
where Reason =
Returns(reverse
polarity as returns are a negative figure)
D Average Uplift From Uplift Table columns
Sum of Gross to Net/Quantity
E Rework Cost From Rework Table columns
Rework/B Grade Quantity
F Scrap From Rework Table columns
Scrap/B Grade Quantity
G=D+E+F Total Disposal Cost Calculated on Report (G=D+E+F)
G*C Provision Required Calculated on Report (G*C)
I A Grade Sales Value From Customer Table
J=H/I Reserve Rate Calculated on NSV Where Reason
= A Grade AND
Returns


What I have done so far is:

1. To create a Union Query to group together all of the columns needed
for the report so that I can have everything on one query:

The result is: columns are as follows:

Network
Month
A Grade Sales Units
Budget Returns
SumOfGross to Net
Quantity
Scrap
Rework
B Grade Quantity
A Grade NSV

The Sql is as follows:

SELECT DISTINCTROW [Tbl Customer].Network, Sum([Tbl Customer].Quantity)
AS [A Grade Sales Units], 0.00 AS [Budget Returns],0.00 AS [SumOfGross
to Net], 0.00 AS [Quantity], 0.00 AS [Scrap], 0.00 AS [Rework], 0.00 AS
[B Grade Quantity], Sum([Tbl Customer].NSV) AS [A Grade NSV], [Tbl
Customer].Month
FROM [Tbl Customer]
WHERE ((([Tbl Customer].Reason)="A Grade" Or ([Tbl
Customer].Reason)="Returns"))
GROUP BY [Tbl Customer].Network, [Tbl Customer].Month
UNION SELECT DISTINCTROW [Tbl Customer].Network,0.00 AS [A Grade Sales
Units], -Sum([Tbl Customer]![Quantity]) AS [Budget Returns], 0.00 AS
[SumOfGross to Net], 0.00 AS [Quantity], 0.00 AS [Scrap], 0.00 AS
[Rework], 0.00 AS [B Grade Quantity], 0.00 AS [A Grade NSV], [Tbl
Customer].Month
FROM [Tbl Customer]
WHERE ((([Tbl Customer].Reason)="Returns"))
GROUP BY [Tbl Customer].Network, [Tbl Customer].Month;
UNION SELECT DISTINCTROW [Tbl uplift].Network,0.00 AS [A Grade Sales
Units], 0.00 AS [Budget Returns], Sum([Tbl uplift].[Gross to Net]) AS
[SumOfGross to Net], Sum([Tbl uplift].Quantity) AS Quantity, 0.00 AS
[Scrap], 0.00 AS [Rework], 0.00 AS [B Grade Quantity], 0.00 AS [A Grade
NSV], [Tbl uplift].Month
FROM [Tbl uplift]
GROUP BY [Tbl uplift].Network, [Tbl uplift].Month;
UNION SELECT DISTINCTROW [Tbl Scrap].Network, 0.00 AS [A Grade Sales
Units], 0.00 AS [Budget Returns], 0.00 AS [SumOfGross to Net], 0.00 AS
[Quantity], Sum([Tbl Scrap].Scrap) AS Scrap, Sum([Tbl Scrap].Rework) AS
Rework, Sum([Tbl Scrap].[B Grade Quantity]) AS [B Grade Quantity],
0.00 AS [A Grade NSV], [Tbl Scrap].Month
FROM [Tbl uplift], [Tbl Scrap]
GROUP BY [Tbl Scrap].Network, [Tbl Scrap].Month;

2. I then created another query to summarise the first query so that I
had one line per Month per network.
My columns are now:

Network
Month
Sum Of A Grade Sales Units
Sum Of Budget Returns
Sum Of SumOfGross to Net
Sum Of Quantity
Sum Of Scrap
Sum Of Rework
Sum Of B Grade Quantity
Sum Of A Grade NSV


SQL is :
SELECT DISTINCTROW [1 Group Together].Network, [1 Group
Together].Month, Sum([1 Group Together].[A Grade Sales Units]) AS [Sum
Of A Grade Sales Units], Sum([1 Group Together].[Budget Returns]) AS
[Sum Of Budget Returns], Sum([1 Group Together].[SumOfGross to Net])
AS [Sum Of SumOfGross to Net], Sum([1 Group Together].Quantity) AS
[Sum Of Quantity], Sum([1 Group Together].Scrap) AS [Sum Of Scrap],
Sum([1 Group Together].Rework) AS [Sum Of Rework], Sum([1 Group
Together].[B Grade Quantity]) AS [Sum Of B Grade Quantity], Sum([1
Group Together].[A Grade NSV]) AS [Sum Of A Grade NSV]
FROM [1 Group Together]
GROUP BY [1 Group Together].Network, [1 Group Together].Month;


What I wanted to do next was:

Create a new query by network to create the Running total of each
column using the Month to create the running total on.

Then after that I am stumped.
I assumed I would have to create a report one for each month to pick up
the monthly figures on the Queries. (which would mean a lot of other
queries setup). I need the option to select the report for each
individual month. E.g Run March & you get Jan, Feb & Mar YTD showing
on the report.

Sorry this is so long winded!

If anyone can think of an easier way for me to get to my final report I
would be very grateful!

Thanks!

Catherine

 

[[MVP] S.Clark]

I'm not a fan of Union queries. I prefer to create a table, that holds the
final structure of the data that I need for the report.

Then I use a query, or a series of queries(typically action queries like
APPEND and UPDATE), to bring the data from the normalized form to the end
result.

In some cases, I may end up with 2 or more tables and many queries to get
the final job done.

HTH,


--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Hi,
I know I have posted this to another post but any advice would help
really! Please let me know if you have any advice on an easier way to
do this:

Well, this is what I have:

Tables:

1. Tbl Customer
ID
Month (number)
Customer Code
Customer Name
Network
Product Code
Reason (the lines are A Grade, B Grade & Returns)
Quantity
Sales Value

2. Tbl Uplift
ID
Month (number)
Network
Product Code
Customer Code
Customer Name
Quantity
Uplift
Gross to Net

3. Tbl Scrap
ID
Month (number)
Network
Scrap
Rework
B Grade Quantity
(Deficit)/Surplus



I need a report to show the following by Network: but I also need it to
show the figures by month going across the report YTD so Jan = Jan
Figures, Feb = Jan + Feb Figures etc

A Returns % Calculated on the Report
(C/B)
B A Grade Sales Units From Tbl Customer Quantity WHERE
Reason = A Grade and Returns
C=A*B Budget Returns From Tbl Customer Quantity Column
where Reason =
Returns(reverse
polarity as returns are a negative figure)
D Average Uplift From Uplift Table columns
Sum of Gross to Net/Quantity
E Rework Cost From Rework Table columns
Rework/B Grade Quantity
F Scrap From Rework Table columns
Scrap/B Grade Quantity
G=D+E+F Total Disposal Cost Calculated on Report (G=D+E+F)
G*C Provision Required Calculated on Report (G*C)
I A Grade Sales Value From Customer Table
J=H/I Reserve Rate Calculated on NSV Where Reason
= A Grade AND
Returns


What I have done so far is:

1. To create a Union Query to group together all of the columns needed
for the report so that I can have everything on one query:

The result is: columns are as follows:

Network
Month
A Grade Sales Units
Budget Returns
SumOfGross to Net
Quantity
Scrap
Rework
B Grade Quantity
A Grade NSV

The Sql is as follows:

SELECT DISTINCTROW [Tbl Customer].Network, Sum([Tbl Customer].Quantity)
AS [A Grade Sales Units], 0.00 AS [Budget Returns],0.00 AS [SumOfGross
to Net], 0.00 AS [Quantity], 0.00 AS [Scrap], 0.00 AS [Rework], 0.00 AS
[B Grade Quantity], Sum([Tbl Customer].NSV) AS [A Grade NSV], [Tbl
Customer].Month
FROM [Tbl Customer]
WHERE ((([Tbl Customer].Reason)="A Grade" Or ([Tbl
Customer].Reason)="Returns"))
GROUP BY [Tbl Customer].Network, [Tbl Customer].Month
UNION SELECT DISTINCTROW [Tbl Customer].Network,0.00 AS [A Grade Sales
Units], -Sum([Tbl Customer]![Quantity]) AS [Budget Returns], 0.00 AS
[SumOfGross to Net], 0.00 AS [Quantity], 0.00 AS [Scrap], 0.00 AS
[Rework], 0.00 AS [B Grade Quantity], 0.00 AS [A Grade NSV], [Tbl
Customer].Month
FROM [Tbl Customer]
WHERE ((([Tbl Customer].Reason)="Returns"))
GROUP BY [Tbl Customer].Network, [Tbl Customer].Month;
UNION SELECT DISTINCTROW [Tbl uplift].Network,0.00 AS [A Grade Sales
Units], 0.00 AS [Budget Returns], Sum([Tbl uplift].[Gross to Net]) AS
[SumOfGross to Net], Sum([Tbl uplift].Quantity) AS Quantity, 0.00 AS
[Scrap], 0.00 AS [Rework], 0.00 AS [B Grade Quantity], 0.00 AS [A Grade
NSV], [Tbl uplift].Month
FROM [Tbl uplift]
GROUP BY [Tbl uplift].Network, [Tbl uplift].Month;
UNION SELECT DISTINCTROW [Tbl Scrap].Network, 0.00 AS [A Grade Sales
Units], 0.00 AS [Budget Returns], 0.00 AS [SumOfGross to Net], 0.00 AS
[Quantity], Sum([Tbl Scrap].Scrap) AS Scrap, Sum([Tbl Scrap].Rework) AS
Rework, Sum([Tbl Scrap].[B Grade Quantity]) AS [B Grade Quantity],
0.00 AS [A Grade NSV], [Tbl Scrap].Month
FROM [Tbl uplift], [Tbl Scrap]
GROUP BY [Tbl Scrap].Network, [Tbl Scrap].Month;

2. I then created another query to summarise the first query so that I
had one line per Month per network.
My columns are now:

Network
Month
Sum Of A Grade Sales Units
Sum Of Budget Returns
Sum Of SumOfGross to Net
Sum Of Quantity
Sum Of Scrap
Sum Of Rework
Sum Of B Grade Quantity
Sum Of A Grade NSV


SQL is :
SELECT DISTINCTROW [1 Group Together].Network, [1 Group
Together].Month, Sum([1 Group Together].[A Grade Sales Units]) AS [Sum
Of A Grade Sales Units], Sum([1 Group Together].[Budget Returns]) AS
[Sum Of Budget Returns], Sum([1 Group Together].[SumOfGross to Net])
AS [Sum Of SumOfGross to Net], Sum([1 Group Together].Quantity) AS
[Sum Of Quantity], Sum([1 Group Together].Scrap) AS [Sum Of Scrap],
Sum([1 Group Together].Rework) AS [Sum Of Rework], Sum([1 Group
Together].[B Grade Quantity]) AS [Sum Of B Grade Quantity], Sum([1
Group Together].[A Grade NSV]) AS [Sum Of A Grade NSV]
FROM [1 Group Together]
GROUP BY [1 Group Together].Network, [1 Group Together].Month;


What I wanted to do next was:

Create a new query by network to create the Running total of each
column using the Month to create the running total on.

Then after that I am stumped.
I assumed I would have to create a report one for each month to pick up
the monthly figures on the Queries. (which would mean a lot of other
queries setup). I need the option to select the report for each
individual month. E.g Run March & you get Jan, Feb & Mar YTD showing
on the report.

Sorry this is so long winded!

If anyone can think of an easier way for me to get to my final report I
would be very grateful!

Thanks!

Catherine

 

[catherine2255]

Hi,
how do I create the table automatically? do I do this with vba or can I
do it with sql?

 

[Vincent Johns]

You might be able to create your combined Tables with SQL (or in a
Macro), depending on just what you want to accomplish.

However, you might consider using a set of Crosstab Queries to display
your results. Consider the following example...

Since you didn't post any example data, I made up some. (Most of the
following datasheet views are folded to fit several fields onto one
line, without losing the headers.)


[Tbl Customer] Table Datasheet View:

Customer_ID Month Customer Customer Network
Code Name
----------- ----- -------- -------- -------
-1513544438 3 abc Rasputin xyz
-72006585 2 Ccode John Doe xyz

Product Reason Quantity Sales Value NSV
Code
------- ------- -------- ----------- -----
p23 Returns 3 $20.00 $4.00
Pcode A Grade 1 $10.00 $3.00


[Tbl Scrap] Table Datasheet View:

Scrap_ID Month Network Scrap Rework
-------- ----- ------- ----- ------
150260348 3 xyz 33 2

B Grade (Deficit)/Surplus
Quantity
--------- -----------------
3 -20


[Tbl Uplift] Table Datasheet View:

Uplift_ID Month Network Product Customer
Code Code
--------- ----- ------- ------- --------
-1275920435 2 xyz 223 24
1814200003 3 xyz 84 13

Customer Name Quantity Uplift Gross
to Net
-------- ---- -------- ------ -----
Jane Doe 27 3 -4
Jim Stuart -3 7 21


To make your Union Query easier to edit, I gave each SELECT part a name.
This allowed me to edit each in Query Design View, although I actually
didn't change much.

[Q_010CustGradeA] SQL:

SELECT DISTINCTROW [Tbl Customer].Network,
Sum([Tbl Customer].Quantity) AS [A Grade Sales Units],
0 AS [Budget Returns], 0 AS [SumOfGross to Net],
0 AS Quantity, 0 AS Scrap, 0 AS Rework,
0 AS [B Grade Quantity],
Sum([Tbl Customer].NSV) AS [A Grade NSV],
[Tbl Customer].Month
FROM [Tbl Customer]
WHERE ((([Tbl Customer].Reason)="A Grade"
Or ([Tbl Customer].Reason)="Returns"))
GROUP BY [Tbl Customer].Network,
[Tbl Customer].Month;

The results of this first one look like this...

[Q_010CustGradeA] Query Datasheet View:

Network A Grade Budget SumOfGross
Sales Units Returns to Net
------- ----------- ------- ----------
xyz 1 0 0
xyz 3 0 0

Quantity Scrap Rework B Grade A Grade Month
Quantity NSV
-------- ----- ------ ------- ------- -----
0 0 0 0 $3.00 2
0 0 0 0 $4.00 3


[Q_020CustReturns] SQL:

SELECT DISTINCTROW [Tbl Customer].Network,
0 AS [A Grade Sales Units],
-Sum([Tbl Customer]![Quantity]) AS [Budget Returns],
0 AS [SumOfGross to Net], 0 AS Quantity,
0 AS Scrap, 0 AS Rework, 0 AS [B Grade Quantity],
0 AS [A Grade NSV], [Tbl Customer].Month
FROM [Tbl Customer]
WHERE ((([Tbl Customer].Reason)="Returns"))
GROUP BY [Tbl Customer].Network,
[Tbl Customer].Month;

[Q_030Uplift] SQL:

SELECT DISTINCTROW [Tbl uplift].Network,
0 AS [A Grade Sales Units], 0 AS [Budget Returns],
Sum([Tbl uplift].[Gross to Net])
AS [SumOfGross to Net],
Sum([Tbl uplift].Quantity) AS Quantity,
0 AS Scrap, 0 AS Rework, 0 AS [B Grade Quantity],
0 AS [A Grade NSV], [Tbl uplift].Month
FROM [Tbl uplift]
GROUP BY [Tbl uplift].Network, [Tbl uplift].Month;

This next one was kind of puzzling. Since you call for [Tbl uplift]
without ever using it, the effect (if you hadn't used DISTINCTROW) would
have been to multiply your summed values by the number of records in
[Tbl uplift] -- not especially useful, IMHO. Unless there's a reason
for it to be there, I suggest taking it out.

[Q_040Scrap] SQL:

SELECT DISTINCTROW [Tbl Scrap].Network,
0 AS [A Grade Sales Units], 0 AS [Budget Returns],
0 AS [SumOfGross to Net], 0 AS Quantity,
Sum([Tbl Scrap].Scrap) AS Scrap,
Sum([Tbl Scrap].Rework) AS Rework,
Sum([Tbl Scrap].[B Grade Quantity])
AS [B Grade Quantity],
0 AS [A Grade NSV], [Tbl Scrap].Month
FROM [Tbl uplift], [Tbl Scrap]
GROUP BY [Tbl Scrap].Network, [Tbl Scrap].Month;

OK, now that we have these Queries defined, it's easy to collect them
with a Union Query. (It's actually about the same as yours, just
expressed in a way that I think might be easier to maintain.)

[1 Group Together] SQL:

SELECT * from [Q_010CustGradeA]
UNION SELECT * from [Q_020CustReturns]
UNION SELECT * from [Q_030Uplift]
UNION SELECT * from [Q_040Scrap];

The results look like this, given my example data:

[1 Group Together] Query Datasheet View:

Network A Grade Sales Budget SumOfGross
Units Returns to Net
------- ------------- ------- ----------
xyz 0 -3 0
xyz 0 0 -4
xyz 0 0 0
xyz 0 0 21
xyz 1 0 0
xyz 3 0 0

Quantity Scrap Rework B Grade A Grade Month
Quantity NSV
-------- ----- ------ -------- ------- -----
0 0 0 0 $0.00 3
27 0 0 0 $0.00 2
0 33 2 3 $0.00 3
-3 0 0 0 $0.00 3
0 0 0 0 $3.00 2
0 0 0 0 $4.00 3

Now, your Query that sums the results might look like this (but I
changed your SQL to abbreviate the name of the Union Query):

[Q_060Sums] SQL:

SELECT DISTINCTROW GT.Network, GT.Month,
Sum(GT.[A Grade Sales Units])
AS [Sum Of A Grade Sales Units],
Sum(GT.[Budget Returns])
AS [Sum Of Budget Returns],
Sum(GT.[SumOfGross to Net])
AS [Sum Of SumOfGross to Net],
Sum(GT.Quantity) AS [Sum Of Quantity],
Sum(GT.Scrap) AS [Sum Of Scrap],
Sum(GT.Rework) AS [Sum Of Rework],
Sum(GT.[B Grade Quantity])
AS [Sum Of B Grade Quantity],
Sum(GT.[A Grade NSV]) AS [Sum Of A Grade NSV]
FROM [1 Group Together] AS GT
GROUP BY GT.Network, GT.Month
ORDER BY GT.Network, GT.Month;

Its results look like this:

[Q_060Sums] Query Datasheet View:

Network Month Sum Of A Grade Sum Of Budget
Sales Units Returns
------- ----- -------------- -------------
xyz 2 1 0
xyz 3 3 -3

Sum Of SumOfGross Sum Of Sum Of Sum Of
to Net Quantity Scrap Rework
----------------- -------- ------ ------
-4 27 0 0
21 -3 33 2

Sum Of B Grade Sum Of A
Quantity Grade NSV
-------------- ---------
0 $3.00
3 $4.00

Now you can display the totals by month, with each network on a separate
line (I suppose I should have included 2 networks... but you'll see them
if you try running this). Each of the summed quantities that you
display will need its own Crosstab Query. This one is for [Sum Of A
Grade NSV], with totals by month for each network. You could define a
similar one for [Sum Of B Grade Quantity], etc.

What I did with the column headers was to display both the month number
and and abbreviated name, such as "02 Feb" or "11 Nov". This keeps them
in order, but it's instead possible to specify which months you'd like
to see listed (there're a couple of ways to do that).

[Q_070Xtab A Grade NSV] SQL:

TRANSFORM Sum(Q6.[Sum Of A Grade NSV])
AS [SumOfSum Of A Grade NSV]
SELECT Q6.Network,
Sum(Q6.[Sum Of A Grade NSV]) AS Totals
FROM Q_060Sums AS Q6
GROUP BY Q6.Network
PIVOT Format$([Month],"00 ")
& Format$(DateSerial(2000,[Month],1),"mmm");

[Q_070Xtab A Grade NSV] Query Datasheet View:

Network Totals 02 Feb 03 Mar
------- ------ ------ ------
xyz $7.00 $3.00 $4.00


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

Hi,
how do I create the table automatically? do I do this with vba or can I
do it with sql?

I'm not a fan of Union queries. I prefer to create a table, that holds the
final structure of the data that I need for the report.

Then I use a query, or a series of queries(typically action queries like
APPEND and UPDATE), to bring the data from the normalized form to the end
result.

In some cases, I may end up with 2 or more tables and many queries to get
the final job done.

HTH,

Hi,
I know I have posted this to another post but any advice would help
really! Please let me know if you have any advice on an easier way to
do this:

Well, this is what I have:

Tables:

1. Tbl Customer
ID
Month (number)
Customer Code
Customer Name
Network
Product Code
Reason (the lines are A Grade, B Grade & Returns)
Quantity
Sales Value

2. Tbl Uplift
ID
Month (number)
Network
Product Code
Customer Code
Customer Name
Quantity
Uplift
Gross to Net

3. Tbl Scrap
ID
Month (number)
Network
Scrap
Rework
B Grade Quantity
(Deficit)/Surplus



I need a report to show the following by Network: but I also need it to
show the figures by month going across the report YTD so Jan = Jan
Figures, Feb = Jan + Feb Figures etc

A Returns % Calculated on the Report
(C/B)
B A Grade Sales Units From Tbl Customer Quantity WHERE
Reason = A Grade and Returns
C=A*B Budget Returns From Tbl Customer Quantity Column
where Reason =
Returns(reverse
polarity as returns are a negative figure)
D Average Uplift From Uplift Table columns
Sum of Gross to Net/Quantity
E Rework Cost From Rework Table columns
Rework/B Grade Quantity
F Scrap From Rework Table columns
Scrap/B Grade Quantity
G=D+E+F Total Disposal Cost Calculated on Report (G=D+E+F)
G*C Provision Required Calculated on Report (G*C)
I A Grade Sales Value From Customer Table
J=H/I Reserve Rate Calculated on NSV Where Reason
= A Grade AND
Returns


What I have done so far is:

1. To create a Union Query to group together all of the columns needed
for the report so that I can have everything on one query:

The result is: columns are as follows:

Network
Month
A Grade Sales Units
Budget Returns
SumOfGross to Net
Quantity
Scrap
Rework
B Grade Quantity
A Grade NSV

The Sql is as follows:

SELECT DISTINCTROW [Tbl Customer].Network, Sum([Tbl Customer].Quantity)
AS [A Grade Sales Units], 0.00 AS [Budget Returns],0.00 AS [SumOfGross
to Net], 0.00 AS [Quantity], 0.00 AS [Scrap], 0.00 AS [Rework], 0.00 AS
[B Grade Quantity], Sum([Tbl Customer].NSV) AS [A Grade NSV], [Tbl
Customer].Month
FROM [Tbl Customer]
WHERE ((([Tbl Customer].Reason)="A Grade" Or ([Tbl
Customer].Reason)="Returns"))
GROUP BY [Tbl Customer].Network, [Tbl Customer].Month
UNION SELECT DISTINCTROW [Tbl Customer].Network,0.00 AS [A Grade Sales
Units], -Sum([Tbl Customer]![Quantity]) AS [Budget Returns], 0.00 AS
[SumOfGross to Net], 0.00 AS [Quantity], 0.00 AS [Scrap], 0.00 AS
[Rework], 0.00 AS [B Grade Quantity], 0.00 AS [A Grade NSV], [Tbl
Customer].Month
FROM [Tbl Customer]
WHERE ((([Tbl Customer].Reason)="Returns"))
GROUP BY [Tbl Customer].Network, [Tbl Customer].Month;
UNION SELECT DISTINCTROW [Tbl uplift].Network,0.00 AS [A Grade Sales
Units], 0.00 AS [Budget Returns], Sum([Tbl uplift].[Gross to Net]) AS
[SumOfGross to Net], Sum([Tbl uplift].Quantity) AS Quantity, 0.00 AS
[Scrap], 0.00 AS [Rework], 0.00 AS [B Grade Quantity], 0.00 AS [A Grade
NSV], [Tbl uplift].Month
FROM [Tbl uplift]
GROUP BY [Tbl uplift].Network, [Tbl uplift].Month;
UNION SELECT DISTINCTROW [Tbl Scrap].Network, 0.00 AS [A Grade Sales
Units], 0.00 AS [Budget Returns], 0.00 AS [SumOfGross to Net], 0.00 AS
[Quantity], Sum([Tbl Scrap].Scrap) AS Scrap, Sum([Tbl Scrap].Rework) AS
Rework, Sum([Tbl Scrap].[B Grade Quantity]) AS [B Grade Quantity],
0.00 AS [A Grade NSV], [Tbl Scrap].Month
FROM [Tbl uplift], [Tbl Scrap]
GROUP BY [Tbl Scrap].Network, [Tbl Scrap].Month;

2. I then created another query to summarise the first query so that I
had one line per Month per network.
My columns are now:

Network
Month
Sum Of A Grade Sales Units
Sum Of Budget Returns
Sum Of SumOfGross to Net
Sum Of Quantity
Sum Of Scrap
Sum Of Rework
Sum Of B Grade Quantity
Sum Of A Grade NSV


SQL is :
SELECT DISTINCTROW [1 Group Together].Network, [1 Group
Together].Month, Sum([1 Group Together].[A Grade Sales Units]) AS [Sum
Of A Grade Sales Units], Sum([1 Group Together].[Budget Returns]) AS
[Sum Of Budget Returns], Sum([1 Group Together].[SumOfGross to Net])
AS [Sum Of SumOfGross to Net], Sum([1 Group Together].Quantity) AS
[Sum Of Quantity], Sum([1 Group Together].Scrap) AS [Sum Of Scrap],
Sum([1 Group Together].Rework) AS [Sum Of Rework], Sum([1 Group
Together].[B Grade Quantity]) AS [Sum Of B Grade Quantity], Sum([1
Group Together].[A Grade NSV]) AS [Sum Of A Grade NSV]
FROM [1 Group Together]
GROUP BY [1 Group Together].Network, [1 Group Together].Month;


What I wanted to do next was:

Create a new query by network to create the Running total of each
column using the Month to create the running total on.

Then after that I am stumped.
I assumed I would have to create a report one for each month to pick up
the monthly figures on the Queries. (which would mean a lot of other
queries setup). I need the option to select the report for each
individual month. E.g Run March & you get Jan, Feb & Mar YTD showing
on the report.

Sorry this is so long winded!

If anyone can think of an easier way for me to get to my final report I
would be very grateful!

Thanks!

Catherine