This article is old but make a great case for considering Access as part of the enterprises long-term application strategy.

http://www.fmsinc.com/MicrosoftAccess/Strategy/index.asp

Seth

Dav,
What you have is exactly how I did it to start off. However, the score has to scale to a MAX POTENTIAL. That is where I am lacking information. If you just multiply by the PercentOfTotal then total score is reduced. because each line is multiplied by %. For the purposes of this algorithm that will not work.
Seth

I am trying to determine a SCORE from 11 rows in a table.

Those 11 rows are being aggregated into five rows using a ScoringCategoryID column as follows…

ScoringCategoryID   CategoryScore       PercentOfTotal
---------------------------------------------------------
7                   15.00	            0.40  
8                   15.00	            0.30  
9                   14.50	            0.20  
10                  4.50	            0.05  
11                  4.50	            0.05

I need to get a RawScore from this data. Unfortunately my customer does not want me to merely sum the CategoryScore column (53.5 total). Do you see how the PercentOfTotal column sums to 1. Each ScoringCategoryID therefore has a WEIGHT. So of the total score…ScoringCategoryID is supposed to be 40% of the score. ScorCatID 8 is supposed to be 30% of the total etc.

I am not sure how to do this in a query. How do I get the Score?

Here is the current query…

SELECT jc.ScoringCategoryID, 
       SUM(etjs.CalculatedScore) as CategoryScore,  
       Max(sc.PercentOfTotal) PercentOfTotalScore  
  FROM tblEventTurnJudgeScores etjs  
INNER JOIN tblJudgingCriteria jc ON  jc.JudgingCriteriaID = etjs.JudgingCriteriaID  
INNER JOIN tblScoringCategories sc ON jc.ScoringCategoryID = sc.ScoringCategoryID  
WHERE etjs.EventTurnJudgeID = 1068  
GROUP BY jc.ScoringCategoryID

This is supposed to be aggregated into a single score. Any ideas.

Thanks. That’s a good point. Group by is gone.

I have a SQL Var @SumScore dec(9,4)

I am trying to assign the variable as follows:

SET @SumScore =   
    	(  
    		SELECT Sum(  
    			(  
    				SELECT SUM(etjs.CalculatedScore * sc.PercentOfTotal) as CategoryScore   
    				FROM tblEventTurnJudgeScores etjs 
    					INNER JOIN tblJudgingCriteria jc ON  jc.JudgingCriteriaID = etjs.JudgingCriteriaID  
    					INNER JOIN tblScoringCategories sc ON jc.ScoringCategoryID = sc.ScoringCategoryID  
    				GROUP BY jc.JudgingCriteriaID  
    			) 
    		As ComputedScore) AS SumTotalScore  
    	)

In other words…the inner select is returning one column. I want the var to be assigned the SUM of all of the rows that are being return there.

I realize that this could be done with a temp table pretty easily. But is that the only way?

Thanks for your help.

Seth

curtisk, Thanks for asking. As of now…no luck. I even did the wireshare thing with one customer…but since I did it while connected to copilot the traffic was encrypted. At least there were NO references to the IP of our web services. Still working on it though. Thanks for your help. SEth

You might also want to check out VistaDB. It is syntactically compatible (although not perfectly) with sql server. All managed code. But it is not free.

SQL Server Compact Edition does not support stored procs, fyi. But is a single file deployment plus your data file.

SQLite is extremely fast and lightweight. Deployment is single file plus data file. But it has some syntactic limitations and has limitations to its dotnet integration (although I am pretty sure there IS a ado.net provider for it.)

I support an application built on sql express edition and it is a PAIN. It has a lot of install failures. (Probably about 10k installs over the last 3 years.) But if you need to power, hot backup, full sp and function support, connection pooling, etc it will work for you.

Never used mysql so I can’t comment.

Seth

Check out this for creating the PDF.

http://www.lebans.com/reporttopdf.htm

As for having a URL in the PDF…I don’t think it can be done. But I would wait for others to comment to be sure.

Seth

Hoob,

HAven’t tried it yet but I am sure this is it. I am both a JungleDisk user and a TSVN user. Thanks for the tip.

Seth

This is what I would have said. But Gerrie’s answer is a good one too