Dec 27 2009

Answer by Seth Spearman for How to be productive in Access VBA + SQL development?

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

Dec 23 2009

Comment by Seth Spearman on How to you WEIGHT returned values by a Group By value

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

Dec 22 2009

How to you WEIGHT returned values by a Group By value

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.

Dec 22 2009

Comment by Seth Spearman on How do I sum the results of a select that returns multiple rows

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

Dec 22 2009

How do I sum the results of a select that returns multiple rows

I have a SQL variable @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?

Dec 22 2009

How do I sum the results of a select that returns multiple rows

I have a SQL variable @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?

Dec 22 2009

How do I sum the results of a select that returns multiple rows

I have a SQL variable @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?

Dec 22 2009

How do I sum the results of a select that returns multiple rows

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

Dec 22 2009

Comment by Seth Spearman on How can I solve an application networking issue?

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

Dec 21 2009

Answer by Seth Spearman for Package tsql with application

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