better way to work with queries

[dharkangel]

Hi everyone,

I know there must be a better way to do what I am doing right now so I'm going to describe it. I have a Pivot Chart form being created from a query. The query is a UNION query on a bunch of other queries. The problem is, after you see what I've done, I'm going to have a HUGE amount of queries lying around. You'll see other queries within this one that look like this: BarcelonaRunningPercentageJan, BarcelonaRunningPercentageFeb, etc. There needs to be a query like this for every month and for about 10 programs (Barcelona, Tokyo) so as you can see this is going to get out of hand very soon.

Here is my UNION query that the Pivot Chart form is built from:
****************************************************
SELECT Weeks.WeekDate, BarcelonaRunningPercentageJan.BarcelonaClosedByCommitSum, BarcelonaRunningPercentageJan.BarcelonaWeeklyClosed, IIf(BarcelonaRunningPercentageJan.BarcelonaWeeklyClosed=0,0,BarcelonaRunningPercentageJan.BarcelonaClosedByCommitSum/BarcelonaRunningPercentageJan.BarcelonaWeeklyClosed) AS BarcelonaPercentage
FROM Weeks INNER JOIN BarcelonaRunningPercentageJan ON Weeks.ID=BarcelonaRunningPercentageJan.ID UNION SELECT Weeks.WeekDate, BarcelonaRunningPercentageFeb.BarcelonaClosedByCommitSum, BarcelonaRunningPercentageFeb.BarcelonaWeeklyClosed, IIf(BarcelonaRunningPercentageFeb.BarcelonaWeeklyClosed=0,0,BarcelonaRunningPercentageFeb.BarcelonaClosedByCommitSum/BarcelonaRunningPercentageFeb.BarcelonaWeeklyClosed) AS BarcelonaPercentage
FROM Weeks INNER JOIN BarcelonaRunningPercentageFeb ON Weeks.ID=BarcelonaRunningPercentageFeb.ID UNION SELECT Weeks.WeekDate, BarcelonaRunningPercentageMar.BarcelonaClosedByCommitSum, BarcelonaRunningPercentageMar.BarcelonaWeeklyClosed, IIf(BarcelonaRunningPercentageMar.BarcelonaWeeklyClosed=0,0,BarcelonaRunningPercentageMar.BarcelonaClosedByCommitSum/BarcelonaRunningPercentageMar.BarcelonaWeeklyClosed) AS BarcelonaPercentage
FROM Weeks INNER JOIN BarcelonaRunningPercentageMar ON Weeks.ID=BarcelonaRunningPercentageMar.ID UNION SELECT Weeks.WeekDate, BarcelonaRunningPercentageApr.BarcelonaClosedByCommitSum, BarcelonaRunningPercentageApr.BarcelonaWeeklyClosed, IIf(BarcelonaRunningPercentageApr.BarcelonaWeeklyClosed=0,0,BarcelonaRunningPercentageApr.BarcelonaClosedByCommitSum/BarcelonaRunningPercentageApr.BarcelonaWeeklyClosed) AS BarcelonaPercentage
FROM Weeks INNER JOIN BarcelonaRunningPercentageApr ON Weeks.ID=BarcelonaRunningPercentageApr.ID UNION SELECT Weeks.WeekDate, BarcelonaRunningPercentageMay.BarcelonaClosedByCommitSum, BarcelonaRunningPercentageMay.BarcelonaWeeklyClosed, IIf(BarcelonaRunningPercentageMay.BarcelonaWeeklyClosed=0,0,BarcelonaRunningPercentageMay.BarcelonaClosedByCommitSum/BarcelonaRunningPercentageMay.BarcelonaWeeklyClosed) AS BarcelonaPercentage
FROM Weeks INNER JOIN BarcelonaRunningPercentageMay ON Weeks.ID=BarcelonaRunningPercentageMay.ID UNION SELECT Weeks.WeekDate, BarcelonaRunningPercentageJun.BarcelonaClosedByCommitSum, BarcelonaRunningPercentageJun.BarcelonaWeeklyClosed, IIf(BarcelonaRunningPercentageJun.BarcelonaWeeklyClosed=0,0,BarcelonaRunningPercentageJun.BarcelonaClosedByCommitSum/BarcelonaRunningPercentageJun.BarcelonaWeeklyClosed) AS BarcelonaPercentage
FROM Weeks INNER JOIN BarcelonaRunningPercentageJun ON Weeks.ID=BarcelonaRunningPercentageJun.ID UNION SELECT Weeks.WeekDate, BarcelonaRunningPercentageJul.BarcelonaClosedByCommitSum, BarcelonaRunningPercentageJul.BarcelonaWeeklyClosed, IIf(BarcelonaRunningPercentageJul.BarcelonaWeeklyClosed=0,0,BarcelonaRunningPercentageJul.BarcelonaClosedByCommitSum/BarcelonaRunningPercentageJul.BarcelonaWeeklyClosed) AS BarcelonaPercentage
FROM Weeks INNER JOIN BarcelonaRunningPercentageJul ON Weeks.ID=BarcelonaRunningPercentageJul.ID UNION SELECT Weeks.WeekDate, BarcelonaRunningPercentageAug.BarcelonaClosedByCommitSum, BarcelonaRunningPercentageAug.BarcelonaWeeklyClosed, IIf(BarcelonaRunningPercentageAug.BarcelonaWeeklyClosed=0,0,BarcelonaRunningPercentageAug.BarcelonaClosedByCommitSum/BarcelonaRunningPercentageAug.BarcelonaWeeklyClosed) AS BarcelonaPercentage
FROM Weeks INNER JOIN BarcelonaRunningPercentageAug ON Weeks.ID=BarcelonaRunningPercentageAug.ID UNION SELECT Weeks.WeekDate, BarcelonaRunningPercentageSep.BarcelonaClosedByCommitSum, BarcelonaRunningPercentageSep.BarcelonaWeeklyClosed, IIf(BarcelonaRunningPercentageSep.BarcelonaWeeklyClosed=0,0,BarcelonaRunningPercentageSep.BarcelonaClosedByCommitSum/BarcelonaRunningPercentageSep.BarcelonaWeeklyClosed) AS BarcelonaPercentage
FROM Weeks INNER JOIN BarcelonaRunningPercentageSep ON Weeks.ID=BarcelonaRunningPercentageSep.ID UNION SELECT Weeks.WeekDate, BarcelonaRunningPercentageOct.BarcelonaClosedByCommitSum, BarcelonaRunningPercentageOct.BarcelonaWeeklyClosed, IIf(BarcelonaRunningPercentageOct.BarcelonaWeeklyClosed=0,0,BarcelonaRunningPercentageOct.BarcelonaClosedByCommitSum/BarcelonaRunningPercentageOct.BarcelonaWeeklyClosed) AS BarcelonaPercentage
FROM Weeks INNER JOIN BarcelonaRunningPercentageOct ON Weeks.ID=BarcelonaRunningPercentageOct.ID UNION SELECT Weeks.WeekDate, BarcelonaRunningPercentageNov.BarcelonaClosedByCommitSum, BarcelonaRunningPercentageNov.BarcelonaWeeklyClosed, IIf(BarcelonaRunningPercentageNov.BarcelonaWeeklyClosed=0,0,BarcelonaRunningPercentageNov.BarcelonaClosedByCommitSum/BarcelonaRunningPercentageNov.BarcelonaWeeklyClosed) AS BarcelonaPercentage
FROM Weeks INNER JOIN BarcelonaRunningPercentageNov ON Weeks.ID=BarcelonaRunningPercentageNov.ID UNION SELECT Weeks.WeekDate, BarcelonaRunningPercentageDec.BarcelonaClosedByCommitSum, BarcelonaRunningPercentageDec.BarcelonaWeeklyClosed, IIf(BarcelonaRunningPercentageDec.BarcelonaWeeklyClosed=0,0,BarcelonaRunningPercentageDec.BarcelonaClosedByCommitSum/BarcelonaRunningPercentageDec.BarcelonaWeeklyClosed) AS BarcelonaPercentage
FROM Weeks INNER JOIN BarcelonaRunningPercentageDec ON Weeks.ID=BarcelonaRunningPercentageDec.ID;
***************************************************
Here is one of the sub queries within this UNION query:
******************************************************
SELECT (SELECT Sum(BarcelonaWeeklyClosedByCommit) FROM ClosedByCommit Where ID<=A.ID AND ID>0 AND ID <6) AS BarcelonaClosedByCommitSum, (SELECT Sum(BarcelonaWeeklyTotalClosed) FROM ClosedByCommit Where ID<=A.ID AND ID>0 AND ID <6) AS BarcelonaWeeklyClosed, A.ID
FROM ClosedByCommit AS a
WHERE ID>0 And ID<6;
******************************************************
thank you,

SR

 

[Golom]

Your real problem (as I'm sure you're aware) is that your tables appear to be designed as though they were spreadsheets with a separate table for each city and a separate column for each month. The proper way to do this is to have a "City" field and a "Month" field and organize the data in rows rather than columns.

If that level of re-design isn't an option for you then I fear that you're stuck with massive blobs of SQL like this. Their only purpose in life is to create the normalized structure that you require for SQL and the relational database to work efficiently.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]