Aggregate Count listing tables and count of all their records

[whilburn]

Based on the inserted image. I have a listing of tables and a count of their records across the rows. I am trying to figure out an approach that would provide the exact information collected in spreadsheet format using the table name and aggregate counts across rows. Any help for a construct is appreciated



Thanks
Wayne Hilburn

[RuralGuy]

I assume that data is from a query. You can export a query to Excel.

[rpeare]

I don't understand your question. Is this a spreadsheet you're currently maintaining aside from your database?

if you're looking to cycle through all your tables in your database, count the records and either write that to an excel file or to be able to spit it out on a report out of access you can do that.

You just need to be familiar with TableDef.

dim TblDef

for each TblDef in currentdb.tabledefs
     If not (tblDef.name like "Msys*" or tblDef.name like "~*") then
          debug.print "Table " & TblDef.name & " has " &  dcount("*", TblDef.name) & " records" 
     endif
next TblDef

this is the basic method, you can decide how you want to spit out the output, this version spits it out to your immediate window so you can verify the results.

[whilburn]

To Rural Guy and rpeare,
Thanks and i can see by re-reading my post where you cant imagine what i was trying to say
Here is the whole picture..
I utilize a system that i am trying to replicate within Access. The only way for me to know that my updates are working to keep my Access DB up to date is by getting aggregate counts of all the tables and their records. The Image of the aggregate counts you see in my original post are an excel export from my original system.
I would like to run a query or report that provides the information in the exact format.
From the code rpeare provided, i see that it will provide the information via vba. I will pursue this as a possible solution.
I was dabbling with doing a count for groupings within tables or groupings across multiple tables and was looking for a solution through the design query for a grouping and then creating a union. But couldnt figure out how to list the groups and counts across rows and was thinking of just asking a high level question regarding a construct for a query solution.

For example,
from the original image, the (Bizterm, BiztermGroup, BiztermRelationship) objects give me a plethora of groupings and necessary counts that I need to derive. I realize that an easy high level approach using vba might be suitable, but the groupings would be repetitive in form and might be easier to achieve in a query. I may be wrong, and thats why i am here asking because i am only making assumptions.

Hopefully this provides a little more information.

Thanks
Wayne

[aytee111]

What do you mean by groupings? The first post talks about tables and their record counts, which has been answered. Any additional counts that are needed would need to be explained.

[rpeare]

if there's a specific field or set of fields in your table that has the 'grouping' you want you can create aggregate queries

i.e.

SELECT [GROUP FIELD NAME], count([PRIMARY KEY FIELD]) as RecordCount FROM [TABLE NAME] GROUP BY [GROUP FIELD NAME]

[whilburn]

Thanks for the replies
I resolved my issue in the following manner.
1. created a single query with a group by count for each different type of object that ultimately will show up on my spreadsheet.
2. created a sing query where I added the count fields of all the non-related queries into this single query.
3. created report where the main report referenced the query created in step 2.
4. selected add fields button on report
5. added all the individual count fields into the report and it gave me my aggregate report.