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

 

[Yukonbanzai]

Sorry, also should mention that it hi lights the first semi colon where I click OK

 

[wgcs]

I'm taking a stab at this: I think that every field that is not in an Aggregate function must be in the GROUP BY expression, though the error text doesn't really help with that conclusion:

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

 

[Yukonbanzai]

copied and pasted and still get the same error message at the first semi-colon.

Just to make sure I'm not the problem:
I open my select query in SQL view and paste this code in...Right?

 

[Yukonbanzai]

Tried a different approach and ended up with this code:

SELECT Cadet.HealthCare, Cadet.LastName, Cadet.FirstName, Avg(Grpng.G1) AS AvgOfG1
FROM Cadet LEFT JOIN Grpng ON Cadet.HealthCare=Grpng.HealthCare
GROUP BY Cadet.HealthCare, Cadet.LastName, Cadet.FirstName;

Created a form with the scores as a sub-form. Does what I want with the exception that as I enter ther scores, the average does not update until I close the form. Tried the "save record" and "refresh form" command buttons without success.

 

[wgcs]

I believe at the location you're using it (in a view), you want to leave the "INTO CURSOR AvgScores" off.

I was assuming the SQL code was being executed in a program.

I don't have much experience with views, so I'm not sure of how to force it to update.... since VFP doesn't have "sub Forms", I assume you mean the scores are in a Grid. You could try firing the "Requery" method of the Grid showing the Averages... ("refresh" doesn't cause a Requery... but Requery does.)

 

[craigsboyd]

Just a stab in the dark...is it possible that the code directly above this SQL statement ends with a semi-colon? If so, even if it is commented out code, remove that semi-colon! If not, then we have ruled out another cause for the problems you are having.

_{craig1442@mchsi.com}

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

 

[craigsboyd]

WHAT? Yukonbanzai, where is this code being used? I mean where are you typing it in at, exactly?

_{craig1442@mchsi.com}

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

 

[Yukonbanzai]

I owe all an apology. I'm working in Access not VFP.
The information I got here was helpful though.

I've been called a lot of things, but bright was probably not one of them.

Again, sorry