Counting Tables

[cchaalan]

Hi,
I am trying to get a count for the # of records in 5 queries by creating the count in one query, but the only way I can do that is if I create 5 additional queries for each count. Is there any way to use multiple count statements in SQL.
Example:
Query Q_Duplicates:
SELECT COUNT(*) AS CNTDup
FROM Q_Duplicates;

Query Table T_FTOR:
SELECT COUNT(*) AS CNTFTOR
FROM T_FTOR;

....& there's an additional 4 other query's that do the same Count...There must be a way to create one query that counts the # of records for each table!
PLeaseeeee Help...

 

[stopher]

Is this any good...

SELECT "CNTDup" AS TableName, Count(*) AS RecordCount
FROM Q_Duplicates
UNION
SELECT "CNTFTOR" AS TableName, Count(*) AS RecordCount
FROM T_FTOR

UNION
SELECT etc...

Stopher

 

[Moniker]

Open a new query and add a table -- any table -- to it. (A query needs at least one table in it to work, but for this case, it doesn't matter which table it is.)

Turn on the Group By function (more on this in a second), and switch ovber to SQL view and then type this:

SELECT
    DCount("*","Your_1st_Table")
    +DCount("*","Your_2nd_Table")
    +DCount("*","Your_3rd_Table")
    +DCount("*","Your_4th_Table")
    +DCount("*","Your_5th_Table") 
    AS Totals
FROM
    Name_Of_Whatever_Table_You_Added_To_The_Query
GROUP BY
    DCount("*","Your_1st_Table")
    +DCount("*","Your_2nd_Table")
    +DCount("*","Your_3rd_Table")
    +DCount("*","Your_4th_Table")
    +DCount("*","Your_5th_Table") 
;

Run this and the number of records in Your_1st_Table, Your_2nd_Table, etc. will be added together and returned. You need the GROUP BY on here because otherwise you are returning a large cartesian product, which you don't want. You're actually still returned that, but since GROUP BY is on, only one record returns.

Note that I've shown you a sort of shady way to do this. It's cleaner in code using just the DCount statements (Totals = DCount1+DCount2...). The above query will do it though.

 

[Moniker]

Strangely (and unbeknownst to me), a co-worker had used the UNION SELECT method mentioned above. So, we did a little contrast and compare, and here are the results.

The method I used (DCount) was about twice as fast, but that's not as impressive as it sounds. Mine ran in just under five seconds, where the UNION SELECT took just over eleven seconds.

However, the UNION SELECT offered a nice advantage. You could easily throw in the table name so you could see the total records for each table. The DCount method won't do that without a few teporary tables and such, which makes it unnecessarily complex.

Two other considerations:
- While the UNION SELECT will show each table, it will not show tables/queries that return zero records. There may a way around this (we tried Nz on the Count()), but we didn't see it. Then again, we spent maybe five minutes on the whole thing.

- To get the total of all the records in all the tables, a separate query will need to be run against the UNION SELECT query, where as the DCount() method returns the entire count.

In other words, it depends on how much detail you need. If you don't care how many records are in each table, then the DCount is faster and supplies the total record count with the need for a second query. If you want to see the totals for each recordset displayed at once, use the UNION SELECT method.

 

[stopher]

Moniker
When I read you first post I wondered about speed. Well done for testing. Interesting results.

I couldn't replicate your problem with zero counts. The union query I used shows tables with zero count (see attached). I did it in Access 97 in case there was an issue with versions.

Stopher

 

[cchaalan]

Hi,
Thank you for the quick answers. Both suggestions worked, but using the DCOUNT gives me a total sum of all the tables, not each individual one, so I can't use it. The UNION I can use, b/c it gives me an individual record count for each table. But it doesn't treat the sums as individual fields for each table, so how do I use this data in a report to do calculations? See attached pic of result. The data is now stored in record format, I am not able to create a report that says take the record count of table1 - the record count of table2. Or am I wrong? Please let me know, your help is appreciated.
Regards,
Cchaalan

 

[Dennisk]

If you need individual counts then you need individual queries. However you can then use all the total queries in one query with a calculated field to sum them all up.

 

[stopher]

You can perform a crosstab on your current query to create the fields.

Here's the crosstab for my previous example:

TRANSFORM Sum(RecordCount) AS SumOfRecordCount
SELECT "myCount" AS myCount
FROM Query1
GROUP BY "myCount"
PIVOT TableName;

Equally you can play around with Monikers solution to show separate fields using DCOUNT. In my previous example it would look like this:

SELECT "table Count" AS [Info], DCount("*","Q_Duplicates") AS Dup, DCount("*","T_FTOR") AS FTOR, DCount("*","Table3") AS Table3;

The variant on Moniker's solution looks like a much cleaner option I think i.e. one query rather than two and most likely faster. Note you don't need to have a dummy table involved.

Stopher

 

[Moniker]

It turns out my co-worker was trying to count a specific field, not a wildcard (*) field, and hence, he was getting bad results. The suggestion stopher posted was correct.

Of course, none of this has to be a query at all. It's faster in code (although it won't count tables that are linked).

Private Sub cmdRecCount_Click()

    Dim TableName As TableDef
    Dim TableCount As Integer
    Dim RecordCount As Long
    Dim colTableNames As New Collection
    Dim colRecCounts As New Collection
    Dim ctr As Integer
        
    ctr = 1
    TableCount = 1
    For Each TableName In CurrentDb.TableDefs
        RecordCount = RecordCount + TableName.Attributes
        colTableNames.Add Item:=TableName.Name
        colRecCounts.Add Item:=TableName.RecordCount
        Debug.Print colTableNames(ctr) & " - "; colRecCounts(ctr)
        ctr = ctr + 1
        TableCount = TableCount + 1
    Next
    
    Debug.Print "Total Tables: " & TableCount & " | Total Records: " & RecordCount

End Sub

I'm just writing the results to the debug window, but it could easily be modified to write to a query. Note that I'm also storing the results in a collection, so that they can be accessed at any point, not just within a query.