SQL

[Jason]

I am familiar with PHP/MySQL, however have been assigned
to complete a task at work in ASP using an Access
database. I am having troubles with a standard SQL
statement. It simply is not working with Access. If any
one knows how I can accomplish the same result using a
different syntax I would appreciate it.

Here is my sql statement:

SELECT issue_type, count(*) FROM main WHERE id = "44432"
GROUP BY issue_type;

Here is my output from MySQL:
+-----------+----------+
| issuetype | count(*) |
+-----------+----------+
| Billing | 2 |
| System | 3 |
+-----------+----------+

Using access I get a box asking for "issue_type" field,
and get the following output.

+------------+----------+
| issue_type | count(*) |
+------------+----------+
| XXXXXX | 113 |
+------------+----------+

Where XXXXX is I get what ever I type in the box. However
the count is always a count of total records in the
table.

The ASP page produces the following:
Error Type:
Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters.

 

[Chris]

That error usually means that one of the fields is not named correctly.
Check to make sure you are spelling things right.

Also, is id a text or numeric field. If it is numeric, then there is no
need for the quotes.

Lastly, when you use a aggregate function (Count, Sum, etc), you have to
alias the field.

Select Issue_Type, Count(*) as Total FROM Main Where ID = 44432 Group By
Issue_Type
OR
Select Issue_Type, Count(*) as Total FROM Main Where ID = "44432" Group By
Issue_Type


If that doesn't help, then I'll need more info.

 

[Jared Kale]

I had the same problem early on.

Access wants you to have the table name in front of any
type of field name that you are trying to use. So, the
statement should be

SELECT main.issue_type,count(main.*) FROM main WHERE
main.id = "44432" GROUP BY main.issue_type

I'm not totally sure on the GROUP BY statement since I
usually shy away from them, but, given the access syntax
that access usually likes, that should be what it's
looking for.

Hope this helps ^_^

 

[Tim Ferguson]

Access wants you to have the table name in front of any
type of field name that you are trying to use. So, the
statement should be

No: the query design grid puts these all over the place, but that's only
because it's stupid. As long as the field names are not ambiguous, they are
not needed.

Tim F