Go Back   Access World Forums > Microsoft Access Discussion > Queries

Thread Tools Rate Thread Display Modes
Old 09-23-2010, 09:52 AM   #1
Top Fuel Friday
Registered User
Join Date: Sep 2010
Location: Louisville, KY USA
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Top Fuel Friday is on a distinguished road
Question: Counting records in multiple tables in one querry

I'm creating an Access 07' database for tracking quality audits my department. There are a total of 11 audits we do, each with it's own table of records within the database.

I want to create a query that will count the total number of records created for a given date. This date is stored in an Audit Date field that is in every table.

What's the easiest way to do this?

Top Fuel Friday is offline   Reply With Quote
Old 09-23-2010, 10:42 AM   #2
Super Moderator
MarkK's Avatar
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,285 Times in 1,224 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
The eaisest way is to get someone else to do it ...
Function CountAuditsByDate(AuditDate as date) as integer
  dim vTables
  dim var
  dim sum as integer

  'create an array of table names to look in
  vTables = split("Audit1 Audit2 OtherAudits JimsAudits Audits_R_Us")
  'run a DCount() for each table
  for each var in vTables
    sum = sum + dcount("*", var, "AuditDate = #" & AuditDate & "#")

  CountAuditsByDate = sum
End Function
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Select the most recent entry from multiple tables chrisuff Queries 1 06-15-2009 08:45 AM
Listing all records for same field in multiple tables rjbudden Queries 6 10-27-2006 03:52 AM
Merging multiple tables question hammerva General 1 05-12-2004 09:40 AM
Counting records in a subform and displaying it moshi Forms 15 10-15-2003 03:55 AM
Counting Records from multiple tables merciless32 General 4 02-27-2003 11:26 AM

All times are GMT -8. The time now is 10:10 AM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit

Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World