Averaging scores 1

[Yukonbanzai]

I'm trying to create a table or whatever to calculate cadet shooting averages.
Need to calculate for 30 to 40 shooters with a min of 50 scores each. Goal is to have a running average that updates as new scores are entered.

Any help appreciated

 

[baltman]

Don't know exactly what you're looking for, or how the data is stored, but you can do this with SQL if your data is agreeable:

SELECT CadetID,avg(score) GROUP BY CadetID from MyTable

 

[Yukonbanzai]

I've tried a variety of failed tables, but the one I currently am using has these fields:

cdtID
g1
g2
g3
and so on
g* being the score (0.0 to 6.0)

 

[BPeisch]

I would do this with 2 tables--one that stores the cadet's name, plus any other demographic info on the cadet, and a child table that holds scores with one record for each score. You probably don't need to store the average anywhere because it's easy enough to calculate it when you need it, based on a similar query to what baltman showed:

SELECT Cadets.CadetID, Cadets.FirstName, Cadets.LastName, ;
      AVG(Scores.score) as AvgScore ;
   FROM Cadets ;
      INNER JOIN Scores on Cadets.CadetId = Scores.CadetId ;
   GROUP BY CadetID ;
   INTO CURSOR AvgScores

-BP (Barbara Peisch)

http://www.peisch.com

 

[baltman]

I'd suggest a table like this:

CREATE TABLE scores (cdtID c(10),TestDT T, Gscore n(5,2))

You can then use the SQL and also track (and able to report/filter) scores by test time.

Brian

 

[Yukonbanzai]

BPeisch,
I put your code example into the SQL and I get an error stating that there are characters after the end of statement. It then highlites INTO CURSOR...

What did I do wrong????

 

[baltman]

This is how I'd do it:

SELECT Cadets.CadetID, Cadets.FirstName, Cadets.LastName, ;
AVG(Scores.score) as AvgScore ;
FROM Cadets, scores where Cadets.CadetId = Scores.CadetId ;
GROUP BY Cadets.CadetID INTO CURSOR AvgScores

 

[Yukonbanzai]

I still get an error
missing operator between CadetID and INTO

 

[TheRambler]

It would be easier for someone to help you if you posted your SELECT statement.

 

[Yukonbanzai]

Here is my select statement.

SELECT Data.HealthCare, Data.FirstName, Data.LastName,
AVG(Grpng.g1) as AvgScore
FROM Data, Grpng where Data.HealthCare = Grpng.HealthCare ;
GROUP BY Data.HealthCare INTO CURSOR AvgScores

 

[craigsboyd]

SELECT Data.HealthCare, Data.FirstName, Data.LastName, AVG(Grpng.g1) as AvgScore ;
FROM Data, Grpng where Data.HealthCare = Grpng.HealthCare ;
GROUP BY Data.HealthCare INTO CURSOR AvgScores

...you seem to be missing one or more line continuation characters ( from your select statement. Unlike languages such as C++, C#, Java, etc. in VFP whitespace matters.

_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​

 

[Stella740pl]

Does it look exactly like this in your code or you broke it into lines for Tek-Tips only? If it really looks like this, then semicolons should be at the end of each line. Absense of them could be a reason of "Missing operator" error.

 

[Yukonbanzai]

I tried this and got a message that characters found after end of SQL statement

SELECT Data.HealthCare, Data.FirstName, Data.LastName, AVG(Grpng.g1) as AvgScore ;
FROM Data, Grpng where Data.HealthCare = Grpng.HealthCare ;
GROUP BY Data.HealthCare INTO CURSOR AvgScores


This is a copy and paste of my statement

 

[TheRambler]

Notice the use of semicolons and try:

SELECT Data.HealthCare, ;
Data.FirstName, ;
Data.LastName, ;
AVG(Grpng.g1) as AvgScore ;
FROM Data, Grpng ;
WHERE Data.HealthCare = Grpng.HealthCare ;
GROUP BY Data.HealthCare ;
INTO CURSOR AvgScores

 

[Yukonbanzai]

AARRRGGG
Now it tells me that I'm using a reserved word or punctuation is wrong. Hilites the semi-colon after first HealthCare. If I delete it, it gives me the same error message at the next semi-colon

 

[craigsboyd]

Did you cut and paste the code directly as I wrote it out for you?

_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​

 

[Stella740pl]

Yukonbanzai,

The reserved word you use is DATA, and it seems to me the error has something to do with it. Although I was able to name the table Data in my VFP6 and select from it, by the messages you get I would suggest that you use a newer version, and I am not sure how it behaves in this case. Try to rename your table.

 

[Yukonbanzai]

Craig: I copied your text and pasted it in.
Stella: Renamed the Data table to Cadet.

Still get message that characters after end of statement

here is copy of my statement

SELECT Cadet.HealthCare, Cadet.FirstName, Cadet.LastName, AVG(Grpng.g1) as AvgScore ;
FROM Cadet, Grpng where Cadet.HealthCare = Grpng.HealthCare ;
GROUP BY Cadet.HealthCare INTO CURSOR AvgScores

Tables are currently named "Cadet" and "Grpng

 

[BPeisch]

What is the exact wording of the error message you are getting? If you can give an error number, that would help.


-BP (Barbara Peisch)

http://www.peisch.com

 

[Yukonbanzai]

The error message is
"The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect" (Error 3141)