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

Quote:

Originally Posted by 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

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

Expand|Select|Wrap|Line Numbers

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

FROM

qselMaintenanceContracts: Query of Maintenance Contracts

Expand|Select|Wrap|Line Numbers

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

FROM Tbl_Contract_Maintenance.[Supplier ID]

Total Contracts

Expand|Select|Wrap|Line Numbers

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

FROM qselGeneralContracts, qselMaintenanceContracts

SUM & Count

re: SUM & Count

re: SUM & Count

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about