Step through each field in query

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

John W. Vinson

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.

A Crosstab query may be the solution here. Have you tried one?

John W. Vinson [MVP]
 

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

Top