Problems with dates and totals

A

astrom33

Registered User.
Local time
Today, 14:32
Joined
May 29, 2009
Messages
21
I have a table with a lot of transactions (date, time, name of employee, transaction number, etc.). There are two rows of importance to me--The date and time of the transaction. For example:

Date--------------Time
1/4/2004----- 1/4/2004 9:05:00 AM
1/4/2004----- 1/4/2004 9:08:00 AM
1/4/2004----- 1/4/2004 9:10:00 AM
1/4/2004----- 1/4/2004 9:10:00 AM
1/4/2004----- 1/4/2004 10:31:00 AM
1/5/2004----- 1/5/2004 1:00:00 PM
1/5/2004----- 1/5/2004 1:01:00 PM
1/5/2004----- 1/5/2004 1:08:00 PM
1/5/2004----- 1/5/2004 3:00:00 PM

I wanted to count the difference between the 1st transaction and 2nd transaction, 2nd transaction and 3rd transaction, and so on for each specific date. However, I want to exclude any time between the transactions that is greater than 10 minutes (by entering >10 in criteria). So, for example, the time between 9:10 a.m. and 10:31 a.m. on 1/4/2004 does not show up on my query.

The expression I used was:

[FONT=&quot]Delay: DateDiff("s",DMax("[TIME]","TableName","[DATE]=#" & [DATE] & "#
AND [TIME]<#" & [TIME] & "#"),[TIME])[/FONT]

However, there are two problems:

1. When I get the results for my query the time between 9:10 a.m. and 9:10 a.m. on 1/4/2004 comes up as 2 minutes, which is actually the time between 9:08 a.m. and 9:10 a.m. How can I fix this?

2. When I run a report I want to sum up the "Delay" times that are equal to less than 10 minutes each day. However, the results I get look like this:

--Date------------Time--------------Time Between Transaction
1/4/2004---- 1/4/2004 9:05:00 AM
1/4/2004---- 1/4/2004 9:08:00 AM---- 3
1/4/2004---- 1/4/2004 9:10:00 AM---- 2
etc., etc.,

But I want the totals to look like this:

--Date-----Time Between Transaction
1/4/2004---- 5
1/5/2004---- 8


Any help on these 2 problems? Thanks.
 
The first thing I notice, is that you have date twice. The field "Time" actually contains the date, so I don't think you need a separate field for Date.

I am not entirely sure how to do the calculation you want but it will make the table more normalized and efficient if you only have the "Time" field. You came to the reight place as there are plenty of people who will be able to help with the actual calculation. :)

Edit: Actually I know nothing at all about your calculation. :D
 
Last edited:
Kryst51 said:
The first thing I notice, is that you have date twice. The field "Time" actually contains the date, so I don't think you need a separate field for Date.

I am not entirely sure how to do the calculation you want but it will make the table more normalized and efficient if you only have the "Time" field. You came to the reight place as there are plenty of people who will be able to help with the actual calculation. :)

Edit: Actually I know nothing at all about your calculation. :D
Click to expand...

Yeah, but unfortunately eventually I will need that field because I will need to have a sum just for that day. I know I might not need it in the query, but I will need it in the report.
 
Well, what I meant was, there are ways to separate the date from the time using vba and sql, so you don't need to store it twice, I just don't know how to do it, only that it can be done. Storing the date twice is a normalization issue, and could cause you problems.
 
Thanks. I will remove the date from the query and the table. I think I'll do it once I get an answer to my 2 problems. I would hate to delete the field in the table to then find out I need it for a report.

Can anyone think of a way I can make it so I just have the daily totals in the report?
 
One Idea is to use your query in another query which sums by date on the minutes column.

Does anyone else have a better suggestion or more accurate or detailed? I really don't know what to suggest (Just thought that the two dates might cause problems.)?
 
Having the Date and DateTime wont screw things up...it's just that you are storing redundant data. By the way, to get separate parts of the DateTime, you would use the Format() function.

As to your problem...What you need to do is to be able to determine which is the start time and which is the end time. Normally, those two times would ideally be store in the same record. But since they aren't in this case, is there an ID number that ties the start and end time together?
 
Scooterbug said:
Having the Date and DateTime wont screw things up...it's just that you are storing redundant data. By the way, to get separate parts of the DateTime, you would use the Format() function.

As to your problem...What you need to do is to be able to determine which is the start time and which is the end time. Normally, those two times would ideally be store in the same record. But since they aren't in this case, is there an ID number that ties the start and end time together?
Click to expand...

Wanted to give you fame:
You must spread some Reputation around before giving it to Scooterbug again.
Thanks for your help, I didn't want to screw up getting his question answered! :(
 
Kryst51 said:
One Idea is to use your query in another query which sums by date on the minutes column.

Does anyone else have a better suggestion or more accurate or detailed? I really don't know what to suggest (Just thought that the two dates might cause problems.)?
Click to expand...


Thanks. I'll try it out. As for the dates, unfortunately that is how the table was given to me and I am very hesitant to delete a field on a table. Although I know the date is also included in the txn_tm column as well.
 
