Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009
    Posts
    1

    Unanswered: Access Query problem

    I will try to make this as brief as possible. I have an Access 2003 database used for tracking Standard Operating Procedures. The SOP compliance criteria is a certain period of time since the last review period (2 years). The SOPs are "owned" by ~20 different groups or crafts. I built a Compliant/Not Compliant by Craft report. The report data source is a total count master query that shows the total number compliant and not compliant for each craft

    To make this work, each group or craft has a compliance query with criteria that the time from effective date to now can not exceed 730 days. I built a total count query for each of these select queries (for each craft). Problem is in the total count master query, I can add up to 32 of the total count queries (remember 1 compliant and 1 not compliant count query for each craft) with a good result, but more than 32 count queries added to the master count query gives the "query is too complex" message. I removed spaces from the sub query names and reduced the size of those names as well to no avail. There must be a way to overcome this. In order for the report to be of value, I must itemize the compliant/not compliant numbers by craft. I hope I adequately described the mechanics of the total count master query and the underlying queries feeding it. I am not an expert, but consider myself to be proficient with Access even though I avoid sub forms and stick to the basics. Thanks for any suggestions for the "query is too complex" problem. - TByrd

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There are limits to the complexity of a query or of nested queries (Number of enforced relationships: 32 per table minus the number of indexes that are on the table for fields or combinations of fields that are not involved in relationships, Number of tables in a query: 32 etc... see: Microsoft Access Specifications and Limits | Database Solutions for Microsoft Access | databasedev.co.uk) and it's not only a question of length, so having shorter names won't help you here.

    If you have reached such a limit, one possible solution would be to use a temporary table where you could store intermediate values, then process those values with a final (or level 2) query

    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •