Source language: Translate to:

Problem Using dbpAVG

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Problem Using dbpAVG

Postby carlos torres » Tue Nov 01, 2011 6:24 am

Hi everybody

TIA

I have 20 estudents and 10 different subjects. How can I get the average of the student marks for each different subjects?

I have an array with the subject codes then with a loop I've been trying to use a SQL query within a loop changing the subject code in order to get a different average each time but it doesn't work.

I'm stuck there!

I will appreciate any help with this.

regards,
carlost
User avatar
carlos torres
 
Posts: 289
Joined: Mon May 02, 2005 8:14 am
Location: Pamplona, Colombia

Postby Gaev » Tue Nov 01, 2011 7:25 am

carlost:

The general process would depend on whether records are structured like ...

Code: Select all
Student,MarksForSubject1,MarksForSubject2,MarksForSubject3 ... MarksForSubject10
... or ...
Code: Select all
Student,SubjectName,Marks
Student,SubjectName,Marks
etc.

If they are in the first format, the process would be to simply use the dbpAvg command 10 times, one for each Marks field.second format

However, if they are in the second format ...

1) Use dbpFieldToVar (NoDuplicates=Yes) ... to get a list of Subjects

2) use StrParse to make up an Array Variable from this list of Subjects

3) do a Loop/EndLoop for each of the Subjects in the Array Variable ... with each iteration, use dbpQuery to first filter the records for the particular Subject ... then dbpAvg to get the Marks for the filtered records.

If you still have difficulty doing it, please provide the structure of your database and the name of your ...

- Database (ID)
- Table
- Fields that store Subjects and Marks

... then people can provide the script.
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

About using dbpAVG

Postby carlos torres » Tue Nov 01, 2011 6:19 pm

Hi Gaev Thanks for your quick answer.

After I post the question I continued working around and I found a very similar solution.

I made three loops:
- # 1 to get the subjects code from the table Subjects (I figureout that your suggestion using dbpFieldToVar is faster, i will try it later)
Code: Select all
Loop "1" "[myDb.Subjects.$RecCount]" "[i]"
     dbpGotoRecord "myDb" "Subjects" "[i]"
     SetVar "[CodeSubject[i]]" "[myDb.Subject.Code]"
EndLoop

- # 2 to create temporal tables one for each subject assigning to a variable the new table name. it doesn't work in any other way. I select some fileds from the main Database "marks" to copy them into new temporary tables:
Mrks.CodeEstuden, Mrks.CodeSubject, Mrks.Mark, Mkrs.Group

Code: Select all
Loop "1" "[MaxNumberOfSubjects]" "[i]"
     Setvar "[NewTbl]" "tmp[i]"
     SetVAr "[SubjectCode]" "[SubCode[i]]"
     dbpExecSQL "Macafide" "SELECT  Mrks.Mark,..... INTO [NewTb]|FROM Mrks|WHERE (((Mrks.CodeSubject)=[#34][SubjectCode][#34]) AND ((Mrks.Group)=[#34][SelectedGroup][#34]));|" ""
endloop


- # 3 to get the averages of each table and delete/drop each temporal tables. I had to use this third loop because if I try to get the average inmediately to the new created table it doesn't work.
Code: Select all
Loop "1" "[MaxNumberOfSubjects]" "[i]"
     Setvar "[NewTb]" "tmp[i]"
     dbpOpenTable "myDb" "[NewTb]" ""
     dbpAvg "myDb" "[NewTb]" "Mark" "[Average[i]]"
     dbpDropTable "myDb" "[NewTb]"
Endloop


It works. I will try later some speedy solutions like the one you suggested.

Thanks again

regards,
carlost
User avatar
carlos torres
 
Posts: 289
Joined: Mon May 02, 2005 8:14 am
Location: Pamplona, Colombia


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest