Function Unique Count

[Danarath]

Hi all,

I have continuing relationship One to Many between 3 tables that relation fields are [ServiceName], [UnitName] and [StaffName].
I would like to know in one Service how many unit and how many staff?

Please could you tell me! there are exist some ways or some formulas that can be use in query to count in field [UnitName] and [StaffName], The result will be number of Unit and number of Staff? (in different in values not the same like in attach file).
Thanks toward hope some one help me.
Please look attach file.

 

[sneuberg]

It's not clear to me what you want to count. Given the tblService in the database you attached what should the output look like?

 

[JHB]

Your question is more or less the same as this, (is it some kind of school work, or are you the same person with a 2 different user name?) :
https://access-programmers.co.uk/forums/showthread.php?t=292481

 

[Danarath]

Hi all,

The results I wish in the same query number of unit and staff,
take a look in attach picture file.
Help me please!!!.

 

[sneuberg]

I believe qryCombinedCounts in the attached database does what you want. It takes a few queries to get there. Considering the count of the unique Unit Names within each Service Name the unique combinations are obtained in qryDistinctUnitsByService which is:

SELECT DISTINCT tblService.ServiceName, tblService.UnitName
FROM tblService;

the counts are obtained using qryDistinctUnitsByService in qryUnitCounts which is

SELECT qryDistinctUnitsByService.ServiceName, Count(qryDistinctUnitsByService.UnitName) AS CountOfUnitName
FROM qryDistinctUnitsByService
GROUP BY qryDistinctUnitsByService.ServiceName;

the same types of queries qryDistinctStaffByService and qryStaffCounts do the same things for the Staff Names.

qryStaffCounts and qryUnitCounts are joined in qryCombinedCounts to give the final results. It's possible that this could be done with less queries but this is it for now.

 

[JHB]

Hi all,

The results I wish in the same query number of unit and staff,
take a look in attach picture file.
Help me please!!!.

Did you read post #3

 

[Danarath]

Hi Sneuberg,

Thanks a lot!!! you got my wish!!!!!!

 

[sneuberg]

Would you answer JHB's question now? That was

Your question is more or less the same as this, (is it some kind of school work, or are you the same person with a 2 different user name?) : https://access-programmers.co.uk/forums/showthread.php?t=292481