Summing Calculated Field in a query

[Hargo]

Hi

I have a query which calculates the duration of each activity undertaken by a patient by simply deducting the [Start Time] from the [End Time] - I have called this field within the query [Duration]

What I now need is to Total the [Duration] field for the week so I can see the number of hours of activities a patient does in a week

I have used Between Date1 & Date2 in the Date Field Criteria but have no idea what the new formula or expression would be

The query currently returns 3 'records' / lines (as the patient did three activites between the dates selected) but I imagine it has to somehow aggregate these so that each patient only has 1 'record / line

Hope that makes sense (i'm a novice at queries)

[zmbd]

More information than what you need now; however, you should bookmark for future reference:
Sum data by using a query


You'll also want: Aggregate Query Woes


and a handy reference:
Basic SQL Syntax for Access Queries


If you are still stuck please post your SQL
Open the Query in design mode
Right click in the table area
Select SQL View
IN a post here, click on the [CODE/] button in the toolbar
Now Cut and Paste the SQL between the two [code][/code] tags.

[Hargo]

Thanks again zmbd I'll have a read and see how I get on

[Hargo]

I have managed most of what I wanted using a Crosstab Query of a Total Query, both shown below:

SELECT tbl_25_Hours.[Patient ID], tbl_25_Hours.[Activity Date], tbl_25_Hours.[Start Time], tbl_25_Hours.[End Time], tbl_25_Hours.Activity, tbl_Patient_Information.Surname, tbl_Patient_Information.[Forename(s)], Sum([End Time]-[Start Time]) AS Duration
FROM tbl_Patient_Information INNER JOIN (tbl_Admissions INNER JOIN tbl_25_Hours ON tbl_Admissions.[Patient ID] = tbl_25_Hours.[Patient ID]) ON tbl_Patient_Information.[NHS Number] = tbl_Admissions.[NHS Number]
GROUP BY tbl_25_Hours.[Patient ID], tbl_25_Hours.[Activity Date], tbl_25_Hours.[Start Time], tbl_25_Hours.[End Time], tbl_25_Hours.Activity, tbl_Patient_Information.Surname, tbl_Patient_Information.[Forename(s)]
HAVING (((tbl_25_Hours.[Activity Date]) Between #6/10/2014# And #6/17/2014#));

TRANSFORM Sum([25 Hour Activity Duration Calculation].Duration) AS SumOfDuration
SELECT [25 Hour Activity Duration Calculation].[Patient ID], Sum([25 Hour Activity Duration Calculation].Duration) AS [Total Of Duration]
FROM [25 Hour Activity Duration Calculation]
GROUP BY [25 Hour Activity Duration Calculation].[Patient ID]
PIVOT [25 Hour Activity Duration Calculation].Activity;
 

What I'd like is for the date part (bold) to be input by the user

I tried to make the Total Query a Parameter query using [Activity Date] BUT got the following message:

"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables"


Any advice?

[twinnyfo]

Hargo,

Concerning your first question, change line 4 of your first block of code to:

HAVING (((tbl_25_Hours.[Activity Date]) Between [Start Date?] And [Stop Date?]));

You just have to make sure that the user enters a valid date format (e.g. m/d/yy). You can put that into your input string, too.

HAVING (((tbl_25_Hours.[Activity Date]) Between [Start Date (m/d/yy)?] And [Stop Date (m/d/yy)?]));

This can become challenging if this is a raw SQL query. If you were building your string in VBA, you could validate a date.

Hope this hepps!

[Hargo]

Hi Twinnyfo

Just checking through the workings of Db and a problem has popped up with this crosstab query

Since Patient ID became a lookup of AdmisisonsKey I am getting:

Type mismatch in expression message when I try to run the query

It appears to NOT like PatientID as a row heading, presumably because it is a number now rather than text?

Any suggestions on a fix?



Here's the code

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
TRANSFORM Sum([qry_Activity_Duration_&_Dates_Select].Duration) AS SumOfDuration
SELECT [qry_Activity_Duration_&_Dates_Select].PatientID, [qry_Activity_Duration_&_Dates_Select].Forenames, [qry_Activity_Duration_&_Dates_Select].Surname, Sum([qry_Activity_Duration_&_Dates_Select].Duration) AS TotalActivityDuration
FROM [qry_Activity_Duration_&_Dates_Select]
WHERE ((([qry_Activity_Duration_&_Dates_Select].ActivityDate)>=[Start Date] And ([qry_Activity_Duration_&_Dates_Select].ActivityDate)<=[End Date]) AND (([qry_Activity_Duration_&_Dates_Select].Outcome)="Accepted"))
GROUP BY [qry_Activity_Duration_&_Dates_Select].PatientID, [qry_Activity_Duration_&_Dates_Select].Forenames, [qry_Activity_Duration_&_Dates_Select].Surname
PIVOT [qry_Activity_Duration_&_Dates_Select].Activity;