Select and count Query in VBA (Access 2010)

Question

I have a select query as part of an on_click event within a form.

varSQL2 = "SELECT DISTINCT(*), count(*) AS Count1" & _
          " FROM Inventory WHERE Part_ID='" & rs!Part_ID & "';"

I am wondering what the correct syntax for this query would be. I would like all records that match the ID and then a count of how many there are. Is this syntax correct?

It may be found that this is the correct syntax, however, I am neither getting an error nor response.

Can anyone suggest a better alternative. I have seen cases where a subquery has been used, but I don't really understand the difference between that and what I have used.

Answer 1

For getting a count on how many recordsets have a specific Part_ID use:

varSQL2 = "SELECT count(*) AS Count1" & _
          " FROM Inventory WHERE Part_ID='" & rs!Part_ID & "';"

Now to your used DISTINCT(*) : I do not know what the purpose was, because of missing explanation, but it would compress exactly equal recordsets. If you follow the idea of a relational database design (which Access is thought for), you will never have two exact table-entries in one table.

So, if you wanted to achieve anything with this distinct, please explain it in your question, that we can help you.

Answer 2

As Sergio wrote, you need a subquery like this:

SELECT Count(*) AS Count1
FROM (
    SELECT DISTINCT * 
    FROM Inventory 
    WHERE Part_ID = [ rs!Part_ID ] 
)