Automating parameter query 2

[TrekBiker]

My tblScores looks like this in simplified form.

Category1, Company1, Score1
Category1, Company2, Score2
Category1, Company3, Score3
Category1, Company4, Score4
Category2, Company5, Score1
Category2, Company6, Score2
Category2, Company7, Score3
Category2, Company8, Score4
etc

At present I'm using a form with Category and Company combos to set criteria for queries that process results.

Instead of stepping through each combination manually I like to automate the process along these lines.

For each Category
For each Company in that Category
Run some code
Next Company
Next Category

Any pointers to doing this would be much appreciated.

 

[Andrzejek]

Basd on your example it looks like Company field is unique.

So you can do this:
[pre]
rs.Open "SELECT * FROM tblScores ORDER BY 2"

Do Whilw Not(rs.EOF)
...Run some code
Loop
[/pre]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[TrekBiker]

Thanks Andy but not sure I'm further forward.

Seems to me I need code to

- start at Category1
- query to find companies in this categry, ie Company1,2,3,4
- for Company1 run the next query in my code, setting Category = Category1, Company = Company1
- then Category1, Company2
- then Category1, Company3
- then Category1, Company4

- then move to Category2 and do the same for Company5,6,7,8
- etc

It's just that I'm not clear how to do this.

 

[dhookom]

Are you running code for each company and category or executing a SQL statement? It would help if you provided more detail.

Duane
Hook'D on Access
MS Access MVP

 

[TrekBiker]

Sorry Duane, here's the full picture.

My starting table has fields Category, Company, Judge and eight scores from this judge.

In processing I'm using a combo on form Categories to manually pick a particular Category for the Where close in a first query TotalsPerJudge that totals the scores for each judge.

SELECT FullResults.Category, FullResults.Company, FullResults.[1], FullResults.[2], FullResults.[3], FullResults.[4], FullResults.[5], FullResults.[6], FullResults.[7], [1]+[2]+[3]+[4]+[5]+[6]+[7] AS TotalScore
FROM FullResults
WHERE (((FullResults.Category)=[forms]![Categories].[Category]))
ORDER BY [1]+[2]+[3]+[4]+[5]+[6]+[7] DESC;

I then aggregate the scores for all judges with query TotalsAllJudges.

SELECT TotalsPerJudge.Category, TotalsPerJudge.Company, Sum(TotalsPerJudge.TotalScore) AS SumOfTotalScore
FROM TotalsPerJudge
GROUP BY TotalsPerJudge.Category, TotalsPerJudge.Company
ORDER BY Sum(TotalsPerJudge.TotalScore) DESC;

I then rank the total scores for each company in this query, which also appends the results to table CategoryTotals

INSERT INTO CategoryTotals ( Category, Company, CategoryTotal, CategoryRank )
SELECT TotalsAllJudges.Category, TotalsAllJudges.Company, TotalsAllJudges.SumOfTotalScore, (SELECT Count([Company]) AS HowMany
  FROM TotalsAllJudges AS Dupe
  WHERE Dupe.SumOfTotalScore > TotalsAllJudges.SumOfTotalScore)+1 AS Rank
FROM TotalsAllJudges
ORDER BY TotalsAllJudges.SumOfTotalScore DESC;

What I'd like to do is automate this so a procedure starts with the first Category and does the append, moves to the next category and appends, and so on.

 

[dhookom]

I assume you want to rank by category. I think you could run your first query without the category filter. Then change the subquery to include AND Dupe.Category = TotalsAllJudges.Category.

Duane
Hook'D on Access
MS Access MVP

 

[TrekBiker]

I'm ranking by Total Score, ie for a category, sum the scores of the companies in that category.

What I'm after is the syntax for a block of code that runs through the sequence in my original question, ie

For each Category

For each Company in that Category​

Run the queries​

Next Company​

Next Category

Is it For/next, Do While, or what?

Many thanks for taking the time to respond.

 

[dhookom]

Again, I don't think you need to loop through anything. I would try queries like:

=== TotalsPerJudgeAllCategories ===

SELECT Category, Company, [1]+[2]+[3]+[4]+[5]+[6]+[7] AS TotalScore
FROM FullResults;

=== TotalsAllJudges =======

SELECT Category, Company, Sum(TotalScore) AS SumOfTotalScore
FROM TotalsPerJudgeAllCategories
GROUP BY Category, Company;

=== Final append query =====

INSERT INTO CategoryTotals ( Category, Company, CategoryTotal, CategoryRank )
SELECT TotalsAllJudges.Category, TotalsAllJudges.Company, TotalsAllJudges.SumOfTotalScore, 
   (SELECT Count([Company]) AS HowMany
    FROM TotalsAllJudges AS Dupe
     WHERE Dupe.SumOfTotalScore > TotalsAllJudges.SumOfTotalScore AND Dupe.Category = TotalsAllJudges.Category)+1 AS Rank
FROM TotalsAllJudges
ORDER BY TotalsAllJudges.SumOfTotalScore DESC;

Duane
Hook'D on Access
MS Access MVP

 

[TrekBiker]

Duane

You're an absolute star, many thanks, works a treat. Sorry not to have understood your earlier reply.

 

[PHV]

You may even get rid of the TotalsPerJudgeAllCategories query:

SELECT Category, Company, Sum([1]+[2]+[3]+[4]+[5]+[6]+[7]) AS SumOfTotalScore
FROM FullResults
GROUP BY Category, Company

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TrekBiker]

Thanks PHV, anything that simplifies the process is a help.

The next part of the analysis has an extra ingredient, where companies are scored by criteria as well as Category.

The starting data query NormalisedData has fields

Category
Company
Criterion
Score

This has a join to a table giving the number of judges in each Category.

I can't get all of the later stages of the analysis eg adding Ranks to work without running separate pairs of queries for each of 7 criteria. This is an one pair

Criteria2TotalsPerJudge

SELECT NormalisedResults.Category, NormalisedResults.Company, NormalisedResults.Criterion, Sum(NormalisedResults.Score) AS SumOfScore, Judges.Judges
FROM NormalisedResults INNER JOIN Judges ON (NormalisedResults.Company = Judges.Company) AND (NormalisedResults.Category = J/b]/u]udges.Category)
GROUP BY NormalisedResults.Category, NormalisedResults.Company, NormalisedResults.Criterion, Judges.Judges
HAVING (((NormalisedResults.Criterion)=2));

Criteria2TotalsWithRanks

INSERT INTO CriterionScores ( Category, Company, YourScore, YourPosition, Criterion, Judges )
SELECT Criteria2TotalsPerJudge.Category, Criteria2TotalsPerJudge.Company, Criteria2TotalsPerJudge.SumOfScore, (SELECT Count([Company]) AS HowMany     FROM Criteria2TotalsPerJudge AS Dupe      WHERE Dupe.SumOfScore > Criteria2TotalsPerJudge.SumOfScore AND Dupe.Category =Criteria2TotalsPerJudge.Category)+1 AS Rank, Criteria2TotalsPerJudge.Criterion, Criteria2TotalsPerJudge.Judges
FROM Criteria2TotalsPerJudge
ORDER BY Criteria2TotalsPerJudge.Category, Criteria2TotalsPerJudge.SumOfScore DESC;

Is it possible to avoid this repetition?

 

[TrekBiker]

No need to worry with this one thanks, have sorted out a way of doing it.