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 JanYTD, Feb YTD & 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
