Count function breaks form when no records meet the criteria

[RickyBeast]

Hello,

I am quite new to access and especially have trouble understanding and dealing with null.
I have the following code

 SELECT Count(T_STUDENTS.studentsID) AS res1yes, T_COACHING.COACH1res, T_COACHING.COACH1port, T_COACHING.COACHworkgroup
FROM T_STUDENTS, T_COACHING
WHERE (((T_STUDENTS.res_vrijstelling)=False) And ((T_STUDENTS.stud_year)="1") And ((T_STUDENTS.stud_coach)=forms!F_cboCOACHING!cboCOACHING))
GROUP BY T_COACHING.COACH1res, T_COACHING.COACH1port, T_COACHING.COACHworkgroup;

it runs fine, untill the moment the count is 0. At that point it breaks the form.
How can i tell access to deal with this? Somehow i need to replace the count result with 0 if there are no records that meet the criteria.

 

[plog]

...breaks the form.

Can you elaborate on what that means? The query you posted relies on the form, not vice versa, so I don't understand how the form could break.

Also, you query is based on 2 tables that you don't relate in any way. When you have multiple datasources in a query usually should have a JOIN clause that tells the computer how those 2 tables relate to another, it is also permissible to relate them in the WHERE clause. But your query does neither of these. How come your 2 tables aren't related?

 

[RickyBeast]

Hello,

thank you for your reply.
I will explain more elaborately.

I have a main form with 5 subforms. Each of the subforms runs a query similar to the code i posted above, but using different fields from T_STUDENTS and T_COACHING.
When one of the queries finds no matches the subform does not display when i run the main form. This is what i mean by 'breaks'.
The fields from T_STUDENTS and T_COACHING only interact in the subform to make some calculation; the values in the T_COACHING fields are somehow static, but depending on policy change they could change.

I hope this clarifies my code.

so, to avoid the subforms from not displaying in the main form, and therefor causing the calculation in the main form to break, i believe i have to be able to tell the query to give 0 as a result when no records match the criteria in the queries. I ready about nz() and such, but have no idea where to add this in the code above.

 

[plog]

Nz won't solve null records. It converts a record's Null value to a 0, not a null record to 0. It doesn't create a record where there is none.

Most likely this is going to involve a LEFT JOIN, or possibly a subquery. But I don't fully understand what your query is trying to accomplish. Perhaps you can post some sample data from all relevant tables along with what you expect the query to produce based on that sample data. Be sure to include a case that currently fails to produce a record.

 

[RickyBeast]

thank you. Here is what i have and what i want it to do;
my table T_STUDENTS contains information about a student
STUD_vrijstelling = a yes/no field
STUD_year = a list of options; 1,2,3,A or G
STUD_coach = a name that is taken from another table
table T_COACHING contains the number of ours a coach has to work with a student per year.
COACHINGres = 6 (hours)
COACHINGport = 3.5
COACHINGworkgroup = 4

what i want the query to do, is to find all students that meet the criteria NO, 1, and the name of a coach (which the query takes from a form with a dropdown list). Then count all these records and put it in the res1yes field.

This count is used for a form in which some calculations are made:
[txtRES1yes] = res1yes * COACHINGres
[txtRES1port] = res1yes * COACHINGport
[txtRES1workgroup] = (res1yes/2) * COACHINGworkgroup

this is one of the sub-queries/subforms of my mainform. The other queries/subform change values to YES, 2, same coach name.

When i run the Main form, it shows the subform that then displays
EXAMPLE
Students year 1 [12] (result from res1yes) [48]
portfolio [42]
workgroups [2] [ 8]

As long as there are records that meet all the criteria, the form actually has the subform with the information. But as soon as there are no records that meet all the criteria, the subform is not displayed in the mainform.
I once tried to solve the problem by adding a UNION that displays a fixed number of 0. But then all the results in the main form's subforms showed 0 no matter what the results of the count were.

Greets Ricky

 

[plog]

No explanation, sample data. Please post data from your tables along with table/field names and enough data to cover all cases. Then post what you expect as a result of that sample data.

Use a spreadsheet or post in the forum using this format for data:

TableNameHere
Field1Name, Field2Name, Field3Name
Dave, 12, 2/3/2015
Larry, 29, 4/1/2015
Sally, 33, 5/2/2015

 

[RickyBeast]

ok, another try:

T_STUDENTS

studentsID STUD_coach RES_vrijstelling STUD_year
1 Ricky False 2
2 Ricky False 1
3 Sally False 2
4 Sally True 2
5 Gwen False 1
6 Gwen True 1
7 Ricky False 3

second table T_COACHING
COACHres COACHport COACHwork
4 3.5 4

When i run the first query which looks for STUD_coach (parameter value), False, 1 and select Ricky as parameter value I would get

Res1yes COACHres COACHport COACHwork
1 4 3.5 4

When i run the same query for Sally, i get
Res1yes COACHres COACHport COACHwork


in this case there are no values listed. What i need is
Res1yes COACHres COACHport COACHwork
0 4 3.5 4


is this more clear?

 

[plog]

Makes sense. What you need to do is use a sub-query to get your totals and apply your criteria, then you create another query using it and T_COACHING.

Here's the SQL for your sub-query:

SELECT COUNT(studentsID) AS res1yes
FROM T_STUDENTS
WHERE STUD_Coach='Ricky' AND RES_vrijstelling=False AND STUD_year=1;

Save that and name it Totals_sub1. You will also have to fix the WHERE clause to look at your form.

Then you use the below SQL to get the resutls you want:

SELECT res1yes, COACHres, COACHport, COACHwork
FROM T_COACHING, Totals_sub1

 

[RickyBeast]

Thank you very much. That did the trick. I do not understand why however.