By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,401 Members | 1,381 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,401 IT Pros & Developers. It's quick & easy.

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

P: 7
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_Table" 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.
Oct 1 '06 #1
Share this Question
Share on Google+
9 Replies


100+
P: 143
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_Table.Worker) AS CountOfWorker
FROM Worker_Table;

SELECT [CountOfEmployee]+[CountOfWorker] AS SumTotal
FROM qryEmployeeCount, qryWorkerCount
GROUP BY qryEmployeeCount.CountOfEmployee, qryWorkerCount.CountOfWorker;
Oct 2 '06 #2

P: 7
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.
Oct 2 '06 #3

PEB
Expert 100+
P: 1,418
PEB
Hi Ericka,

You can try the following:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Select
  3. "Employee_Table" as Table_Name,
  4. Count(*) As Record_Total
  5.  
  6. From Employer_table
  7.  
  8. UNION ALL
  9.  
  10. Select
  11. "Worker_Table" as Table_Name
  12.  
  13. Count(*) As Record_Total
  14.  
  15. From Worker_Table
  16.  
  17. UNION ALL
  18.  
  19. Select
  20. "All" as Table_Name
  21.  
  22. nz(DCount("*","Worker_Table",""))+nz(DCount("*","Employer_table","")) As Record_Total;
  23.  
  24.  
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_Table" 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.
Oct 2 '06 #4

P: 7
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 .
Oct 2 '06 #5

P: 7
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
Oct 2 '06 #6

PEB
Expert 100+
P: 1,418
PEB
Is it working?
Oct 7 '06 #7

P: 7
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
Oct 7 '06 #8

PEB
Expert 100+
P: 1,418
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_Table" as Table_Name

Count(*) As Record_Total

From Worker_Table

UNION ALL

Select DISTINCT
"All" as Table_Name

Count(*)+nz(DCount("*","Employer_table","")) As Record_Total
From Worker_Table;
Oct 7 '06 #9

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

Regards,

Ericka
Oct 10 '06 #10

Post your reply

Sign in to post your reply or Sign up for a free account.