Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
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
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,242
Thanks: 8
Thanked 1,175 Times in 1,118 Posts
MarkK is a glorious beacon of light MarkK is a glorious beacon of light MarkK is a glorious beacon of light MarkK is a glorious beacon of light MarkK is a glorious beacon of light MarkK is a glorious beacon of light
The eaisest way is to get someone else to do it ...
Code:
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 & "#")
  next

  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
Reply

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 05:16 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


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