how do i return just the number of items, not all the items? also please reply in design view language, I don't know sql yet thanks

query design

show table
itemID
Item

View--ShowTotals

ItemID Group by Count

HTH,
Cynthia

Thanks ... often times I'm hoping that this method is faster than returning all the items. i assume so, but who knows .. Do you happen to know this? Let's say I have a few other columns in the query design grid that do have criteria, but I only need to know the count of one field in a table (after it passes the criteria).

So if I Un-check all of the "show" boxes except for the item that I need to count, is that the correct way to do it? and put the Totals view to Count like you said, in tha tfield?

or is the fastest way to show nothing and have a artificial column with an expression countingi that? i'm just trying to make sure i find the way that avoids queries that take hours to run!

Sorry, but it's easiest for me to explain this using sql and what you're asking about is very easy sql, so...

A count counts the records in a table, so even if you count based on the field it doesn't really matter.
For example:

select count(*)
from table

or

select count(field1)
from table

should yield the same results - Also it's easy enough to create a test table and put in some sample data, so don't trust me just test it yourself. Note: nulls may not play as friendly, but I can't remember off the top of my head.

To limit your count by field I usually do something like this:
select count(*)
from table
where field = whatever your criteria

So, from the design view just put in your criteria.

Note: it is also possible you could so something with an iif or dcount, but both are particular to access and I prefer to stick with ANSI sql as much as possible since that should work regardless of platform.

Also, the criteria portion is where speed could become an issue..., but unless you're talking 100,000 of thousands of records you probably don't need to worry about it until it happens.

For beginning sql, check out www.w3schools.com


Edited by: Ender on Fri Jun 27 19:09:03 EDT 2008.