JSP result set from MS Access - how to get the location name?

Question

I have a table called "form" in ms access which have following fields: formno, location, status.

I want to create a report which calculates:

1. the total no of forms(columns) at each location
2. the total no of forms(columns) with status= "pending" at each location.

I tried to do it with this query:

select count(formno) as totalforms
from form
group by location;

select count(formno) as pendingforms
from form
group by location
WHERE status = 'pending';

Answer 1

SELECT Location, COUNT(FormNo) AS TotalForms, 
SUM(IIF(status = "pending", 1, 0)) AS Pending
FROM FORM GROUP BY Location

Does this help?

Answer 2

The second SQL Statment has the Where Clause in the wrong place. It should look like one of these:

SELECT Count(form.formno) AS CountOfformno, form.location
FROM form
GROUP BY form.location, form.status
HAVING (((form.status)='pending'));

SELECT Count(form.formno) AS CountOfformno, form.location
FROM form
WHERE (((form.status)='pending'))
GROUP BY form.location;

Basically if you wish to group by a column and set criteria on it use the Having Clause. The Where Clause comes before the group by clause.

To learn the SQL syntax you may find it easier to use the Query By Design Grid (in Design View) first then switch to SQL View after you get the results you are after in Datasheet View.

Answer 3

Form is a reserved word and must be enclosed in square brackets: http://support.microsoft.com/kb/286335. I would suggest renaming the table, because it will continue to cause problems.

select location,count(formno) as totalforms
from [form]
group by location

select location, count(formno) as pendingforms
from [form]
WHERE status = 'pending'
group by location

Note that count(*) can also be used if you wish to include all records in the table: http://stackoverflow.com/questions/433913/in-sql-is-there-a-difference-between-count-and-countfieldname/433920#433920