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

SUM & Count

P: 19
Hi,

I am trying to perform a calculation that adds together two 'Counts' (on seperate tables) to give a total value.

The code for the two counts is:

Count(Tbl_Contract_Maintenance.[Supplier ID]) AS [No of General Contracts], Count(Tbl_Contract_General_Head.[Supplier ID]) AS [No of Maintenance Contracts]

I thought that i might have been able to use the 'Sum' Function in an expression like sum(count(Tbl_Contract_Maintenance.[Supplier ID]))+count(Tbl_Contract_General_Head.[Supplier ID]).....

but this is not working.

is there another way around this?

Thanks
Jan 8 '08 #1
Share this Question
Share on Google+
2 Replies


JKing
Expert 100+
P: 1,206
Hi,

I am trying to perform a calculation that adds together two 'Counts' (on seperate tables) to give a total value.

The code for the two counts is:

Count(Tbl_Contract_Maintenance.[Supplier ID]) AS [No of General Contracts], Count(Tbl_Contract_General_Head.[Supplier ID]) AS [No of Maintenance Contracts]

I thought that i might have been able to use the 'Sum' Function in an expression like sum(count(Tbl_Contract_Maintenance.[Supplier ID]))+count(Tbl_Contract_General_Head.[Supplier ID]).....

but this is not working.

is there another way around this?

Thanks
Hey there,

Have you tried making a separate query for each count and then combining those fields in a new query?
Jan 8 '08 #2

JKing
Expert 100+
P: 1,206
Here's an example:

qselGeneralContracts: Query of General Contracts
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(Tbl_Contract_General_Head.[Supplier ID]) AS [No of General Contracts]
  2. FROM
  3.  
qselMaintenanceContracts: Query of Maintenance Contracts
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(Tbl_Contract_Maintenance.[Supplier ID]) AS [No of Maintenance Contracts]
  2. FROM Tbl_Contract_Maintenance.[Supplier ID]
  3.  
Total Contracts
Expand|Select|Wrap|Line Numbers
  1. SELECT [No of Maintenance Contracts], [No of General Contracts],  [No of Maintenance Contracts] + [No of General Contracts] As TotalContracts
  2. FROM qselGeneralContracts, qselMaintenanceContracts
  3.  
Jan 8 '08 #3

Post your reply

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