Question: Counting records in multiple tables in one querry (1 Viewer)

Top Fuel Friday · Sep 23, 2010

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?

MarkK · Sep 23, 2010

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
[COLOR="Green"]
  'create an array of table names to look in[/COLOR]
  vTables = split("Audit1 Audit2 OtherAudits JimsAudits Audits_R_Us")
[COLOR="Green"]  'run a DCount() for each table[/COLOR]
  for each var in vTables
    sum = sum + dcount("*", var, "AuditDate = #" & AuditDate & "#")
  next

  CountAuditsByDate = sum
End Function

Question: Counting records in multiple tables in one querry (1 Viewer)

Top Fuel Friday

New member

MarkK

bit cruncher

Similar threads

Users who are viewing this thread