SUM & Count

[jadeverell]

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

[JKing]

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?

[JKing]

Here's an example:

qselGeneralContracts: Query of General Contracts

SELECT Count(Tbl_Contract_General_Head.[Supplier ID]) AS [No of General Contracts]
FROM
 

qselMaintenanceContracts: Query of Maintenance Contracts

SELECT Count(Tbl_Contract_Maintenance.[Supplier ID]) AS [No of Maintenance Contracts]
FROM Tbl_Contract_Maintenance.[Supplier ID]
 

Total Contracts

SELECT [No of Maintenance Contracts], [No of General Contracts],  [No of Maintenance Contracts] + [No of General Contracts] As TotalContracts
FROM qselGeneralContracts, qselMaintenanceContracts