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

    simple query returning query is to complex error message

    I am beginning to think that access is more trouble than it is worth. I wanted to find a simpler way to enter and report on my data other than excel but I am getting problem after problem and error after error.
    This is the report I will eventually try and create. This is it in excel.


    Click image for larger version. 

Name:	coaching sheet.JPG 
Views:	33 
Size:	160.9 KB 
ID:	35816
    The individual tells me what they did, how long they did it, and how many trips they could get in the time they did the work.
    Right now I am trying to build the individual queries I need for the report and am down to the efficiency one. I am getting query is to complex even though it is a simple calculation. EFF1: [Per1]*[utl1] efficiency = performance times utilization.
    does access "look back down the chain" or something. I have had to create 4 or 5 other queries to get the performance % and Utilization % I need for this efficiency calculation. This is really getting frustrating.

  2. #2
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Well, *too complex* is Access saying *can't figure out how to do this.* Here are some reasons for this...
    - problem with data types or with NULL fields on records
    - problem with Name Autocorrect (if it's checked un-check it)
    - use of reserved words (as field/table/parameter/alias names) making the query hard to interpret.

    It would help to see the entire SQL of the query. If the query is based on a another query, post the lower one as well.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It would also help to see your dB and the Excel spreadsheet.

  4. #4
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142
    I have normalized it as best I can but I can take any guidance you can provide. I think one of my problems is that in my Individual Earned Time [INDVEarnedT] query none of the task trips or standards are shown default 0 even though I have set all the default task trips and standard fields to 0 in my AssociateTask Table. I am not sure why.
    Attached Files Attached Files

  5. #5
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Hmm, well this is not normalized but no way to *properly* aide you without knowing what everything is. What are tables Exceptions 1 and 2 hold and what are those initials?

  6. #6
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142
    ok...exceptions are anything that happen to you during your day that are not part of your process that you have to stop and take care of. So lets say that you are putting away product in a warehouse. Your process is 1.pick it up 2.scan it. 3. move it to location. 4. place product in location 5. scan product into location. 6. return to dock. repeat.

    During this process you are sent to a location that can not hold the material physically even though the system says it can. Now you must stop the put away process and go find inventory control and have them help you find a good location to put the material in. This is an exception and must me timed.

    The code is simply a fast way of letting me know what happened. BI (Bin Issue) takes up less space on the sheet than having to tell me "the bin was full and I had to go and find someone to help." The initials or codes are really irrelevant.

    I am interested in the time it took to solve the problem. I need so many of them per activity because experience has shown that more than one problem can occur during the task. I capped the individual task exception limit to 12 (the most I ever say anyone claim)

    For my database I need
    1. each individual exception and how long it took (data entry)
    2. add up each time for the individual task exception time ( queries TotalEXC1 through TotalEXC15)
    3. the total exception time for the entire day. (query TotalEXC0)

    This happens through out my database. I need query's for individual task and total task for the day. I know everything looks all jumbled up but this is the only way I can figure out how to get the information in and the reports I need out. I just don't know why it can't do a simple multiplication calculation. performance times utilization.

  7. #7
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Thanks that helped.

    Exceptions belong in a Look-up Table not as Field Names. You are trying to set-up Excel in Access the way Excel works but Access does not work that way. Your tables should look more like...

    tblAssociates
    aAssociateID (PK)
    aFirstName
    aLastName
    etc...

    tblAssociateActivities
    aaAssociateActivitiesID (PK)
    aaDate
    aaTimeIn
    aaTimeOut
    aaExceptionID (FK to tlkpExceptions PK)
    etc...

    tlkpExceptions
    eExceptionID (PK)
    eAbbreviation
    eDescription
    eActive

    In Access tables are long not wide. Wide is reserved for queries.

  8. #8
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142
    I dont understand. can you make a small sample DB or point me to something like I need? Will this help me with my query's?

  9. #9
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Yes, it will help with your queries. It will make the workable because Access does mother down not across.

    I, like others here, volunteer our time so, sorry but no, can't make you a quick sample. You need to take the sample file you uploaded and rework the tables. Then we can help you work on queries, forms and reports. Oh, and another thought there are many sites that have free samples. Do a search on time management ms access, which is basically what you have, and you might find a sample file!

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

Similar Threads

  1. "Too complex query" for a Simple Select Query
    By dragon232 in forum Queries
    Replies: 3
    Last Post: 11-29-2016, 02:45 PM
  2. IIF Returning #error Message
    By Tlo in forum Queries
    Replies: 8
    Last Post: 01-31-2016, 08:12 AM
  3. Expression is Too Complex Error Message
    By EddieN1 in forum Reports
    Replies: 1
    Last Post: 05-29-2013, 11:16 PM
  4. Replies: 2
    Last Post: 05-25-2013, 09:16 PM
  5. Simple Query not returning any data.
    By psoli in forum Queries
    Replies: 4
    Last Post: 06-16-2011, 11:07 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