Counting Distinct Records Across Joined Tables

[dbc23]

This is bugging me, and it seems there should be a simple answer.

I need to join three normalized SQL tables to get a link between the primary key I want to group by (DriveID) and the DISTINCT count I want (MobileSetupMaster.Description).

The issue is each DriveID has multiple ShiftIDs (potentially) wherein each ShiftID will have the SAME MSM.Description field (same item on both shifts). When what I need to know is how many distinct items are at each DriveID regardless of # of shifts, and ShiftID doesn't link directly to the ONLY table with a count of Alyx so I need to then join them on a third field MobileID.

My current workaround is doing a second query to count shifts and then use an expression to divide by that but it's cludgy and not 100% accurate and these numbers are about to be very important for some high level reports.

Is there a way to get this three degree join to count only distinct descriptions associated with a DriveID? I've been reading a lot of "count distinct" posts and pages but they all seem to only relate to when the query is working within a single table, it seems that my joins are what's complicating it.

The HAVING is necessary because the table i'm pulling from records historic records that were associated and they are constantly being modified, so that field shows what is currently associated.



Also, the WHERE criteria are a workaround for a non-standard naming convention that (I HOPE) will be fixed in the near future but is in the hands of a separate team.

SELECT dbo_DriveMaster.DriveID, Count(dbo_MobileSetupMaster.Description) AS TotalAlyx INTO [#AlyxAssigned]


FROM ((dbo_MobileSetupMaster RIGHT JOIN dbo_DriveShiftMobileDetail ON dbo_MobileSetupMaster.MobileID = dbo_DriveShiftMobileDetail.MobileID) RIGHT JOIN dbo_DriveShiftDetail ON dbo_DriveShiftMobileDetail.ShiftID = dbo_DriveShiftDetail.ShiftID) RIGHT JOIN dbo_DriveMaster ON dbo_DriveShiftDetail.DriveID = dbo_DriveMaster.DriveID


WHERE (((dbo_DriveMaster.FromDateTime) Between Date()-365 And Date()+365) AND ((dbo_MobileSetupMaster.Code) Like "@*") AND ((dbo_MobileSetupMaster.Description) Not Like "*Trima*" And (dbo_MobileSetupMaster.Description) Like "*Aly*"))


GROUP BY dbo_DriveMaster.DriveID, dbo_DriveShiftMobileDetail.MostRecent


HAVING (((dbo_DriveShiftMobileDetail.MostRecent)=True));

Any input is GREATLY appreciated.

[June7]

Options:

1. Do aggregate query to get proper group count and join the aggregate query to master dataset.

2. Domain aggregate function DCount.

If you want to provide db for analysis, follow instructions at bottom of my post.