Results 1 to 11 of 11
  1. #1
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142

    Query is too complex to create report

    I am trying to create a report biased on a query that holds all the variables I need. The problem is that when I try to build that query I keep getting query is to complex error message. This might not be the best solution for the creation of this report.
    What I really need is a report that can be filtered by person and date and show the other 11 fields I need. I have tried to follow
    http://allenbrowne.com/ser-62.html

    and



    https://www.access-diva.com/r6.html

    but the code is beyond me right now. I am looking for something simpler to follow.

    The end result will be a report that has the individuals name and date they worked, as well as all the individual task information.
    task, trips, standard, reported time, exceptions, earned time, productive time, un-explained time, performance, utilization, and efficiency.
    All the individual queries to generate these calculations have been built, I just can't find a way to put them all together into one cohesive report.
    Attached Files Attached Files

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You've zipped up the lock file, not the actual db.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142
    Here you go. I have since my first post tried to build a report in the report wizard but it still wont work. I think the problem is the number of queries I have to have to get all the calculations I need. Maybe if I can figure out how to get my numbers with less queries it might work?
    Attached Files Attached Files

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The only report in the sample works fine. I don't know what you're trying to achieve so it's hard to guide you towards a solution. Tools at your disposal for more complicated reports include domain aggregate functions like DSum() and subreports based on your queries.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Hi Chris,
    I agree with Paul... we need some kind of drawing/ image/ sketch to know how/what you want the report to look like.

    So I'm still monitoring your posts. I looked at your current dB (Post #3) and I would have to say that you still have issues with the table designs and relationships.
    I fear you are going to be really frustrated trying to get valid results.

    I modified my example dB to get it closer to your dB kkms7.accdb. Even with my dB version, I do not think you can rely solely on queries - you will probably need to write some UDFs (UDF = user defined function).

    Good luck with your project..........
    Attached Files Attached Files

  6. #6
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142
    Here is the break down of what my end goal is for the reports. I will eventually need more but right now I have a big three that I have to implement.
    warehouse daily productivity report that can be filtered by shift and date.
    Click image for larger version. 

Name:	58.jpg 
Views:	26 
Size:	199.5 KB 
ID:	36090
    individual daily coaching reports filtered by associate and date. This is the associates day at a glance.
    Click image for larger version. 

Name:	59.jpg 
Views:	26 
Size:	181.5 KB 
ID:	36091
    Individual weekly summery report by associate name and date.
    Click image for larger version. 

Name:	60.jpg 
Views:	27 
Size:	110.8 KB 
ID:	36092

    Right now I am focusing on the coaching report since it has the most information on it. The top of the report consist mainly of associate info.
    Click image for larger version. 

Name:	49.jpg 
Views:	26 
Size:	54.0 KB 
ID:	36093
    This bar has the name, date, department, sub-department as well as the daily total numbers for some of the associates metrics that we track. I have built queries that give me all of these numbers. I can leave these numbers off this report if I have to. The main thing is the daily activities the associate performs.
    Click image for larger version. 

Name:	50.jpg 
Views:	28 
Size:	187.6 KB 
ID:	36094
    This is a daily task break down of everything the assocaite did on that date.
    Task, trips, task standard, job reported time, exceptions that happned on the job and so on.

  7. #7
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142
    And lastly The daily overall average for performance, Utilization, and Efficiency.
    Click image for larger version. 

Name:	51.jpg 
Views:	29 
Size:	33.8 KB 
ID:	36095

    I thought I could add all these query results into a separate query to generate the report.
    Click image for larger version. 

Name:	53.JPG 
Views:	28 
Size:	72.5 KB 
ID:	36096
    I take what I need and place it in the query.
    Click image for larger version. 

Name:	55.jpg 
Views:	28 
Size:	36.3 KB 
ID:	36097

    and of course get this.
    Click image for larger version. 

Name:	52.JPG 
Views:	28 
Size:	16.0 KB 
ID:	36098

    This is the report without any of the total daily numbers in it. What can I do to get all the query results into one report? Am I even doing the queries right?
    And what the the blue heck is a UDF?

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by chriswrcg View Post
    And what the the blue heck is a UDF?
    A UDF is a User Defined Function, ie a function you write.
    For instance, if you want the total exception time for a specific Associate, on a specific date, for a specific task, you could write a UDF to do that.
    Also see
    https://sfmagazine.com/post-entry/ma...ned-functions/
    https://www.fontstuff.com/vba/vbatut04.htm


    Quote Originally Posted by chriswrcg View Post
    Am I even doing the queries right?
    IMO, no, the queries are designed wrong. Mostly because you have "AssociateID" in the 4 main tables.
    And there is no way to know which task was performed on which day. Also, there is no way to tell which exceptions belong to which task.


    The images help a tiny bit, but can't tell where the data comes from and what calculations are performed.


    Apologies if I am wrong, but it appears that you did not start the database by designing it on paper/whiteboard/the window/etc first.
    A lot of people try converting an Excel spreadsheet to Access without going through a design process ( and yes, I was one or those, a long time ago).

  9. #9
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142
    A UDF is a User Defined Function, ie a function you write.
    For instance, if you want the total exception time for a specific Associate, on a specific date, for a specific task, you could write a UDF to do that.
    So, should I create all my needed calculations through the use of UDF's rather than building a query for them? From what I can see online creating a calculated task field in a query is creating a UDF. Is that right or wrong?

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by chriswrcg View Post
    So, should I create all my needed calculations through the use of UDF's rather than building a query for them?
    Hard to say. It depends on how involved it is to get the results you want to display into a query.

    Quote Originally Posted by chriswrcg View Post
    From what I can see online creating a calculated task field in a query is creating a UDF. Is that right or wrong?
    Wrong. A UDF is VBA code, a Function, that returns a value. You can call a UDF in a query or use it in a control on a form.


    And my apologies.... I just saw that I misspelled "WORK" - I had "WORD"
    It has been corrected (see attachment). At the same time, I changed a couple of things. I modified the after update code for cboSelectName control. I added a reset button to clear the associate info (on the main form). And I created an UDF to calc the total exception time by task, by date, by associate.

    I am using the UDF named "fnCalcExceptionTime" (in Module1) in the query "qryTest_UDF". If you look at the query in design view you will see that the table "jnctTasksExceptions" is not in the query, but the total exception time by task is displayed. You can verify the total exception time by selecting Ron Banta, clicking on each task (5) in the task sub form and looking at the footer of the Exception sub form.
    Attached Files Attached Files

  11. #11
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142
    This is great!! and at the same time completely terrifying. I looked at module 1 and am months away from understanding how to create that. I get the locations part of it and where you are pulling the data from but the syntax for actually performing the calculations are beyond me. Do you recommend any books on access vba code that I can start getting into?

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

Similar Threads

  1. Replies: 1
    Last Post: 10-08-2015, 10:59 AM
  2. Report wizard won't work using complex query
    By jzacharias in forum Reports
    Replies: 1
    Last Post: 09-20-2015, 10:03 AM
  3. Replies: 7
    Last Post: 01-16-2014, 09:17 AM
  4. Sub report of a complex query/report
    By roar58 in forum Reports
    Replies: 1
    Last Post: 03-11-2012, 08:41 PM
  5. Complex Query/Queries for a Report
    By Rawb in forum Queries
    Replies: 3
    Last Post: 02-04-2010, 07:44 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