Summarizing Data for Reports

[DaveMac]

I have a table that has the following fields:

Key_ID ’ Just an auto number field
Event_Counter ’ A Counter located in a logic controller of a machine
Total_Scrap ’ Running total of scrap produced by this machine
Date_Time ’ Date and time event was logged
Reason_Code ’ A code entered by the operator for scrapping product
Machine_Number ’ Machine number 1 through 4
Shift ’ 1st 2nd or 3rd

The data would look something like this:

Key_ID,Event_Counter,Total_Scrap,Date_Time,Reason_Code,Machine_Number,Shift

54 8789   11548  1/1/01 17:54:02 901 1 1
55 488787 9987   1/1/01 17:54:02 500 2 1
56 32	 774787 1/1/01 17:54:02 500 3 1
57 9977   24     1/1/01 17:54:02 905 4 1
58 8789   11588  1/1/01 17:55:02 901 1 1
59 488788 9989   1/1/01 17:55:02 500 2 1
60 32     774799 1/1/01 17:55:02 500 3 1
61 9977   898    1/1/01 17:55:02 905 4 1
62 8789   11602  1/1/01 17:56:02 901 1 1
63 488788 99907  1/1/01 17:56:02 500 2 1
64 32     774888 1/1/01 17:56:02 500 3 1
65 9977   1650   1/1/01 17:56:02 905 4 1
66 8790   11650  1/1/01 17:57:02 901 1 1
67 488788 99907  1/1/01 17:57:02 500 2 1

I created a sample that has groupings of Shift, Machine, Event_counter so I have a detail that is not shown but is by Event_Counter. I need to take the First record within the detail and do a datediff from the last record. Min and Max give me errors because sometimes the Program logging the data gives a 0. Can this be done in VBA? I would like to practice my VBA but am not sure if that is the way to go. I would use the same concept to do many other First record and last record calculations(Scrap).

Thanks!

 

[WildHare]

You could probably create a function to open a clone recordset and use MoveFirst and MoveLast to grab the two date/time guys. Have the function pass back the DateDiff in seconds or minutes or whatever, and you should be good to go.

Just remember to create your clone with the exact same criteria and/or sorting order so that you get a duplicate of the recordset being used in the report.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[DaveMac]

Jim,
I see acdetail, acrecord and acfirst-aclast. I wonder how would you structure code that said something like dim the detail as a rs and then used the acfirst and aclast. I am not fimiliar with how to do that but sounds like something useful. I would put these controls in each of the 4 grouping levels I have. Work_Date, Shift, Machine, and Event.


Thanks again,
Dave