Query Time Difference AND Monthly Totals

[valsanch]

Hello All,

I am VERY, and I mean VERY new to Access. I've been racking my brain all afternoon and googling like crazy. I just completed two levels of training on Access 2010 and have never worked with the program before. I already set up my tables and now I am on to querying. I have a table with several columns, two of which are "Start Time" and "End Time". I already created a query using the DateDiff function to calculate the time difference for each record. It output a new field with the time difference in hours. Now, I want to sum the totals of the time differences by month and I cannot for the life of me figure it out. My new query has Date (m/dd/yyy) and Hours.

Can someone please tell me how I can sum the time difference totals by month? Your help is greatly appreciated!

Regards,
Valerie

 

[CJ_London]

There is a function called Month so your query needs to

Select Month(YourDate), sum (TimeDiff)
From YourTable
GROUP BY Month(YourDate)

Note - avoid using reserved words - common ones are name, date, table, time, field

 

[valsanch]

Thanks! Unfortunately, I'm not doing the sum function correctly. When I add, Month(Date) to the third column of the query, it will return the correct corresponding number. However, when I add the sum(contact hours) it says I need quotes around everything so when I add the quotes and run the query the third column says exactly what I typed into the builder no number just text.

 

[valsanch]

I ended up adding the month function to the same datediff query. I then did a new query summing the hours by month. I get the right numbers. Is it not productive to have done the datediff, month and sum functions in two separate queries?

 

[Brianwarnock]

When asking for advice on queries it is useful to show the SQL of your query, you can switch to this from design view and copy and paste it, remember to put it in code tags.

You can do what you want in one query, you say that your first query as shown by CJ_London, the timediff field he showed was your Datediff calculation , they are the only two fields needed to be selected.

NOTES I see that you have spaces in your field names, avoid spaces and special character except the underscore_ , as you progress they will cause syntax problems and prevent Access using intellisense.

Also you obviously have separate Date and Time fields, that is also to be avoided, it is easy to split each out of a composite date/time field using Datevalue and Timevalue but more difficult to put them back together .

Brian

 

[valsanch]

Brian,

