Can't work out a query

[stc]

Hello,

I am having problems with a query I am trying to run. The query involves two fields, BusinessTypeID and DateCustomerActive. I want to be able to count the total number of customers within each BusinessTypeID (which ranges from 1 - 24) who have a value in the DateCustomerActive field, and then display them in a form. So do I need to assign a field name to the results of each calculation? If so, how do I do this for all 24 BusinessTypeIDs in one query? Thanks for any help you can offer - I think I may be making this more complicated than it needs to be!

 

[Guest_imported]

I'm not sure if this is really what you were after but the SQL below would count the number of active customers by each business type. If there are no active customers in the business type then it will miss that business type out.

SELECT [Table1].[BusinessTypeID], Count([Table1].[BusinessTypeID]) AS CountOfBusinessTypeID
FROM Table1
WHERE (((IsNull([DateCustomerActive]))=False))
GROUP BY [Table1].[BusinessTypeID];

However if you use a left join from the buisiness types table you will get all the possible business types listed and a count of zero when there are no customers who have that business type ID. On your form just create a link to this query and size the display area so that it has 24 lines and you should be away.

SELECT tblBusinessTypes.BusinessTypeID, Count(Table1.BusinessTypeID) AS CountOfBusinessTypeID
FROM tblBusinessTypes LEFT JOIN Table1 ON tblBusinessTypes.BusinessTypeID = Table1.BusinessTypeID
GROUP BY tblBusinessTypes.BusinessTypeID;

Good luck

R