Re: help with getting to end report
- From: "[MVP] S.Clark" <steve.clark_NOSPAM@xxxxxxxxxx>
- Date: Thu, 30 Mar 2006 12:30:32 -0500
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
"catherine2255" <catherinethomas53@xxxxxxxxx> wrote in message
news:1143708133.282783.253960@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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
.
- Follow-Ups:
- Re: help with getting to end report
- From: catherine2255
- Re: help with getting to end report
- References:
- help with getting to end report
- From: catherine2255
- help with getting to end report
- Prev by Date: Re: New Parameter values (from Form) not being picked up by Query
- Next by Date: Re: Add a range of dates to a table
- Previous by thread: help with getting to end report
- Next by thread: Re: help with getting to end report
- Index(es):
Relevant Pages
|
|
