Results 1 to 7 of 7
  1. #1
    DyngoDawg is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2021
    Posts
    3

    Arrow Repeating data on report with 2 subforms

    Hello - first let me say i am NOT a professional. I am learning as i go.

    I have a report that needs to show the person and the total time spent and the detail records created during total time spent.

    The person (id# name etc) are in a table.
    The times spent on the form are in a separate Table - personid# , start time, end time and the auto increment id#.
    The detail records are a separate table, and are a tracking sheet for work performed and most of the fields autopopulate, updated fields are , case#, and status -

    I need the report to show

    Mickey Mouse 4:00 (hours /min - totaled from the time log table with individual start stop times )

    12345 upload
    34544 problem
    33445 upload


    What i see is that there are 4 entries in the time log for 1:00 each - so when i get the total of 4:00 that is correct but the detail records below are repeating 4 x - once for each time log entry.


    How do i get the total time to display with out causing a repeat in the detail records ?



    Problem resolves if i put all the subreports in a header section except that the subsequent person on the report does not have their time totaled. Only the very first person on the report shows the time. I need to be able to show everyone that did the work, their total time spent , and their detail (supporting ) records.

    How do i approach this to achieve the results i require? Maybe 2 sub reports is not the way to do it ... Maybe i need VBA code ... DEFINITELY i need some helpful expertise!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You mean a report with 2 sub reports? I'm not seeing a need for 2 sub reports but maybe I am not envisioning what you really have. Your main report would be the person details; the sub report the detail records with the time (sum?) for each record. In the sub report you'd group by person ID and total on the calculated field (or total one value, total another value, then in the totals textbox, calculate the sum of A minus the sum of B. If you really have special characters in any names (e.g. #) you really shouldn't. Basically, underscore (_) is the only exception. In the design menu there is a Group and Sort button on the ribbon if you need to turn that on for your report.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    DyngoDawg is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2021
    Posts
    3
    yes and no!

    I have a form that folks fill out ... it consists of name, date, folder date then the detail records (seperate table) that show the case, and status. I do use a time start time stop in the detail records, but I also have a form start form stop in a separate table.

    I am attempting to record the time they open the form and the time they close the form - because ... a worker may do several tasks thru out the day, start on this body of work - work 2 hours then switch to a high priority work for 3 hours then come back to the original body of work later in the day and spend another 2 hours on it. The individual record times are more of an indicator (average time per doc ) than a total time spent. I need to be able to say ... out of an 8 hour day .. this employee spent 4 hours doing this body of work.

    I have an initial form that they start their day with. and IF they only work it once ... everything is peachy. but the nature of the business is small amounts of time spent here and there through out the day ... so i need to consolidate all of these spurts into 1 grand total. Management will not allow a password, nor do they want folks using the arrow keys (navigation bar ) to cycle thru to find their record for the day (not want employees seeing eachothers day...) so i have a query that goes out and isolates their record for the day so they can continue where they left off. This generates a new start / stop time for the form ... so i need to be able to total all the FORM times to a grand total to display on the report . only 1 main record, several times and MANY detail records.

    The detail records have 1 line for each document processed and the subreport displays properly if i do not include the form time... once i add the total time to the report the details appear multiplied by the number of times the form was started/stopped.

    I hope I am describing this adequately !!! I am open to suggestions on HOW to achieve the goal... based on the constraints i have to work with

    The times are never meant to be seen by the employee doing the work. The form time vs the record time, will help management weed out those that open the form ... leave it open on their desktop and do 2 enteries in an 8 hour period, the record times also indicate when i record took more research time to complete. The average record can be completed in under a minute, some can take 3 minutes.. the form time shows the time the employee was ON the TASK. The record time is a detail that helps us determine the average amount of records that CAN be processed in a typical 8 hour day.

    The current tracking system is using an excel spreadsheet - that is emailed to management - that is given to me to enter in a database ... with the spread sheet the employee only needs to say 6 hours.

    I am attempting to create an interface to the database that the employee tracks directly to .. the time stamps are a convenience to the employee cuz they don't have to "track" their time by watching a clock AND a necessity to management as it helps determine if the employee is inflating their time spent and processing less documents than they should be for the time spent.... and eliminates the duplicity of work - creating spreadsheet, emailing 2x , data entry...
    Last edited by DyngoDawg; 03-16-2021 at 03:48 PM. Reason: additional info

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm a bit confused for a few reasons. One is that your post is about reports but now seems to be about getting the data in to the records via forms. The other is that I'm trying to keep my mind occupied, but for personal reasons I'm not able to tackle more than basic issues just right now. I have no doubt someone will jump in and digest all that and take it from here. People are very helpful here.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    DyngoDawg is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2021
    Posts
    3
    sorry .. the forms are how i am gathering the data i am trying to display IN the REPORT. The individual tables update properly from the forms... My problem lies in how to display it in the REPORT.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Any chance you would post an example of your dB for analysis?

  7. #7
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I remember your db from another place. Create a totals query and join tblPerson to tblLog and Group By PersonID, Person Name, WorkDate and use TotalTime:Sum([TimeEnd]-[TimeStart]) with Expression in the Totals row. Save this query then create another one where you bring the details from tblDetails and use that as the source of the report, no subreport needed. Or base the main report on the first query and create a subreport bound to tblDetalis and link by PersonID\WorkDate.

    Providing a sample would make it much easier to get help!

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Main Report and Subreports repeating data
    By PinkLady50 in forum Reports
    Replies: 17
    Last Post: 09-15-2017, 09:37 AM
  2. Replies: 5
    Last Post: 12-12-2016, 07:23 AM
  3. Replies: 1
    Last Post: 11-23-2015, 12:11 PM
  4. repeating details in report
    By msnarayanan in forum Reports
    Replies: 2
    Last Post: 09-25-2015, 08:41 AM
  5. Replies: 2
    Last Post: 10-14-2013, 04:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums