Need a Record Count Summary (1 Viewer)

JMM9000 · Jul 30, 2014

I know that this issue will not be SOLVED with a query, but I am basically looking for a way to display the total number of records for a selected number of tables and queries in my database on one screen or on one single page view. The problem with using reports or forms is that they tend to bind you to a single table as a record source. This summary would require multiple tables and queries. The summary data would look something like this with a total coming from a Count() or count type function:

# of records: 3000
# of records to insert: 500
# of records to delete: 400
# of records to update: 2100
# of records changing location: 100

and so on. One from each table or query. If someone were to have a plausible suggestion or solution, that would make it look great!!

vbaInet · Jul 30, 2014

Welcome to the forum!

JMM9000 said:
# of records to insert: 500
# of records to delete: 400
# of records to update: 2100
# of records changing location: 100

How do you determine the above from a table?

plog · Jul 31, 2014

This will get you the total number of records in all tables and queries:

Code:

SELECT MSysObjects.Type, MSysObjects.Name, DCount("*",[Name]) AS Records
FROM MSysObjects
WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=5) AND ((MSysObjects.Name) Not Like "MSys*"));

You'll have to explain how to determine all the records to Insert/delete/update/etc.

JMM9000 · Aug 6, 2014

the results are basically just the total number of records in each query or table. It's really as simple as that. I can get those numbers other ways, just not all at once.

vbaInet · Aug 6, 2014

It still doesn't make much sense to me. How does Insert/delete/update/etc relate to a table?

AC5FF · Aug 6, 2014

vbaInet:
I tried this code on one of my databases. Quite neat. Although it took forever to run!
(Monster database)
I did get numerous results that gave "Error" ... not sure why. But again; it's a monster that is linked all over the place to XLS, TXT, and other MDB files on numerous different computers here.

JMM9000; I think i understand what you are looking for; but the number of records in a query will very when the query is run. So, looking for a count of records a query will update/delete/etc - i don't think that's possible....

vbaInet · Aug 6, 2014

AC5FF said:
vbaInet:
I tried this code on one of my databases. Quite neat. Although it took forever to run!
(Monster database)
I did get numerous results that gave "Error" ... not sure why. But again; it's a monster that is linked all over the place to XLS, TXT, and other MDB files on numerous different computers here.

I think you mean plog!

But if it's running slow then you can change the DCount() to a subquery.

AC5FF · Aug 7, 2014

OOPs! Yep. Sorry Plog!

Need a Record Count Summary (1 Viewer)

JMM9000

New member

vbaInet

AWF VIP

plog

Banishment Pending

JMM9000

New member

vbaInet

AWF VIP

AC5FF

Registered User.

vbaInet

AWF VIP

AC5FF

Registered User.

Similar threads

Users who are viewing this thread