Query Count

[kitty77]

I have a query that has one field (number1) and I would like to retrieve the records that are less than 5. The table has about 5000 records.
So, if say 500 records meet that criteria, I want just the count or how many records.

Thanks.

 

[plog]

What have you tried? You can use one query as the basis of another query, start there.

 

[kitty77]

Not sure where to start.

 

[theDBguy]

Have you tried DCount()?

 

[kitty77]

That's what I was thinking. What would it look like looking for records <5 using DCount()?

 

[theDBguy]

That's what I was thinking. What would it look like looking for records <5 using DCount()?

Maybe something like:

DCount("*", "QueryName", "ColumName<5")

 

[kitty77]

Will give it a try

 

[kitty77]

So, this seems to work: test: DCount("*","Query2","Field1<5") but it gives number of records that meet the <5 (893) but for 5000 records if repeats 893?
How can I just see the count?

 

[theDBguy]

So, this seems to work: test: DCount("*","Query2","Field1<5") but it gives number of records that meet the <5 (893) but for 5000 records if repeats 893?
How can I just see the count?

So, the DCount() gave 893, and that's not the answer you wanted? What answer did you want instead?

 

[MajP]

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

SELECT COUNT(*) as TotalCountLessThan5
FROM someTable
where number1 < 5

 

[kitty77]

So, the DCount() gave 893, and that's not the answer you wanted? What answer did you want instead?

yes, 893 is the correct count but it repeats with 893 for 5000 records? How can I just get the 893 returned, one record.

 

[theDBguy]

yes, 893 is the correct count but it repeats with 893 for 5000 records? How can I just get the 893 returned, one record.

Can you post the SQL statement you're using?

 

[kitty77]

Can you post the SQL statement you're using?

DCount("*","Query2","Field1<5")

 

[theDBguy]

DCount("*","Query2","Field1<5")

But where are you using that? If in another query, then maybe what you want is what @MajP posted. Otherwise, try using DCount() in a form or a report or in VBA.

 

[kitty77]

But where are you using that? If in another query, then maybe what you want is what @MajP posted. Otherwise, try using DCount() in a form or a report or in VBA.

Yes, in another query. Will try.

 

[Pat Hartman]

Do NOT use domain functions in queries. There is almost always a better, more efficient, option. If what you are looking for is a query that counts, use the QBE to build it.

1. Select the table that contains the data.
2. Select the ID field
2. Select the field for the criteria.
3. Change the query type to TOTALS by selecting the sigma from the ribbon.
4. Add the criteria to the criteria field and change the type from "Group By" to "Where"
5. Change the type for the ID field from "Group By" to Count
6. Save and run

 

[The_Doc_Man]

Pat got here first to tell you WHAT to do, so instead, I'll explain what happened.

To get the result you described, you had to have created a query that included a DCount() in it. That DCount() was run once for every record in your table, so you got 5000 copies of 583. MajP showed you a query that would give you the answer as a single field in a single record. It didn't use DCount() - which is a (VBA) Domain Aggregate function - but rather it used Count() - which is an SQL Aggregate function. The difference is where and how they are executed.

You could have used the DCount to define the value of a textbox {.ControlSource = DCount(...)} or you could have used it in a line of VBA code {X = DCount(...)}. That is because the DCount is executed in the context of Access itself. When you put it in an SQL query it was the "wrong flavor" of aggregate. It had to "consult" with Access for every record in the table because it was a VBA-compatible function, not an SQL function

What MajP showed you was NOT compatible with VBA but it WAS known to SQL. SQL, running in the ACE engine, didn't have to "consult" with the Access environment so didn't have to run once for every record. Should have run a LOT faster, too. About 5000 times faster.

I'll offer the suggestion that you should read up on Aggregate functions and understand that there are TWO KINDS - Domain Aggregates for the VBA environment and SQL Aggregates for the SQL environment. It matters because Access has two environments... SQL and VBA... and they don't actually overlap.

 

[kitty77]

I think I got it working the way I want now. Thanks to all!.
How would I write the following? Test: DCount("*","Query2","Field1>0.5 and <=1")

I'm trying to return a records that are greater than 0.5 and less than or equal to 1. I used the above but get an error?

 

[MajP]

In vba and SQL you cannot do
x > .5 and <=1
you have to repeat the thing you are comparing
X > . 5 and X <= 1

 

[kitty77]

Got it! Thanks.