DSUM in Query assistance for total of specific rows is correct.

[orange]

I modified the sql to deal with your Date criteria, and to allow for employeeIds in (602,603)
and WorkIds in (7,10,12)

The revised SQL is

SELECT [%$##@_Alias].EmployeeID
,[%$##@_Alias].WorkcodeID
,Sum([%$##@_Alias].SumOfBillableHours) AS SumOfSumOfBillableHours
FROM (SELECT TimeCard.EmployeeId
, Sum(tblTimeCardHours.BillableHours) AS SumOfBillableHours
, tblTimeCardHours.dateWorked
, tblTimeCardHours.workCodeId
FROM 
tblTimeCardHours INNER JOIN TimeCard ON
 tblTimeCardHours.TimeCardId = TimeCard.TimeCardid
GROUP BY 
TimeCard.EmployeeId, tblTimeCardHours.dateWorked, tblTimeCardHours.workCodeId
HAVING 
(((TimeCard.EmployeeId) IN (602,603)) AND
 ((tblTimeCardHours.workCodeId)In(7,10,12) AND
 tblTimeCardHours.dateWorked>#31/12/2013# ))
)  AS [%$##@_Alias]
GROUP BY [%$##@_Alias].EmployeeID
,[%$##@_Alias].WorkcodeID;

The result of this SQL/query is:

EmployeeID	WorkcodeID	SumOfSumOfBillableHours
602	7	5
602	10	7
602	12	40
603	10	5
603	12	4

Because of the way the foum adds this graphics, I had to put the results before the test data!! It was trying to put my text entries into the table data grids??? So read the table/data changes first to get the right sequence of events.




I modified test data to have the following data:

Added employeeID 603

EmployeeId	TimeCardid
602	1
603	2

Added workcodeIds and hours for EmployeeID 602 and 603 to ensure here were records for
both employees and workcodeIds including 7, 10 and 12

TimeCardIhoursId	BillableHours	dateWorked	workCodeId	TimeCardId
1	8	20/01/2014	12	1
2	8	02/03/2014	12	1
3	8	24/02/2014	12	1
4	8	05/09/2014	12	1
5	8	05/12/2014	12	1
6	7	05/12/2014	10	1
7	5	23/03/2014	7	1
8	4	24/03/2014	12	2
9	5	24/03/2014	10	2

[SalVOM]

Ok, i've added the missing select and it gave me the correct total thanks but i've still need the list of "DateWorked" to make sure i'm pulling & summing all the right dates. How do i get those back to display?

[orange]

Did you look at the last post I sent? It has 2 employeeids, 3 different workcodeIds, and a list of the hoursworked and worktypeId. The latest SQL was set up to give you

Sum Hoursworked by worktypeId and EmployeeId

[SalVOM]

Sorry, i missed it! will try it.

[SalVOM]

I've entered the revised SQL and modified with the correct table names but errors with "engine can't fine table/query TimeCardHours". which is the correct name of the table!?

SELECT [%$##@_Alias].EmployeeID
,[%$##@_Alias].WorkcodeID
,Sum([%$##@_Alias].SumOfBillableHours) AS SumOfSumOfBillableHours
FROM
(SELECT TimeCards.EmployeeId ,
Sum(TimeCardHours.BillableHours) AS SumOfBillableHours ,
TimeCardHours.dateWorked ,
TimeCardHours.workCodeId
FROM
TimeCardHours INNER JOIN TimeCards ON TimeCardHours.TimeCardId = TimeCards.TimeCardid
GROUP BY
TimeCards.EmployeeId,
TimeCardHours.dateWorked,
TimeCardHours.workCodeId
HAVING
(((TimeCards.EmployeeId) IN (602,603)) AND
((TimeCardHours.workCodeId)In(7,10,12) AND TimeCardHours.dateWorked>#31/12/2013# ))
) AS [%$##@_Alias]
GROUP BY [%$##@_Alias].EmployeeID
,[%$##@_Alias].WorkcodeID;

[orange]

Your table is named Time Card Hours according to your jpg.

So you have to put the name inside [] brackets because it has embedded spaces.[Time Card Hours]


As mentioned in post #10
Do yourself a major favor -adopt a naming convention that only uses A-Z0-9 and _
You'll save yourself lots of frustration with syntax issues.

Good luck

[amrut]

Sorry to step in.
Access treats TimeCardHours and Time Card Hours as different tables. You screenshot shows Time Card Hours as table name and you are using TimeCardHours.

[SalVOM]

I appologize for the ignorance of my Access knowledge as i was trying to save myself the fustration by just using a microsoft templete and didn't change any specifications of what was built. Not knowing Access/syntax has been fustrating all in itself and I greatly do appreciate all the help.

[orange]

Did you get it working??
None of us knew the pitfalls/issues with database/access when we started out. We've learned from our mistakes, assumptions and from forums and posts like this. And nobody knows it all.
You can learn something from anyone if you're open and listen long enough.
SQL is not a trivial subject, and Access's formatting/punctuation can be frustrating at times.

Hang in.

[SalVOM]

Well I have learned alot, thanks! and the query ran this time but the results were empty, perhaps to many brackets?

SELECT [%$##@_Alias].EmployeeID, [%$##@_Alias].WorkCodeID, Sum([%$##@_Alias].SumOfBillableHours) AS SumOfSumOfBillableHours
FROM (SELECT [Time Cards].EmployeeID, Sum([Time Card Hours].BillableHours) AS SumOfBillableHours, [Time Card Hours].DateWorked, [Time Card Hours].WorkCodeID FROM [Time Card Hours] INNER JOIN [Time Cards] ON [Time Card Hours].TimeCardID = [Time Cards].TimeCardID GROUP BY [Time Cards].EmployeeID, [Time Card Hours].DateWorked, [Time Card Hours].WorkCodeID HAVING ((([Time Cards].EmployeeID) IN (602,603)) AND (([Time Card Hours].workCodeID)In(7,10,12) AND [Time Card Hours].DateWorked>#12/31/2013# ))) AS [%$##@_Alias]
GROUP BY [%$##@_Alias].EmployeeID, [%$##@_Alias].WorkCodeID;

[orange]

The query should not run if there is a syntax error. Now if there are offsetting syntax issues it may run...

When I copied your sql to look at it I noticed a space in TimeCard ID-- don't know if it's real or an issue with formatting on the forum

..........
FROM [Time Card Hours] INNER JOIN [Time Cards] ON
[Time Card Hours].TimeCard ID = [Time Cards].TimeCardID 
GROUP BY [Time Cards].EmployeeID
, [Time Card Hours].DateWorked
, [Time Card Hours].WorkCodeID 
...............................

I'm going to adjust my mock up to match your names.........

[orange]

I just renamed my tables to match yours.

I ran this SQL and it works.

SELECT [%$##@_Alias].EmployeeID, [%$##@_Alias].WorkcodeID, Sum([%$##@_Alias].SumOfBillableHours) AS SumOfSumOfBillableHours
FROM (SELECT [Time Cards].EmployeeId
, Sum([Time Card Hours].BillableHours) AS SumOfBillableHours
, [Time Card Hours].dateWorked
, [Time Card Hours].workCodeId 
FROM
 [Time Card Hours] INNER JOIN [Time Cards] ON
 [Time Card Hours].TimeCardId=[Time Cards].TimeCardid 
GROUP BY [Time Cards].EmployeeId
, [Time Card Hours].dateWorked
, [Time Card Hours].workCodeId 
HAVING
 ((([Time Cards].EmployeeId) In (602,603)) And
 (([Time Card Hours].workCodeId) In (7,10,12) And
 [Time Card Hours].dateWorked>#12/31/2013#)))  AS [%$##@_Alias]
GROUP BY [%$##@_Alias].EmployeeID
, [%$##@_Alias].WorkcodeID;

Results, same data as before

EmployeeID	WorkcodeID	SumOfSumOfBillableHours
602	7	5
602	10	7
602	12	40
603	10	5
603	12	4

[SalVOM]

looks it's the Forum because there is no space at that location.

SELECT [%$##@_Alias].EmployeeID,
[%$##@_Alias].WorkCodeID,
Sum([%$##@_Alias].SumOfBillableHours) AS SumOfSumOfBillableHours
FROM
(SELECT [Time Cards].EmployeeID,
Sum([Time Card Hours].BillableHours) AS SumOfBillableHours,
[Time Card Hours].DateWorked,
[Time Card Hours].WorkCodeID
FROM [Time Card Hours] INNER JOIN [Time Cards] ON [Time Card Hours].TimeCardID = [Time Cards].TimeCardID
GROUP BY [Time Cards].EmployeeID,
[Time Card Hours].DateWorked,
[Time Card Hours].WorkCodeID
HAVING ((([Time Cards].EmployeeID) IN (602,603)) AND (([Time Card Hours].workCodeID)In(7,10,12) AND [Time Card Hours].DateWorked>#12/31/2013# ))) AS [%$##@_Alias]
GROUP BY [%$##@_Alias].EmployeeID, [%$##@_Alias].WorkCodeID;



and it runs:

Employee ID	Work Code ID	SumOfSumOfBillableHours

[SalVOM]

Copied straight from what was sent i results were still empty.

[orange]

Are the field names in your tables the same as I am using?
Can you show jpg of your table field names?

If you want to do a copy of your database; compact and repair, and zip it I'll look at it.

Earlier the EmployeeId and EmployeeName were a concern --Is that still an issue?