Need a little help with a SQL count calc

[Rich S]

Howdy, all:

Please refer to the attached.

Background: This is a "flat" file that, unfortunately, should've been created using a parent-child relationship where the StudentID number should've been a primary/foreign key, but alas, this is what I inherited to work on; there are multiple records for the same student. I don't know how to easily create a parent-child relationship where all you have a children-type records so I thought rather than going through creative T.O. mapping and calcs, SQL might handle the job neater and more elegantly. The report will be sub-summarized by student.

In this form, there are 17 fields where users can choose a rating of 1, 2, 3, or N/A. What I need to do count (and total) how many 1's, 2's, 3's and N/A's were assigned per question/field. For example, in Screenshot 1, for question 1a (field name: DANIELSON_FORM::_1a_DemoKnowledgeContent_value__lxt): it would have a Rating (value) of 1, 2, 3, or N/A. In Screen Shot 2, let's say there are five records belonging to a student where the entered values are 1-1-3-2-2 . Under OSTE >Counts > 1, on the 1a row, column 1 should populate with 2; column 2 should populate with 2; column 3 should populate with 1, and the N/A should either be empty or populate with 0. In addition, there are two separate sets of calcs: one for OSTE's and the other for Supervisors, so they need to be tallied separately, using the Observer__lxt field.

The calc that I have so far isn't working--it's for 1a:

ExecuteSQL (

"SELECT
COUNT ( \"_1a_DemoKnowledgeContent_value__lxt\" )
FROM DANIELSON_FORM
WHERE \"_1a_DemoKnowledgeContent_value__lxt\" = '1' and Observer__lxt = "OSTE"
" ; "" ; "" ; DANIELSON_FORM::_1a_DemoKnowledgeContent_value__lxt

)

 

What do I need to do to get this calc to work, or am I going at this all wrong and there's a better way of doing things? (I tend to find the most complicated, convoluted way of doing things--a skill I wish I didn't have.)

 

TIA for your help!

Rich

Edited December 7, 2015 by WF7A

[Wim Decorte]

What error did you get?  A question mark?  That would indicate a syntax error.

 

You are passing in a parameter at the end of the ExecuteSQL() call but you have no placeholder for it in your query...

[GisMo]

triple check all of your spelling of the those fields and maybe try this:

 

Quote

 

ExecuteSQL (

"SELECT
COUNT ( \"_1a_DemoKnowledgeContent_value__lxt\" )
FROM DANIELSON_FORM
WHERE \"_1a_DemoKnowledgeContent_value__lxt\" = ? AND \"Observer__lxt\" = ?";

"" ; "" ;1;"OSTE"

)

 

 

[Rich S]

Thanks guys.

I just tried GisMo's calc and am now seeing a zero instead of a question mark, so that's a good sign...though we're still not there, yet; I coded four, separate calcs (of GisMo's above), one for looking at 1s, one for 2s, etc. I also had the field name wrong (*blush*) for Observer__lxt--it should be EvaluatorPosition__lxt. I tried both ways of entering that field name, escaped and not escaped. (I know an escape is necessary when  fieldnames begin with an underscore character.)

I'm looking at one student's found records (eleven of them) and she has the following for 1a:

No 1s; four 2s; seven 3s; no N/A's. ...and I'm seeing zeroes as the result of the calculation in all four.

Here's how it stands for 3's:

 

ExecuteSQL (

"SELECT
COUNT ( \"_1a_DemoKnowledgeContent_value__lxt\" )
FROM DANIELSON_FORM
WHERE \"_1a_DemoKnowledgeContent_value__lxt\" = ? AND \"EvaluatorPosition__lxt\" = ?" ;
"" ; 3 ; "OSTE")

 

Edited December 7, 2015 by WF7A

[webko]

I'm not that flash at Execute SQL, but I think that is missing one parameter, either the row separator or the field separator:

ExecuteSQL(sqlQuery; fieldSeparator; rowSeparator {;arguments...})

 

How does this go?:

ExecuteSQL (
"SELECT
COUNT ( \"_1a_DemoKnowledgeContent_value__lxt\" )
FROM DANIELSON_FORM
WHERE \"_1a_DemoKnowledgeContent_value__lxt\" = ? AND \"EvaluatorPosition__lxt\" = ?" ;
"" ; "" ; 3 ; "OSTE")

 

[Rich S]

Greets!

Rats, that didn't work--I'm getting a result, but it doesn't correspond to a count for either the found set or an individual.

[GisMo]

Try each part of the query one by one for example try just searching that value field or try removing the count and see your results:

Quote

Let(

[

sql ="SELECT COUNT ( \"_1a_DemoKnowledgeContent_value__lxt\" )
         FROM DANIELSON_FORM
         WHERE \"_1a_DemoKnowledgeContent_value__lxt\" = ?"

]

executesql(sql;"";"";3)

)

 

[webko]

Execute SQL is no respecter of the Found Set - that query will Count _all_ records in the target table where _1a_DemoKnowledgeContent_value__lxt=3 and EvaluatorPosition__lxt=OSTE

[Rich S]

Ahhhh, okay. So if I take it, then, that even if I inserted a DISTINCT argument (for Student ID) that isn't going to help.

I'm not a drinking man, but I may start with this project. : P

Edited December 7, 2015 by WF7A

[GisMo]

21 minutes ago, WF7A said:

Ahhhh, okay. So if I take it, then, that even if I inserted a DISTINCT argument (for Student ID) that isn't going to help.

I'm not a drinking man, but I may start with this project. : P

No. You just need to add Student ID to the query. execute sql has nothing to do with the filemaker found set..

for example:

Let(
[
studentID = mytable::StudentID;
sql = "SELECT
COUNT ( \"_1a_DemoKnowledgeContent_value__lxt\" )
FROM DANIELSON_FORM
WHERE \"_1a_DemoKnowledgeContent_value__lxt\" = ? AND \"EvaluatorPosition__lxt\" = ? AND StudentID=?" 
];

ExecuteSQL (sql;"" ; "" ; 3 ; "OSTE";studentID) 
)

 

Edited December 7, 2015 by GisMo
missing paren. in let

[Rich S]

Hi, again:  I just tried your calc (an error message popped up saying that there one too many/few left/right parenthesis) so I inserted a right parenthesis at the end, but I'm still getting the dreaded question mark as a result.

[GisMo]

I updated my post with the correct parenthesis.

Are you running this in a script? The studentID must pull from the correct table/context for it to work.

What does your query look like? Come into the forum chat. I'm idling there.

Edited December 7, 2015 by GisMo

[Rich S]

To everybody's that's following, here's what worked...thanks to GisMo:

Let(
[
sql = "SELECT
COUNT ( \"_1a_DemoKnowledgeContent_value__lxt\" )
FROM DANIELSON_FORM
WHERE \"_1a_DemoKnowledgeContent_value__lxt\" = ? AND \"EvaluatorPosition__lxt\" = ? AND StudentID=?"
];

ExecuteSQL (sql;"" ; "" ; 3 ; "OSTE";DANIELSON_FORM::studentID)
)

 

I changed the student ID field from its original name to studentID. Apparently, there was a problem with the fieldname.

BIG thanks to GisMo!

[Wim Decorte]

the problem with the "student ID" name is that you did not quote it like you did with the other fields....