[font=Arial]Hi, how do i use conditional statement in sql? im using access 2003 as frontend and backend.

i would like to count the number of records in a table (ex. ENROLL) where there is only 1 instance of any value in a field (ex. STUDENT_NO) plus another condition where a given value (ex. 1) should exist in another field (ex. SECTION).

example

Table name: ENROLL

STUDENT_NO -- SECTION -FEE
16 ---------------- 1 ------ 75
16 ---------------- 2 ------ 75
17 ---------------- 1 ------ 100
18 ---------------- 2 ------ 125
19 ---------------- 1 ------ 75
19 ---------------- 2 ------ 75
19 ---------------- 3 ------ 75
20 ---------------- 1 ------ 150

Parameters:

COUNT
The number of records (count) in the table where there is only one instance of the STUDENT_NO value and the corresponding SECTION value is 1

VALUE
The sum of the FEE values in the table where there is only one instance of the STUDENT_NO
value and the corresponding SECTION value is 1


The answer should come out like this:

count = 2
value = 250

What would be the correct SQL statement for this situation that will be used in MS Access?
Thanks in advance for the help.

This post has been edited by bamn68: 28 Mar, 2007 - 10:12 PM

You might want to use the HAVING sql clause.
Look in the manual how it works.

I was able to get it to work with this query:



but only when the data type for STUDENT_NO is numeric.
The data type of the field equivalent to STUDENT_NO that im working on is text and that's where i'm getting an error message.

I know this is very easy for you guys. I'm just a newbie and i'd like to get this program to work so i can pull the right report.

Any ideas how i could revise the query above if the data type of STUDENT_NO is text?

Appreciate all the help.

Would be, if it was SQL. But Access is not, it has very strange ways to do things, so i am afraid i can not help you further.
Again, i would retry going from the original and using a HAVING clause.

I finally got the answer. I changed the DCount() into a Select Count subquery.
For the benefit of those who would like to know it, here's the statement:



It should work no matter whether [STUDENT_NO] is text or numeric.

Cheers!