Need help with average records per hour desperately. (1 Viewer)

[jmonica]

HI, I am in dire need of help. I attempted to do this myself and I just can't wrap my brain around it. Can someone please help me? I would be very grateful.
I am trying to display on a form the average number of lines/records per hour that a person is adding to the database. After hearing some ideas from people and trying to get it work myself I've come to realize that I suck at this. I thought I had a handle on it and I kind of did 4 years ago, when I created the inventory program that we use, but I don't know what to do. What I though I'd do was this.
Trying to work with Dfirst and DLast I'd subtract the time of the most recent record from the time of the first record entered and then divide the number of records to display the average per hour in a text box. I have NO idea how to do this or even where to place the VBA code. Can some please help me with this code and where to place it? I really, really need help here. Thanks VERY much for any help that you can provide. I really do appreciate it. :banghead:

 

[plog]

Can you post some sample data of your data and then what you expect as the result based on that sample data? Be sure to include table and field names.

 

[jmonica]

Can you post some sample data of your data and then what you expect as the result based on that sample data? Be sure to include table and field names.

HI, Thanks for your reply. Honestly I'm not sure what to post. Basically I have a form (names frmMain) that does a lookup from a table called tblItem_master. An item is selected from that table, the details of which are pasted into a separate table along with things like the Quantity counted, the persons name and a date/time column. This table is called tblPhysical_Count.

What I want to do is have a box on the form frmMain that displays how many lines per hour or minute on average they are adding records. I think if I take the time that they enter their first item, subtract that from the time that they counted the most recent item and then do the math to divide the total number of records there are in the tblPhysical_Count table, and display that it should work no? It doesn't sound that complicated. I just don't know how to do it because I'm an idiot~!!!! :banghead:
As of now I have a simple text box on the form that shows the total number of records. This is done by using =count[LTID] (LTID is an auto number column so every record has to have this filled populated, thus equaling the number of records counted) in the control source of a text box. I hope that makes sense? Does this help explain what I'm going for? Thank You again!

 

[plog]

Yeah, I'm having a hard time visualizing it. I would need to see what your table that you need to pull data from looks like with sample data in it. Draw me a picture from start to finish and connect the dots using data, not words.

 

[dmcdivitt]

It is common practice to include columns in a table containing user ID and timestamp. These are easy to implement on a bound access form by adding hidden fields and setting their value with beforeinsert and beforeupdate events.

With this information in the database a report can be made listing the count of records for each user ID between any two periods of time.

 

[jmonica]

It is common practice to include columns in a table containing user ID and timestamp. These are easy to implement on a bound access form by adding hidden fields and setting their value with beforeinsert and beforeupdate events.

Thanks I know. I have those columns in my table as I have described above.

With this information in the database a report can be made listing the count of records for each user ID between any two periods of time.

Thanks again. I know it can be done. I just don't know how to do it. I don't want to create and run a report. That, I can do myself. Never mind. I'll find someone, somewhere else who can understand the point of what I'm trying to do. It's simple. A box that shows the average number of records per hour based on the time between the first record and subsequent records. Not rocket science to understand. Example: If someone worked from 8:00 am to 11:00 am and added 300 lines to the Db there would be a box on the form (the only form I mentioned) showing that they are averaging 100 lines per hour. Thanks anyway but I'll try another forum.

 

[CazB]

Try something like this... obviously substitute your own fieldnames!

PARAMETERS [Start Date/Time?] DateTime, [End Date/Time?] DateTime;
SELECT mytable.userID, Count(mytable.recordID) AS NewRecords, ([End Date/Time?]-[Start Date/Time?])*24 AS Hours, [End Date/Time?] AS [End], [Start Date/Time?] AS [Start], Count([recordID])/[Hours] AS [Per Hour]
FROM mytable
WHERE (((mytable.timestamp) Between [Start date/Time?] And [End Date/Time?]))
GROUP BY mytable.timestamp, [End Date/Time?], [Start Date/Time?];

 

[jmonica]

Try something like this... obviously substitute your own fieldnames!

PARAMETERS [Start Date/Time?] DateTime, [End Date/Time?] DateTime;
SELECT mytable.userID, Count(mytable.recordID) AS NewRecords, ([End Date/Time?]-[Start Date/Time?])*24 AS Hours, [End Date/Time?] AS [End], [Start Date/Time?] AS [Start], Count([recordID])/[Hours] AS [Per Hour]
FROM mytable
WHERE (((mytable.timestamp) Between [Start date/Time?] And [End Date/Time?]))
GROUP BY mytable.timestamp, [End Date/Time?], [Start Date/Time?];

