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_Contr act_Maintenance .[Supplier ID]) AS [No of General Contracts], Count(Tbl_Contr act_General_Hea d.[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_C ontract_Mainten ance.[Supplier ID]))+count(Tbl_Co ntract_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_Contr act_Maintenance .[Supplier ID]) AS [No of General Contracts], Count(Tbl_Contr act_General_Hea d.[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_C ontract_Mainten ance.[Supplier ID]))+count(Tbl_Co ntract_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:

qselGeneralCont racts: Query of General Contracts
[code=sql]
SELECT Count(Tbl_Contr act_General_Hea d.[Supplier ID]) AS [No of General Contracts]
FROM
[/code]

qselMaintenance Contracts: Query of Maintenance Contracts
[code=sql]
SELECT Count(Tbl_Contr act_Maintenance .[Supplier ID]) AS [No of Maintenance Contracts]
FROM Tbl_Contract_Ma intenance.[Supplier ID]
[/code]

Total Contracts
[code=sql]
SELECT [No of Maintenance Contracts], [No of General Contracts], [No of Maintenance Contracts] + [No of General Contracts] As TotalContracts
FROM qselGeneralCont racts, qselMaintenance Contracts
[/code]