Post the SQL statements for all the queries. Open the query in design view.
Next go to View, SQL View and copy and past it here.
Upon further investigation, the further you go down the query tree,
the higher liklihood that records will be missing before even trying
to sort.
I'm quite embarrassed. I'm posting a subset of the queries below for
now, but If you need me to post all 26 queries I will... Quite
honestly if there is an obvious way to reorganize things so I wouldn't
have these absolutely ridiculous queries your input would be
appreciated.
At the first level I have "Topics" with specific "Weights" (I've
numbered these queries). These "Topics" are then organized into
"Categories" (Indicated by an alphabet letter). Finally, everything
is grouped into the "Super Query." The big issue is how to apply
specific weights to each category and I seem to have ended up with a
preposterous number of queries trying to accomplish this task.
Query 1
SELECT DISTINCTROW tblProjects.kProjectId, tblProjects.yNovelty, Abs
([ynovelty]) AS [Primary], [primary]*[nscore] AS Adjusted
FROM tblProjects, tlkpWeights
WHERE (((tlkpWeights.kWeights)=4));
Query 2
SELECT DISTINCTROW tblProjects.kProjectId, tblProjects.yPublishable,
Abs([ypublishable]) AS [Primary], [primary]*[nscore] AS Adjusted
FROM tblProjects, tlkpWeights
WHERE (((tlkpWeights.kWeights)=5));
Query 3
SELECT DISTINCTROW tblProjects.kProjectId, tblProjects.yStrategic, Abs
([yStrategic]) AS [Primary], [primary]*[nscore] AS Adjusted
FROM tblProjects, tlkpWeights
WHERE (((tlkpWeights.kWeights)=3));
Query A
SELECT DISTINCTROW tblProjects.kProjectId, qryAcademicNovelty.Primary
AS NoveltyP, qryAcademicNovelty.Adjusted AS NoveltyA,
qryAcademicPublishable.Primary AS PublishP,
qryAcademicPublishable.Adjusted AS PublishA,
qryAcademicStrategy.Primary AS StrategyP, qryAcademicStrategy.Adjusted
AS StrategyA, qryAcademicNovelty!Primary+qryAcademicPublishable!Primary
+qryAcademicStrategy!Primary AS [Academic Score], qryAcademicNovelty!
Adjusted+qryAcademicPublishable!Adjusted+qryAcademicStrategy!Adjusted
AS [Academic Adjusted]
FROM ((tblProjects INNER JOIN qryAcademicNovelty ON
tblProjects.kProjectId = qryAcademicNovelty.kProjectId) INNER JOIN
qryAcademicPublishable ON tblProjects.kProjectId =
qryAcademicPublishable.kProjectId) INNER JOIN qryAcademicStrategy ON
tblProjects.kProjectId = qryAcademicStrategy.kProjectId
GROUP BY tblProjects.kProjectId, qryAcademicNovelty.Primary,
qryAcademicNovelty.Adjusted, qryAcademicPublishable.Primary,
qryAcademicPublishable.Adjusted, qryAcademicStrategy.Primary,
qryAcademicStrategy.Adjusted, qryAcademicNovelty!Primary
+qryAcademicPublishable!Primary+qryAcademicStrategy!Primary,
qryAcademicNovelty!Adjusted+qryAcademicPublishable!Adjusted
+qryAcademicStrategy!Adjusted;
SuperQuery
SELECT tblProjects.kProjectId, tblProjects.tProjectName AS Name,
tblProjects.tAreaName AS Area, tblProjects.tConsultant AS Consultant,
tblProjects.tChef AS Chef, tblProjects.tProjectDescription AS
Description, tblProjects.yStart AS Started, tblProjects.dStart,
tblProjects.yComplete AS Completed, tblProjects.dComplete,
tblProjects.tProjectValidationId AS [Validation ID],
tblProjects.yNewProject AS [New Project], tblProjects.lCompliance AS
Compliance, qryScoreIndustry.CompliantP AS [Compliant Total],
qryScoreIndustry.CompliantA AS [Compliant Score],
tblProjects.lCompleteness AS Completeness, qryScoreIndustry.CompleteP
AS [Complete Total], qryScoreIndustry.CompleteA AS [Complete Score],
tblProjects.yAuditFailure AS [Audit Failure], qryScoreIndustry.AuditP
AS [Audit Total], qryScoreIndustry.AuditA AS [Audit Score],
qryScoreIndustry.[Industry Score] AS [Industry Total],
qryScoreIndustry.[Industry Adjusted] AS [Industry Score],
tblProjects.yUtility AS Utility, qryScorePuppy.UtilityP AS [Utility
Total], qryScorePuppy.UtilityA AS [Utility Score],
tblProjects.yExpertise AS Expertise, qryScorePuppy.ExpertiseP AS
[Expertise Total], qryScorePuppy.ExpertiseA AS [Expertise Score],
tblProjects.yWinsAvailable AS Wins, qryScorePuppy.WinP AS [Win Total],
qryScorePuppy.WinA AS [Win Score], tblProjects.yChefBus AS [Bus Chef],
qryScorePuppy.MolBusP AS [Bus Total], qryScorePuppy.MolBusA AS [Bus
Score], tblProjects.yChefOther AS [Other Chef],
qryScorePuppy.MolotherP AS [Other Total], qryScorePuppy.MolotherA AS
[Other Score], qryScorePuppy.[Puppy Score] AS [Puppy Total],
qryScorePuppy.[Puppy Adjusted] AS [Puppy Score],
tblProjects.lSamplesYear AS [Samples/Year], qryScoreBusiness.SamplesP
AS [Samples Total], qryScoreBusiness.SamplesA AS [Samples Score],
tblProjects.lSendOuts AS [Send-Outs/Year], qryScoreBusiness.SendoutP
AS [Send-Out Total], qryScoreBusiness.SendoutA AS [Send-Out Score],
tblProjects.lExpenses AS Expenses, qryScoreBusiness.ExpenseP AS
[Expense Total], qryScoreBusiness.ExpenseA AS [Expense Score],
tblProjects.yBuggyAlgorithm AS [Buggy Algorithm],
qryScoreBusiness.BuggyP AS [Buggy Total], qryScoreBusiness.BuggyA AS
[Buggy Score], tblProjects.yProcessImprovement AS Process,
qryScoreBusiness.ImproveP AS [Process Total],
qryScoreBusiness.ImproveA AS [Process Score],
tblProjects.yProfitability AS Profitable, qryScoreBusiness.ProfitP AS
[Profit Total], qryScoreBusiness.ProfitA AS [Profit Score],
tblProjects.yEquipmentAvailable AS Equipment,
qryScoreBusiness.EquipmentP AS [Equipment Total],
qryScoreBusiness.EquipmentA AS [Equipment Score], qryScoreBusiness.
[Business Score] AS [Business Total], qryScoreBusiness.[Business
Adjusted] AS [Business Score], tblProjects.lXYZEffort AS [XYZ Effort],
qryScoreABC.XYZP AS [XYZ Total], qryScoreABC.XYZA AS [XYZ Score],
tblProjects.yDevelopmentChef AS [Dev Chef], qryScoreABC.ChefP AS [Dev
Chef Total], qryScoreABC.ChefA AS [Dev Chef Score], qryScoreABC.[ABC
Score] AS [ABC Total], qryScoreABC.[ABC Adjusted] AS [ABC Score],
tblProjects.yStrategic AS Strategic, qryScoreAcademic.StrategyP AS
[Strategic Total], qryScoreAcademic.StrategyA AS [Strategic Score],
tblProjects.yNovelty AS Novel, qryScoreAcademic.NoveltyP AS [Novel
Total], qryScoreAcademic.NoveltyA AS [Novel Score],
tblProjects.yPublishable AS Publishable, qryScoreAcademic.PublishP AS
[Publish Total], qryScoreAcademic.PublishA AS [Publish Score],
qryScoreAcademic.[Academic Score] AS [Academic Total],
qryScoreAcademic.[Academic Adjusted] AS [Academic Score],
qryScoreAcademic![Academic Score]+qryScoreABC![ABC Score]
+qryScoreBusiness![Business Score]+qryScorePuppy![Puppy Score]
+qryScoreIndustry![Industry Score] AS Total, qryScoreAcademic!
[Academic Adjusted]+qryScoreABC![ABC Adjusted]+qryScoreBusiness!
[Business Adjusted]+qryScorePuppy![Puppy Adjusted]+qryScoreIndustry!
[Industry Adjusted] AS Score
FROM ((((tblProjects INNER JOIN qryScoreAcademic ON
tblProjects.kProjectId = qryScoreAcademic.kProjectId) INNER JOIN
qryScoreABC ON tblProjects.kProjectId = qryScoreABC.kProjectId) INNER
JOIN qryScoreBusiness ON tblProjects.kProjectId =
qryScoreBusiness.kProjectId) INNER JOIN qryScorePuppy ON
tblProjects.kProjectId = qryScorePuppy.kProjectId) INNER JOIN
qryScoreIndustry ON tblProjects.kProjectId =
qryScoreIndustry.kProjectId
ORDER BY tblProjects.yNewProject, qryScoreAcademic![Academic Adjusted]
+qryScoreABC![ABC Adjusted]+qryScoreBusiness![Business Adjusted]
+qryScorePuppy![Puppy Adjusted]+qryScoreIndustry![Industry Adjusted]
DESC;