Thank You! I am sure this will get me started. I appreciate your help very much and your time.

 

[CazB]

no problem - on re-reading your question I know it's probably not 100% what you were after but hopefully it'll give you a shove in the right direction

 

[jmonica]

Thanks again. I wish I had never included the words User ID as that has nothing to do with what I need. I just simply need to know how many records have been entered into the database and then average it per hour. I never thought I'd have this much trouble given what I was able to do with the program a long time ago. This has got me in a tough spot though. I'm nearing the end of the allotted time I've been given to do this and to have nothing at the end to show for it would be bad for me. It's just very stressful. Thank You again for trying to help though! I'll figure something out.

If I could just calculate how much time has elapsed between the first record entered and the current record entered, I'd be 95% of the way home. That's why I thought DFIRST and DLAST were the way to go.

 

[CazB]

ok, so to clarify:

are you looking at ALL the records in the table 'ever', or just the ones between a certain start and end time?

 

[jmonica]

All of the records. The database is only used for one day, then the data is pulled and it starts from a blank the next time it is used. I have a date/time column so that each entry gets stamped. Table and column are physical_count.dttm_counted.

 

[CazB]

Even Simpler then

SELECT Count(mytable.somefieldorother) AS NewRecords, Min(mytable.timestamp) AS Start, Now() AS [End], ([End]-[Start])*24 AS Hours, Count([somefieldorother])/[Hours] AS [Per Hour]
FROM mytable;

OR

SELECT Count(mytable.somefieldorother) AS NewRecords, Min(mytable.timestamp) AS Start, Max(mytable.timestamp) AS [End], ([End]-[Start])*24 AS Hours, Count([somefieldorother])/[Hours] AS [Per Hour]
FROM mytable;

You don't NEED to show the start and end etc but they're useful to check what it's doing

Oh, and it may be worth while setting the field properties on 'Per Hour' to Fixed with a set number of decimals

 

[jmonica]

Ahh Thank You! I am going to try this now, but I'm confident now that I found someone who understands what I need and knows what they are doing such as yourself, that it's going to work. I knew it wasn't all that confusing because there are not a lot of variables for lack of a better word. It's just that I'm a moron (obviously). I will report back as soon as I give this a try. I cannot thank you enough for your time and help. I am a member of a few forums where I spend a few hours a night helping people with other things that I know a little more about. For me its satisfying to know that I helped someone in dire need. I hope you know that you just did that for me! Much appreciated!

 

[jmonica]

Even Simpler then

SELECT Count(mytable.somefieldorother) AS NewRecords, Min(mytable.timestamp) AS Start, Now() AS [End], ([End]-[Start])*24 AS Hours, Count([somefieldorother])/[Hours] AS [Per Hour]
FROM mytable;

OR

SELECT Count(mytable.somefieldorother) AS NewRecords, Min(mytable.timestamp) AS Start, Max(mytable.timestamp) AS [End], ([End]-[Start])*24 AS Hours, Count([somefieldorother])/[Hours] AS [Per Hour]
FROM mytable;

You don't NEED to show the start and end etc but they're useful to check what it's doing

Oh, and it may be worth while setting the field properties on 'Per Hour' to Fixed with a set number of decimals

oh MAN, I AM NOT HAVING A GODO DAY. I can't figure out what to do with this code. I have plugged in all of the correct table an field names and I thought I would just add it to the onclick event for testing so that when I clicked the text box, it would give me the results, but nope. I am all screwed up. Heres what I have but I keep getting a syntax error. saying its expecting a Case statement after the word "count". Can you tell me where I need to place this code, PLEASE? Thank You!

SELECT Count(Physical_Count.LTID) AS NewRecords, Min(Physical_Count.DTTMCOUNTED) AS Start, Max(Physical_Count.DTTMCOUNTED) AS [End], ([End]-[Start])*24 AS Hours, Count([LTID])/[Hours] AS [Per Hour] FROM physical_ count;

 

[jmonica]

I guess You can't use a select query to update a text box. The code works when run as a select query, but I can't figure out how to make it run and display in the text box and update after every new record is added. I thought I had it there for a minute.

 

[jmonica]

Finally! I was able to use the code you provided in a select query then use DLookup to call the query as the control source for the text box. It appears to be working. I could not have done it without you. Thanks my friend! You saved my A$$ today! And possibly my job! Thank You again CazB!