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.

No. You cannot mix columns and group functions without a GROUP BY clause. You have to select your rows in a query, and perform the count in another query. — Sergio Internicola
– Sergio Internicola, Commented Oct 12, 2015 at 13:04

Andre · Accepted Answer · 2015-10-12 14:03:35Z

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 ] 
)

answered Oct 12, 2015 at 14:03

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

asdev · Accepted Answer · 2015-10-12 14:03:21Z

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.

answered Oct 12, 2015 at 14:03

asdev

9436 silver badges9 bronze badges

4 Comments

LiamH Over a year ago

The distinct removes duplicates. I created a query in the query designer and I was getting duplicate results (not sure why) so I included the distinct to prevent issues.

asdev Over a year ago

Okay then use @andre451 's answer. But I recommend you anyway to check your database-design, because having duplicate recordsets in one table is a little bit strange except you wanted that explicitly.

Andre Over a year ago

asdev has a very valid point here. If SELECT DISTINCT * FROM Inventory gives a different number of records than SELECT * FROM Inventory, then your table has no primary key which is bad. --- SELECT DISTINCT is typically used when selecting only a single or a subset of fields from a table. @LiamH

LiamH Over a year ago

I may not have been very clear as the syntax is different. my fault. however, I have asked this as a separate question here

Collectives™ on Stack Overflow

Select and count Query in VBA (Access 2010)

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related