Need Help for getting grand total of counting records from two tables.

[Ericka]

I used the following query to count the number of records from each table.

Select
"Employee_Table " as Table_Name,
Count(*) As Record_Total

From Employer_table

UNION ALL

Select
"Worker_Tab le" as Table_Name

Count(*) As Record_Total

From Worker_Table;

Can anyone help me to add the grand total(SUM) of all the records from both the tables.

[Tanis]

Why not have seperate queries to return the count of both seperate tables, then have a third query as your sum?

SELECT Count(Employee_ Table.Employee) AS CountOfEmployee
FROM Employee_Table;

SELECT Count(Worker_Ta ble.Worker) AS CountOfWorker
FROM Worker_Table;

SELECT [CountOfEmployee]+[CountOfWorker] AS SumTotal
FROM qryEmployeeCoun t, qryWorkerCount
GROUP BY qryEmployeeCoun t.CountOfEmploy ee, qryWorkerCount. CountOfWorker;

[Ericka]

Thanks for your reply. But I would like to know the SUM of records from both tables in the same query.

Please help me out.

[PEB]

Hi Ericka,

You can try the following:

 
Select
"Employee_Table" as Table_Name,
Count(*) As Record_Total
 
From Employer_table
 
UNION ALL
 
Select
"Worker_Table" as Table_Name
 
Count(*) As Record_Total
 
From Worker_Table
 
UNION ALL
 
Select
"All" as Table_Name
 
nz(DCount("*","Worker_Table",""))+nz(DCount("*","Employer_table","")) As Record_Total;
 
 

Best regards

PEB

:)

I used the following query to count the number of records from each table.

Select
"Employee_Table " as Table_Name,
Count(*) As Record_Total

From Employer_table

UNION ALL

Select
"Worker_Tab le" as Table_Name

Count(*) As Record_Total

From Worker_Table;

Can anyone help me to add the grand total(SUM) of all the records from both the tables.

[Ericka]

Thank you so much for your reply with code. When I used the code which you provided still I am getting error. Can you help me if possible .

[Ericka]

Thank you so much for your reply with code. When I used the code I am getting error ."Query input must contain at least one table or Query".Can you help me if possible

[PEB]

Is it working?

[Ericka]

Sorry for not responding immediately. I tried the way thatyou suggested. Although the query is giving the Grand total of the records, it is looping and giving the Grand total so many times.I dont know how to avoid looping.If you know any other way please let me know.

Thanks,
Ericka

[PEB]

So yeah it's possible

However try:

Select
"Employee_Table " as Table_Name,
Count(*) As Record_Total

From Employer_table

UNION ALL

Select
"Worker_Tab le" as Table_Name

Count(*) As Record_Total

From Worker_Table

UNION ALL

Select DISTINCT
"All" as Table_Name

Count(*)+nz(DCo unt("*","Employ er_table","")) As Record_Total
From Worker_Table;

[Ericka]

Thank you so much for exchanging knowledge with others.The query is working.

Regards,

Ericka