Ok. I tried the query of a query and I was able to get daily totals. However, I tried it on my desktop and it took a LOOOOOONG time. By long I mean a couple of hours. I then tried it on my dual-core laptop (3gb RAM) and it got me the results in maybe 30 minutes. Still a long time.

I will try these method for now, but does anyone have any other (quicker) suggestions.
 
Last edited:
Tip: When making changes to your db make a copy of your database and making changes there, that way if something goes wrong you haven't messed up the original.

Does Scooterbug's answer help you at all? If you stick with him he could give you a better way. He's a bit brighter with these things than I am, so he could probably also help you figure out why the query within a query thing is taking so long. (I would never wait 30 mins for a query to run....Sorry)
 
Is it possible for you to post the db? You can remove any sensative data if need be. Also, posting it in Access 200-2003 will get you more responses if you are using Acess 2007.

Also, I noticed that in your orgininal post, your Dmax forumula had field names like Date and Time. Those are reserved words and should not be used as field names.

Off hand, I can think of a way to find the time between the first and second time (I am assuming that they are start and stop times) as long as there is some kind of ID number tying the two together. As for finding the time between the stop time and the next start time....I would have to see how things are setup.
 
Scooterbug said:
Is it possible for you to post the db? You can remove any sensative data if need be. Also, posting it in Access 200-2003 will get you more responses if you are using Acess 2007.

Also, I noticed that in your orgininal post, your Dmax forumula had field names like Date and Time. Those are reserved words and should not be used as field names.

Off hand, I can think of a way to find the time between the first and second time (I am assuming that they are start and stop times) as long as there is some kind of ID number tying the two together. As for finding the time between the stop time and the next start time....I would have to see how things are setup.
Click to expand...

Let me create a dummy table with dummy information but with the same fields as the original db.
 
That would help. I did a quick db with the data you provided...and I get the feeling that this is some kind of Timesheet/Payroll database?
 
Well, a quick look and I found a way to calculate it...but you will more than likely have to put the data into a separate table and link it to get what you need. Here is the code that I came up with...I put it on the On Click Event of a button:

Code: 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim dtmTime1 As Date
Dim dtmTime2 As Date

Set db = CurrentDb
Set rs = db.OpenRecordset("Query1")
 

With rs
rs.MoveFirst
Do
    Do Until .EOF
    dtmTime1 = ![txn_tm]
        If dtmTime2 = "12:00:00 AM" Then
            .MoveNext
            dtmTime2 = dtmTime1
            dtmTime1 = ![txn_tm]
        Else
            Me.txtHolder = Me.txtHolder & "Time for " & ![txn_id] & ": " & DateDiff("s", dtmTime2, dtmTime1) & vbCrLf
            dtmTime2 = dtmTime1
            .MoveNext
        End If
    Loop
Loop Until .EOF
End With

I put a text box on the form in order to make sure the info was being calculated correctly. Gotta head home..will answer any questions tomorrow.
 
Thanks for the reply. Unfortunately, now I'm confused. My lack of experience is definitely showing.

First, I don't know how to work with buttons. I can work with forms just a bit.

Will I have to create a new table?
FYI: The data that I have is set and no more data is being added.

If so, do I just cut and paste from the old table? If not, what do I do in the new table?

Basically, what do I do with the code you just provided.
 
Sorry about yesterday....had to leave and wanted to get at least something out there for ya :)

I've attached the database for ya. I added a few lines...namely I added coded to take the time difference and add it to another table (Table 2) along with the txn_id number so that you can match things up. You will have to create the table in your database. The code also uses a query to pull it's data (Query1). I did this so that the data can be sorted cronologically.

In the database, you will notice a Form1. That is where the button is for the code to process your data. I've added comments in the code to explain what each line is doing for your reference.

Use the attached database as a reference.

As for making a button, go into the Form Design. A toolbar should appear that have icons for different controls. Find the one for Command Button. If the wizard starts, just cancel out. Once the button is on the form, right click on the button and go into properties. You will see a tab called Events. Find the one called On Click. Click on the field and you will see a small button to the right of the field with 3 dots. Click on that. Choose Code Builder. Copy the code, making sure it's between:

Private Sub Command_Click()
and
End Sub
 

Attachments

Thanks Scooterbug. I will test out the sample data and see what happens.

As a sidenote, I left my desktop running one query all night. This morning I woke up and it had the results. So I will definitely not be trying that query again.
 
The first thing I notices was that on table 2 have records repeating themselves at ID # 37.
Is that supposed to be that way? Also how is the difference being calculated? A formula in the table? An expression? Sorry if these are stupid questions.

I saw your form and it looks great. If I add dates to "table1" it keeps calculating the difference.
 
Last edited:
You must log in or register to reply here.

Similar threads

MattBaldry
Start and End Times with Overlap
Replies
11
Views
375
arnelgp
arnelgp
C
Query Help on "Lookup" Type Function
Replies
12
Views
310
arnelgp
arnelgp
N
Add RowNumber Problem
Replies
11
Views
651
CJ_London
C
GPGeorge
Part II of Creating Add-ins that run in Access and Office with Geoffrey L. Griffith
Replies
4
Views
558
strive4peace
strive4peace
D
Date and Time Format
Replies
12
Views
402
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom