Counting Records

[comprt]

Hi,

I would like to know how I can develop a new table containing total number of records from some of my other queries.

I have a few different queries, and I would like to tabulate simply the total number of records in each of these queries into a table.

Additionally, I would like to know how I could do so dynamically--that is, enter in different criteria into those original queries and get results.

Thanks so much for your help!

Azal

 

[JeremyNYC]

You should make a query for all this information, not a table. To do so, you'll have to make one query for each object whose records you want to count and one query to assemble all of those. The first set of queries will have to return only one row in only one field. Each one would look something like this:
SELECT Count(AccountID) AS RecordsInTblAccount
FROM tblAccount;

Make sure you use the primary key field in each case, as this will be the fastest field on which to count.

The top level query, which pulls all the count queries together, is easy to build. Just add each of those first-level queries to the query and add that one field from each query. Don't build relationships between the queries. It may not be fast, but it should do what you need it to do.

Hope this helps.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[comprt]

Thanks Jeremy. This helps quite a bit. However, I'm wondering if there is a way to do this without necessarily creating a new query object for each one of these totals (I have about 120). I would ideally like to assemble them into the same object, if possible.

Do let me know, and thanks very much for your help.

Azal.

 

[JeremyNYC]

Well, 120 is not what I'd call a few. That information up front would help.

You could certainly write code to gather this information. I'd suggest using a naming convention to offset the objects you want to do the totals on and making sure the primary key is named consistently in each one (like tblAccount, with PK AccountID).

Then you'll be able to code a loop that finds all these objects, uses a recordset to get a count of how many records are in each one, and adds a couple of fields of data (tableName and RecordCount) to a table with just those two fields.

Shouldn't be that bad, as long as you approach it systematically, just doing one part at a time.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[comprt]

I'll try that if this doesn't work out, but actually I've tried the UNION ALL statement to bridge a bunch of SQL SELECT statements together.

The problem is how would I uniquely access them from a record (normally I would use a variable name)? If there isn't an easy way, then I'll try writing some VBA code.

Thanks for your help.

 

[Trainingjason]

create a new form add a command button and type the following

currentdb.tabledef("nameoftable&quot.recordcount