Fastest way to count records meeting criteria in table ?

[edmscan]

I have a table .. and I want to return a count of records that meet the criteria. (The criteria will be the number of records for a certain time period .. for example, last 24 hours, week, month and year and total records) This part is ok .. as I just change the criteria, I just want to find out the most efficient way to get that count number.

The only variable here is the RADIO number ... which will be from 0 to 16384. I just need a COUNT of the number of records .. I do not need any records returned, just a count. But the table is large and will grow more in time.
I just want the most efficient way to do so.

So here is a sample record .. and fields from Transmissions Table.

TransDate TransTime Radio Type Group

2014/08/09 12:23:23 AM 5005 TR 546
2014/08/23 11:23:24 AM 5002 TR 547
2014/09/02 07:34:23 PM 5010 TR 561

So .. my "Radio" value is entered in a form .. named txtSearch for display in txtCount for example.

So .. here are the options that I have come up with:

Method 1:

lngCount = DLookup("Count","qryTransPerYear") using select as below.

SQL from qryTransPerYear = SELECT Count(*) AS [Count] FROM Transmissions WHERE Transmissions.Type="TR" AND Year([TransDate])=Year(Date()) AND Transmissions.Radio=[Forms]![frmMainSwitchboard].[subSearchId].[Form].[txtSearch];


Me!txtCount = lngCount

Method 2:

lngCount = ELookup("Count","qryTransPerYear"."Radio = " & iRadio) 'iRadio will contain the radio value from the form.

SELECT Count(*) AS [Count] FROM Transmissions WHERE Transmissions.Type="TR" AND Year([TransDate])=Year(Date());
Me!txtCount = lngCount


Method 3: (don't know if this works .. as I typically use one of the above)

lngCount = "SELECT Count(*) AS [Count] FROM Transmissions WHERE Transmissions.Type="TR" AND Year([TransDate]) = Year(Date()) AND Transmissions.Radio=[Forms]![frmMainSwitchboard].[subSearchId].[Form].[txtSearch];"
Me!txtCount = lngCount

I am not sure all of the methods will work .. I am just throwing this out there. What is the most efficient way to get a count of the records returned ... ?

Method 4 ..

Using DCount ?

I have heard that using the built in functions can be slower .. so, I am not so much looking for a method that works, but just the fastest way to get that count number based on my criteria.


Thanks ..

[June7]

Setting a variable = to a SQL statement does not provide the count and if lngCount is declared as a number type, the code will error because it is simply trying to set the variable to a text string. Would have to open a recordset object with the sql statement then reference field of recordset to set variable.

Could do a DCount on the raw data table.

lngCount = DCount("*", "Transmissions", "Radio=" & Me.iRadio)

Use as many criteria as needed in the WHERE ARGUMENT.

[edmscan]

Ok .. thanks I will give it a try. I have a good 'test radio", with lots of records. I have about 4 counts to do.

EDIT .. I added a very precise timer, so I will do some testing as to the fastest way I can find.

[edmscan]

Question .. ?

Can I use a query .. with DCount like this.


strCriteria = "Radio=" & lngRadio
DCount ("*","qryTransThisMonth",strCriteria)

It doesn't seem to like when I specify the criteria for the radio ..

The SQL from the Query qryTransThisMonth is below ..

SELECT Count(*) AS [Count] FROM Transmissions WHERE (((Transmissions.Type)="TR") AND ((Month([TransDate]))=DatePart("m",Date())));

The TR part will remain constant in all queries .. and the "date criteria" portion will vary depending on the desired time.

Just thought that the criteria would be easier to see if I did it like this .. or do I have to remove all the criteria from the query for example and specify it in strCriteria ?

[June7]

Use table or query as source data.

Using variable for the criteria won't make any difference. If the code doesn't like the expression, it still won't like it in the variable.

Is Radio field a text or number type?

[edmscan]

Number ..

So .. do I need to alter my SQL for it to work ? I can use DLookup just fine .. but not ELookup and cannot get DCount to work with a query, but a table seems fine. I need to specify .. the
following criteria ... Type = TR, Month([TransDate]) = DatePart("m",Date()) .. and of course the radio from my form.

[June7]

The query is already doing aggregate calc for the Count so use DLookup to pull the value from query.

However, Radio is not a field in the query so it is not even available for reference as filter criteria. This brings us back to using the table as data source for the DCount. Put the 3 criteria in the DCount function. Same syntax as in query but without the WHERE word.

Or the Radio criteria must be in the query.

[edmscan]

Ok .. that makes sense. I can do it in a Group By .. and it works. I will do a bit of testing as to which way is faster.

My elapsed times range from 0.0468754 to 3.921875 depending on the radio used.

[edmscan]

I have not noticed a significant difference on the times to query the table.

If anyone has any thoughts .. please feel free to let me know.

[Iain]

I came across this thread whilst searching on counts of records based on a set criterion. Although my needs should be less complex, I’m unsure if any of the suggestions on this thread could be put to use for my needs.


I have a very large table of images, each of which is designated one of 6 conditions in a field called ImageStatus.


My thinking is that I could create a form based on a query of the table. This of course would list every single image where all I require is a count of the records of each ImageStatus.

I thought that the form might have 6 text boxes to contain the number of each image status, if one of the properties could be used to contain an SQL statement, or maybe VBA should be used to achieve this.


Either way, as an inexperienced programmer, I don’t know the syntax or code to use.


Any suggestions would be most welcome.

Thank you.

[June7]

Your question might get more attention if you started your own thread instead of hijacking an old one. Reference this thread if you think it will be helpful to readers.

I don't understand what you are trying to accomplish. Show sample data and desired output.

[Cottonshirt]

My thinking is that I could create a form based on a query of the table.

my suggestion is to start your thinking with a query. as an "inexperienced programmer" you can help yourself by keeping things simple. first, get a query to return the results you are interested in. then design a form to run the query and display the output.

I have a very large table of images, each of which is designated one of 6 conditions in a field called ImageStatus.

I assume that tbl_images.[ImageStatus] is a number from 0 to 5, giving you six status levels.

all I require is a count of the records of each ImageStatus.

SELECT tbl_images.imagestatus, Count(tbl_images.imagestatus) AS pictures
FROM tbl_images
GROUP BY tbl_images.imagestatus;

many thanks,


Cottonshirt