?
Solved

Count of records across multiple tables

Posted on 2007-08-06
11
Medium Priority
?
605 Views
Last Modified: 2013-11-05
Hi all,

I am using Access2003 SP2 on WInXP SP2.  I have multiple tables all of the same structure.  I an using a list view control (not list box) to display the name of each of the tables in the first column.  I an using an SQL statement to fill the List View Control.  I would like the second column to display the count records for each of the tables.  Any pointer would be greatly appreciated.

Thanks
mavreich

Example:
List View Control
Name:            Records
Table1            20
Table2            56
Table3            12
0
Comment
Question by:Mavreich
  • 6
  • 4
11 Comments
 
LVL 75
ID: 19642504
Can you post that SQL?

You should be able to Group on Table Name and then use Count (*)

mx
0
 
LVL 4

Expert Comment

by:ki_ki
ID: 19642508
what's a list view control ?
0
 
LVL 2

Author Comment

by:Mavreich
ID: 19642515
SQL as follows....

"SELECT MSysObjects.Name " _
            & "FROM MSysObjects " _
            & "WHERE (((MSysObjects.Name) Like ""*NSCC OPEN CALLS"") And ((MSysObjects.Type) = 1)) " _
            & "ORDER BY MSysObjects.Name DESC;"
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 2

Author Comment

by:Mavreich
ID: 19642522
ki_ki:..

Microsoft ListView Control 6.0 (SP6).  In form design go to insert activex control.

Regards
Mavreich
0
 
LVL 75
ID: 19642532
btw ... ignore my first post ... I misread the Q ...

mx
0
 
LVL 2

Author Comment

by:Mavreich
ID: 19642536
lol  thats ok...

Realise this one may be a little tricky.
0
 
LVL 75
ID: 19642557
Ok ...here you go ... but add in your criteria:

SELECT MSysObjects.Name AS TableName, DCount("*",[Name]) AS RecCount
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "MSYS" & "*") AND ((MSysObjects.Type)=1));


mx
0
 
LVL 75
ID: 19642563
That is:

WHERE (((MSysObjects.Name) Like ""*NSCC OPEN CALLS"")

ORDER BY MSysObjects.Name DESC;
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 19642574
I ran this on a test MDB and I get all the table names with corresponding record counts:

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

mx
0
 
LVL 2

Author Comment

by:Mavreich
ID: 19642616
DatabaseMX:

You have my absolute gratitude.  Thank you for your speedy response and solution.  As always Experts Exchange has lived up to its reputation.

Thanking you again.

Mavreich
0
 
LVL 75
ID: 19642625
You are most welcome ... and thank you.

btw ... you might chime in on this rant and help us out:

http://www.rustyrazorblade.com/index.php/2007/07/30/experts-exchange-should-be-removed-from-google-search-results/

mx
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question