UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Crosstab Query With Times Hh:mm, Access 2010    
 
   
dzoker
post Apr 13 2019, 10:45 AM
Post#1



Posts: 426
Joined: 12-February 14



Hi all,

I have a bit of a problem here. I'm creating a database that is to track times. the table is simple

DateN - ShortDate
TimeI - GeneralDate
TimeO - GeneralDate

In the table there are few records for now:

01/01/2019; 8:00:00AM; 1:45:00PM
02/01/2019; 10:00:00AM; 11:00:00PM
03/01/2019; 8:00:00AM; 11:35:00PM
04/01/2019; 5:00:00AM; 22:37:00PM

I also have a query from this table that does DateDiff btw TimeI & TimeO to get the total Minutes
Minutes: DateDiff("n",[TimeI],[TimeO])

In a report from this query I format the time as =[Minutes]\60 & Format([Minutes] Mod 60,"\:00") to get to display in HH:MM per date as well as the SUM of all the times. The problem now is to create a CrossTab query to display a Yearly report (per month) that will show the SUM of times per month. I can get the crosstab report to show SUM of Minutes/Month (Jan, Feb, Mar...) but when I try to Format those field to show SUM in HH:MM format, I get #Type! error. Note that the SUM of times/Month will be greater than 24HRS.

Any help would be greatly appreciated!!

Go to the top of the page
 
RJD
post Apr 13 2019, 12:56 PM
Post#2


UtterAccess VIP
Posts: 9,574
Joined: 25-October 10
From: Gulf South USA


Hi: I think we'll have to see more to help you diagnose this. How are you trying to create the SUM in the crosstab? Is the underlying value text rather than numeric? We are sort of flying blind here. What does the crosstab SQL look like? Can you post a db with the relevant objects so we can see the crosstab in action and test alternatives?

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
dzoker
post Apr 13 2019, 01:38 PM
Post#3



Posts: 426
Joined: 12-February 14



RJD,

I have attached a sample database. I have changed the tables a bit from my initial post to mimic what I try to do. As it stands now I have 2 issues.

1. How do I get to show the time in HH:MM format in the Query1_Crosstab report (you can see for Jan I tried using "=[Jan]\60 & Format([Jan] Mod 60,"\:00")" but got the error #Type!
2. If you look at this report, in June I have -1350 for minutes for the employee John. I know that this is due to the fact that I tried to calculate the DateDiff for a time that ends in 12:00:00AM but have no idea of how to fix it.

Thank you for your help
This post has been edited by dzoker: Apr 13 2019, 01:39 PM
Attached File(s)
Attached File  Test_Time_Database.zip ( 51.23K )Number of downloads: 3
 
Go to the top of the page
 
June7
post Apr 13 2019, 01:52 PM
Post#4



Posts: 460
Joined: 25-January 16



Why not save full date and time into Start and End? This should deal with the crossing midnight issue. That midnight date/time would be 6/2/2019. The difference calc is 120 minutes.







--------------------
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
dzoker
post Apr 13 2019, 02:02 PM
Post#5



Posts: 426
Joined: 12-February 14



June7,

Thank you for your answer. How do you suggest I do that? As you can see from the sample database I have only 1 field for date. The end time will never cross midnight (even though midnight is technically the next day). Maybe I should use 2 field for dates? But then I would still have the problem with time display in HH:MM

Thank you
Go to the top of the page
 
June7
post Apr 13 2019, 02:18 PM
Post#6



Posts: 460
Joined: 25-January 16



So why not save full date/time into Start and End fields?

Otherwise, yes another field for EndDate. Then in query add the date and time fields to get a full date/time value.

DateDiff("n", [DateEntry] + [StartTime], [DateEnd] + [EndTime])

or use table as is:

DateDiff("n", [DateEntry] + [StartTime], [DateEntry] + IIf([EndTime]=0,1,0) + [EndTime])

Each of these allows calculation to display in HH:MM.

The user input form doesn't need to change but code behind that form could save the inputs as full date/time, fixing the midnight date in the process.


This post has been edited by June7: Apr 13 2019, 02:27 PM

--------------------
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
dzoker
post Apr 13 2019, 02:28 PM
Post#7



Posts: 426
Joined: 12-February 14



June7,

QUOTE
Otherwise, yes another field for EndDate. Then in query add the date and time fields to get a full date/time value.

DateDiff("n", [DateEntry] + [StartTime], [DateEnd] + [EndTime])

or use table as is:

DateDiff("n", [DateEntry] + [StartTime], [DateEntry] + IIf([EndTime]=0,1,0) + [EndTime])

I see....That is a possible fix for issue #2

QUOTE
Each of these allows calculation to display in HH:MM.


What do you mean by this? Are you talking about the report?



Thank you
Go to the top of the page
 
June7
post Apr 13 2019, 02:41 PM
Post#8



Posts: 460
Joined: 25-January 16



Well, the normal report looks good. The crosstab version still needs work. Just need to fix the Circular Reference error. Rename each textbox so it isn't same as the field, like tbxJan. Be aware access will autochange the formula to use this name so then fix the Jan formula.

Also, change report layout so it fits on paper. 11x17 landscape works or resize textboxes and shift to use 8.5x11 landscape.


--------------------
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
dzoker
post Apr 13 2019, 03:01 PM
Post#9



Posts: 426
Joined: 12-February 14



June7,

Got it! This is just a sample version of the database that I put together for the purpose of this question, for sure it will be cleaned up later.

Like you said the crosstab report issue is still to be resolved.

Thank you for your help thumbup.gif
Go to the top of the page
 
June7
post Apr 13 2019, 03:08 PM
Post#10



Posts: 460
Joined: 25-January 16



Crosstab is fine after the adjustment I describe.


--------------------
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
dzoker
post Apr 13 2019, 04:22 PM
Post#11



Posts: 426
Joined: 12-February 14



June7,

QUOTE
Crosstab is fine after the adjustment I describe.

The query is ok but the report needs to show HH:MM

(EDIT): You are correct, it is right!!

Thank you!!
This post has been edited by dzoker: Apr 13 2019, 04:26 PM
Go to the top of the page
 
dzoker
post Apr 13 2019, 04:42 PM
Post#12



Posts: 426
Joined: 12-February 14



June7,

One more question. How do I format and add the totals per mount in the crosstab report?
Go to the top of the page
 
June7
post Apr 13 2019, 06:05 PM
Post#13



Posts: 460
Joined: 25-January 16



Textboxes in report footer section. Expression like: =Sum([Jan])\60 & Format(Sum([Jan]) Mod 60,"\:00")

--------------------
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
dzoker
post Apr 14 2019, 10:42 AM
Post#14



Posts: 426
Joined: 12-February 14



June7,

Great! Thank you for your help! cheers.gif
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th April 2019 - 02:55 PM