G
Guest
Hi
I have this sql query designed to show the top 10 records in a given field.
But I want to modify it so that I can use this one query to step through each
of the given fields and append them to the final report as opposed to having
to create a seperate query for each field.
The code is below and the fields go from [Total] all the way to [361+]. Thanks
INSERT INTO ZPayerReportAPAC ( reg3, LE, Custnum, Custname, Total,
[Current], [1-30], [31-60], [61-90], [91-120], [121-180], [181-360], [361+],
Type )
SELECT TOP 10 TotalTemplate.reg3, TotalTemplate.LE, TotalTemplate.Custnum,
TotalTemplate.Custname, Sum(TotalTemplate.Total) AS SumOfTotal,
Sum(TotalTemplate.Current) AS SumOfCurrent, Sum(TotalTemplate.[1-30]) AS
[SumOf1-30], Sum(TotalTemplate.[31-60]) AS [SumOf31-60],
Sum(TotalTemplate.[61-90]) AS [SumOf61-90], Sum(TotalTemplate.[91-120]) AS
[SumOf91-120], Sum(TotalTemplate.[121-180]) AS [SumOf121-180],
Sum(TotalTemplate.[181-360]) AS [SumOf181-360], Sum(TotalTemplate.[361+]) AS
[SumOf361+], TotalTemplate.Type
FROM TotalTemplate
GROUP BY TotalTemplate.reg3, TotalTemplate.LE, TotalTemplate.Custnum,
TotalTemplate.Custname, TotalTemplate.Type
HAVING (((TotalTemplate.reg3)="apacregion") AND ((TotalTemplate.Custnum) Is
Not Null) AND ((Sum(TotalTemplate.[121-180]))>0) AND
((TotalTemplate.Type)="External"))
ORDER BY Sum(TotalTemplate.[121-180]) DESC;
I have this sql query designed to show the top 10 records in a given field.
But I want to modify it so that I can use this one query to step through each
of the given fields and append them to the final report as opposed to having
to create a seperate query for each field.
The code is below and the fields go from [Total] all the way to [361+]. Thanks
INSERT INTO ZPayerReportAPAC ( reg3, LE, Custnum, Custname, Total,
[Current], [1-30], [31-60], [61-90], [91-120], [121-180], [181-360], [361+],
Type )
SELECT TOP 10 TotalTemplate.reg3, TotalTemplate.LE, TotalTemplate.Custnum,
TotalTemplate.Custname, Sum(TotalTemplate.Total) AS SumOfTotal,
Sum(TotalTemplate.Current) AS SumOfCurrent, Sum(TotalTemplate.[1-30]) AS
[SumOf1-30], Sum(TotalTemplate.[31-60]) AS [SumOf31-60],
Sum(TotalTemplate.[61-90]) AS [SumOf61-90], Sum(TotalTemplate.[91-120]) AS
[SumOf91-120], Sum(TotalTemplate.[121-180]) AS [SumOf121-180],
Sum(TotalTemplate.[181-360]) AS [SumOf181-360], Sum(TotalTemplate.[361+]) AS
[SumOf361+], TotalTemplate.Type
FROM TotalTemplate
GROUP BY TotalTemplate.reg3, TotalTemplate.LE, TotalTemplate.Custnum,
TotalTemplate.Custname, TotalTemplate.Type
HAVING (((TotalTemplate.reg3)="apacregion") AND ((TotalTemplate.Custnum) Is
Not Null) AND ((Sum(TotalTemplate.[121-180]))>0) AND
((TotalTemplate.Type)="External"))
ORDER BY Sum(TotalTemplate.[121-180]) DESC;