Problem converting SQL to DSum

[toast]

Hello all,

I'm struggling to convert an SQL statement to DSum.

The table setup is as follows:
DaySheets (Table 1) which contain multiple Shifts (Table 2) which are worked by various Employees (Table 3).

The SQL statement in question is:

SELECT DaySheets.DSdate, Sum(Shifts.ShiftDuration)*24 AS SumOfShiftDuration, Employees.CompanyID
FROM Employees INNER JOIN (DaySheets INNER JOIN Shifts ON DaySheets.DSPage = Shifts.DSPage)
ON (Employees.CompanyID = Shifts.Loader) OR (Employees.CompanyID = Shifts.Driver)
GROUP BY DaySheets.DSdate, Employees.CompanyID

I have tried the following in DSum:

=Dsum("[ShiftDuration]","Employees","INNER JOIN (DaySheets INNER JOIN Shifts
ON DaySheets.DSPage = Shifts.DSPage) ON (Employees.CompanyID = Shifts.Loader)
OR (Employees.CompanyID = Shifts.Driver)")

The result is #Error

The DSum is being used in a textbox on a continuous form of the employees, showing the total number of hours worked for each employee.

Any help would be greatly appreciated on what I need to do to fix the problem, or change the approach.

 

[Beetle]

There are several problems with your DSum.

First, according to your posted SQL, ShiftDuration is a field in Shifts but you're attempting to return that field from Employees.

Second, you can't use a Join in the criteria argument of a domain function. Without knowing more about your structure I couldn't tell you exactly how to structure your DSum, but a rough example might look like;

=DSum("[ShiftDuration]", "Shifts", "Loader = " & [CompanyID] & " Or Driver = " & [CompanyID])

However, having said that, DSum may not really be the most efficient method to use here, especially if this is a continuous form. It would be better to do this in a calculated field in a query then use that query as the Record Source of your form. Not sure if that was what you were trying to accomplish with your original SQL statement or if that was for some other purpose.

If you can post back with more info we may be able to offer you more specific advice on the best way to approach this.

 

[toast]

Thank you for your help. After playing around a lot with domain aggregates and recordsources, I think the best approach is a function returning a value from an appropriate SQL statement, then setting that function as the controlsource.

Now I just need to figure out some disambiguation on the joins...!