Counting with SQL
 

Ok here's my problem. I ran a query that joined two tables. I used a WHERE statement to sort out the data. My problem is, how do I count the row in the sorted data table?

I don't understand your question. Do you mean that you want to know how many rows you have in the result? How to go to a specific row in the result? How to number the rows as you process them?

I want to know how many rows are returned in the result. Im using Access. I know that it says how many records are in the table at the bottom of the table. What I want to do is use the count function to determine how many rows were returned.

I think you would have to use the Group By clause, something similar to this:

Ok I can count now, but my problem is since im joining three tables, im counting from the wrong table. The field i want to count is in the result of my query and in another table from the join. My count function is counting the field from the table i used in the join. How do i count the field in my query results.

Perhaps showing your query would help us help you. :)

[PHP]SELECT DISTINCT [SV-5.NameA]
FROM ([SV-5] LEFT JOIN SFtoFD ON [SV-5].NAMEA = SFtoFD.NAMEB) LEFT JOIN SFtoSV4 ON [SV-5].NAMEA = SFtoSV4.NAMEB
WHERE SFtoSV4.NAMEB IS NULL AND SFtoFD.NAMEB IS NULL;[/PHP]

This returns 9 records, which is the right amount. Now when I use

[PHP]SELECT COUNT(DISTINCT [SV-5.NameA])[/PHP]

I get an error message. What's my problem. Without the DISTINCT I get a count of 48 records which is not right.

You have to apply COUNT to the same query that provides the correct results. Does Access not have a count function that you can merely pass the result to? Something like
I don't have Access on this system, so I can't check for you. Look at your docs.

That didn't work. I know it probably an easy solution that I cant think about.

Apparently Access it's just weird :). It doesn't support count(distinct .. etc).

You could do this : http://allenbrowne.com/ser-66.html.
Or, you could Have an inner select , and count that: