Counting Records from multiple tables

[merciless32]

I need to display the combined total record count from 3 seperate tables on a form. Do I use SUM or DCOUNT or what? Thanks in advance!

 

[Mile-O]

I'd use a bit of code as DCount can be slow - especially when you have loads of records in a table.

Dim db as Database
Dim rs1 As Recordset, rs2 As Recordset, rs3 As Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset("YourFirstTable")
Set rs2 = db.OpenRecordset("YourSecondTable")
Set rs3 = db.OpenRecordset("YourThirdTable")

txtYourTextBox = rs1.Recordcount + rs2.Recordcount + rs3.Recordcount

rs1.Close
rs2.Close
rs3.Close

 

[merciless32]

Close but not quite...

I'm not getting a display of the total in my field. What should I set the Dim to?...Variable?...Integer? Or something else?

 

[Mile-O]

Re: Close but not quite...

I'm not getting a display of the total in my field. What should I set the Dim to?...Variable?...Integer? Or something else?

Firstly, where did you put this code?

Secondly, what version of Access are you using?

 

[Pat Hartman]

Use two queries:

query1:
Select Count(*) As recCount
From tblA
Union Select Count(*) As recCount
From tblB
Union Select Count(*) As recCount
From tblC;

query2:
Select Sum(recCount) as TotRecCount
From query1;