Thank you so much for your advice. I really want to be as clean as possible as I learn access and put it into practice. I went back and took the spaces out of my field headings. The reason I have date and time separated is because I am tracking date, start time and finish time. This is my code:
SELECT tblActivity.Date, Round(Sum(DateDiff('n',[FinishTime],[StartTime])/60),2) AS SimTime, tblActivity.[#ofLearners], ([SimTime]*[#ofLearners]) AS TotalContactHours
FROM tblActivity
GROUP BY tblActivity.Date, tblActivity.[#ofLearners], ([SimTime]*[#ofLearners])
ORDER BY tblActivity.Date;

I have my field, "TotalContactHours" and I want to sum them by month. This is the part I am having trouble with. In addition, when I run this query, I am getting a parameter value prompt. I am not sure what it is asking me to enter, so I just leave it blank and click ok to run the report. Now, when I add CJ-London's code to my query on the 4th column in the builder, when I run the query, it is giving me the text I typed and not the summation by month:
SELECT tblActivity.Date, Round(Sum(DateDiff('n',[FinishTime],[StartTime])/60),2) AS SimTime, tblActivity.[#ofLearners], ([SimTime]*[#ofLearners]) AS TotalContactHours, "Month([Date]), Sum([TotalContactHours])" AS Expr1
FROM tblActivity
GROUP BY tblActivity.Date, tblActivity.[#ofLearners], ([SimTime]*[#ofLearners]), "Month([Date]), Sum([TotalContactHours])"
ORDER BY tblActivity.Date;

Any ideas?

 

[CJ_London]

you have a superfluous double quotation marks in both the select part and group by parts of the code

[COLOR=red]"[/COLOR]Month([Date]), Sum([TotalContactHours])[COLOR=red]"[/COLOR]

Date is a reserved word so recommend changing that as well

 

[valsanch]

Hi CJ_London, correct, but if I take them off, and try to run the query, I get an error message reading, "The expression you entered contains invalid syntax, or you need to enclose your text data in quotes."

 

[jdraw]

These values
"Month([Date]), Sum([TotalContactHours])" AS Expr1 are being treated as a string. It is not being evaluated.
As CJ said the quotes are not needed, in either location.If you get a syntax error so be it, have to correct the error. But quotes are only hiding your issue.

 

[CJ_London]

Date is a reserved word which is almost certainly giving you problems. Besides which if you want to group by month(date) - which means "group by today" - and there is only one today it won't achieve anything because you are also grouping by tblactivity.date

Try: And please note the square brackets round Date and that Month is also a reserved word (like date, it is a function) hence using ActivityMonth

SELECT [COLOR=red]month(tblActivity.[Date]) AS ActivityMonth,[/COLOR] Round(Sum(DateDiff('n',[FinishTime],[StartTime])/60),2) AS SimTime, tblActivity.[#ofLearners], ([SimTime]*[#ofLearners]) AS TotalContactHours,Sum([TotalContactHours]) AS Expr1
FROM tblActivity
GROUP BY [COLOR=red]month(tblActivity.[Date]),[/COLOR] tblActivity.[#ofLearners], ([SimTime]*[#ofLearners]), Sum([TotalContactHours])
ORDER BY [COLOR=#ff0000]month(tblActivity.[Date])[/COLOR]

 

[valsanch]

Hello,

I ended up focusing on another part of the database and now I am back to this query. Sorry for the constant questioning, probably due to my new entry into the Access World. I went back and got rid of the fidl name "Date" and changed it to "SimDate". Here is my code:

SELECT tblActivity.SimDate, Round(Sum(DateDiff('n',[FinishTime],[StartTime])/60),2) AS SimTime, tblActivity.[#ofLearners], ([SimTime]*[#ofLearners]) AS TotalContactHours, Month([SimDate]) AS TotalContactHoursbyMonth
FROM tblActivity
GROUP BY tblActivity.SimDate, tblActivity.[#ofLearners], ([SimTime]*[#ofLearners]), Month([SimDate])
ORDER BY tblActivity.SimDate;

I am still trying to sum the "TotalContactHours" field by month. In the builder, when I input Month([SimDate]), Sum([TotalContactHours]), I keep getting a syntax error. I don't know where I am going wrong! Any insight or guesses is appreciated. Thanks in advance for your patience.

 

[valsanch]

Additional info: My field "TotalContactHours" isn't from a table. I actually created this field by subtracting FinishTime from StartTime. Not sure if this is creating a problem...

 

[RainLover]

In the archives there are some Date and Time functions written by Pat Hartman. They may help.

I have also attached a sample Database that may be of help to you. (Access 2003)

The other advice you have been given is also good. Especially the use of Reserved words. Do a Google on Camel Hump. You should get a SampleSetOfDataThatLooksLikeThs, It is what most people use.

The attachment is a sample only and is meant to be a guide. Just take out the good bits that you want. PS It also works in the negative.

Any faults found by anyone please let me know.

Remember this. There is not 365 days in a year, Date/Time should be stored in the same field as previously said. Dates are stored as numbers. In my sample If you enter 1st April you will get Date as Long of 41365. (Only if today is 11/04/2013)

Hope this helps a little. Dates are not as simple as one would think.

 

[Brianwarnock]

I am puzzled as to why CJLONDON,s code in post 10 does not work for you.

Note also that any summing that you do will be by Group, you group on activitydate plus others therefore it w ill not give a monthly total.

Brian

 

[gayansam]

Dear all,

How to calculate two between Time, Day shift is In time 8:00 and out time 20:00 then Night shift is in time 20:00 to 8:00, how to calculate them I don't know

Please help me

Thank you
:banghead:

 

[RainLover]

You need to use the Function DateDiff.

You can find out more about it in Access help along with other Date functions.

Normally one starts a new thread for a new question. I suggest that if I have not given enough information that you start a new thread.

You should include a sample or two and also explain the Data type you are using. Are you using Date/Time and are you storing Date and Time.

 

[gayansam]

Thanks RainLover,

Because result was (-) minus, so I made it in Excel because I need to create in Access,

Thank you.

You need to use the Function DateDiff.

You can find out more about it in Access help along with other Date functions.

Normally one starts a new thread for a new question. I suggest that if I have not given enough information that you start a new thread.

You should include a sample or two and also explain the Data type you are using. Are you using Date/Time and are you storing Date and Time.

 

[RainLover]

There is a solution given the proper information to start with.

I don't know how excel is going to help.

Try asking again under a thread of your own. You will also get a lot more helpers that way.

 

[gayansam]

Dear All,

I need to calculate work hours, My code is Round(DateDiff('n',[Out_T]-[In_T],1)/60), Because 8:00 to 22:00 result was 10 hours, what is wrong can you help me...

Thanks
Gayan

 

[CJ_London]

You have coded

Round(DateDiff('n',[Out_T]-[In_T],1)/60

The actual structure required is
DateDiff(interval, date1, date2 [, firstdayofweek] [, firstweekofyear] )

So you have set date1=[Out_T]-[In_T]
and date2=1