find distinct record counts of all user tables

[vvwstcat]

I have learned from the web to find the record count of all user tables in ms access, which is something like below:




1. SELECT [Name], DCount("*","[" & [Name] & "]") AS RecCount FROM MSysObjects WHERE Type=1 AND [Name] Not Like "Msys*" ORDER BY 1




In MS Access, we can find the distinct count of a particular item of a table using the following


2. select count(employee_id) as num_of_employees
from
(
select distinct employee_id from Employees
)




Is it possible to combine the above 2 to find the distinct record count of all user tables in ms access? Kindly advise. Thanks a lot in advance.

[Minty]

The first query returns a count of all records from all the tables.

Is that not what you are after??

[vvwstcat]

The first query returns a count of all records from all the tables.

Is that not what you are after??

No. it is not what I'm after. I hope to combine the 2 queries to make a query that can find distinct count for all (user defined) tables. Thanks.

[Minty]

Sorry, but that's what the 1st query does. It lists each table and it's record count?
Do you want a listing of the fields instead of the records?

Or do you mean a listing of the distinct records of each field for each table?

So if a table Table1 had FieldA, FieldB and FieldC you would want

TableName	Fieldname	DistinctRecords
Table1	FieldA	29
Table1	FieldB	12
Table1	FieldC	45

[vvwstcat]

Hi Minty,

the 1st query gives the record counts of the all tables.
the 2nd query gives a distinct count, says, employee-id, of a table (one table only!)
Please be noted that the 1st query uses the [name] dynamically for table-name(s). In such a way, the user does not need to specify the table name. It just goes thru the entire database for all user defined tables.
By examining the 1st query, which uses DCount("*","[" & [Name] & "]") . I wonder whether It is possible to use the 2nd query as the 2nd argument "[" & [Name] & "]". If so, it will list all distinct count of all tables.
As I do not know how to construct the kind of dynamic code like "[" & [Name] & "]" for the distinct query, that's why I am asking for help.
Thank you.

[Minty]

Perhaps you can give an example of the output you are expecting, as your explanation is not making sense to me.
What are you expecting to see with "list all distinct count of all tables."

A single total row count for all tables ?

[Micron]

If your table names were prefaced by object type (e.g. tblUsers) then it ought to be simple enough by looking for "tbl" with Left function? The only problem I can see with what you posted is that sometimes objects that are of type 1 are not tables. If you don't have the object prefix, then perhaps add Flag field where Flag >=0, because the example I looked at, those objects had negative values. However, I think I would count using * wildcard rather than one particular field. So

SELECT MSysObjects.[Name], DCount("*","[" & [Name] & "]") AS RecCount
FROM MSysObjects
WHERE (((MSysObjects.[Name]) Not Like "Msys*") AND ((MSysObjects.[Type])=1) AND ((MSysObjects.Flags)>=0))
ORDER BY 1;

Sorry if I'm not understanding the problem.

[vvwstcat]

If your table names were prefaced by object type (e.g. tblUsers) then it ought to be simple enough by looking for "tbl" with Left function? The only problem I can see with what you posted is that sometimes objects that are of type 1 are not tables. If you don't have the object prefix, then perhaps add Flag field where Flag >=0, because the example I looked at, those objects had negative values. However, I think I would count using * wildcard rather than one particular field. So

SELECT MSysObjects.[Name], DCount("*","[" & [Name] & "]") AS RecCount
FROM MSysObjects
WHERE (((MSysObjects.[Name]) Not Like "Msys*") AND ((MSysObjects.[Type])=1) AND ((MSysObjects.Flags)>=0))
ORDER BY 1;

Sorry if I'm not understanding the problem.

Let me re-phrase the problem:


I want to list all distinct record count of all user defined table.
for example, order table can have many customer-orders, but I want the distinct customer no. (a customer can order many orders), so if I just count the record in customer-order table, then the same customer can be counted multiple times.

therefore the query below list all record count of all user defined table.
SELECT [Name], DCount("*","[" & [Name] & "]") AS RecCount FROM MSysObjects WHERE Type=1 AND [Name] Not Like "Msys*" ORDER BY 1
is not what I want.

2. select count(employee_id) as num_of_employees
from
(
select distinct employee_id from Employees
)
the 2nd query, in particular, uses the subquery to obtain the distinct employee id and the main query does the counting. However, this works for the table you specify!
Now I want to make it generic enough to list all distinct major attributes (such as customer-id for order table, employee-id for employee-table, employer-id for employer-table etc.).
My question is how to re-write the subquery to replace the "[" & [Name] & "]" in the first query. Mind you that the Name is the table name "[" & [Name] & "]". How to fit the subquery "select distinct * from [Name]" for "[" & [Name] & "]".

If this does not make sense to you, I have to give up and hand-write all distinct count query for each table in the database.
Thank you.

[Minty]

You would have to put the ID fields and table names into a separate table, then you could use a query to return the results.
Otherwise how would the query know which the ID fields were.

[orange]

vvwstcat,

Distinct "what" in each user table? Do you have a PK in each table?

In the preceding dialog, Distinct Customer in Customer Orders table -would seem to say of all my Customers, this is the list of Customers that have made 1 or more Orders.
Is that what you are wanting for each table?

I'm trying to understand your requirement.

[vvwstcat]

thanks All for the suggestions. I dont wan to be rude but they are not what I want. Please check the link below (Ecount from Allen Browne)
http://allenbrowne.com/ser-66.html
it is the answer.