Query Field Limit Reached in Access, any way to work around this?

Posted on 2011-05-04
Last Modified: 2012-05-11
Hello everyone, I have a question about Access that I'm not certain there is a solution for but I thought I'd ask.  I have a query which I want to include all the fields in my database so that users can select the appropriate fields/filters from a form (list box w/ multiple selections and then series of combo box filters) and then export these choices to either Word or Excel.  This form works great except for one thing, some databases where I use it have too many fields to allow this.  The source for this form is the query mentioned above.

Do any of you experts know any tricks (maybe using unions of multiple queries) to get around this type of limitation? Or do you have suggestions for how to better this export process?  Basically I don't want to have to write a specific query for each request (because there are many) and I thought this was a good solution.

Question by:Bevos
    LVL 12

    Accepted Solution

    Yes, you might go for an UNION query, make sure you use the UNION ALL command, as for UNION query are known to be slow in performance.

    LVL 30

    Assisted Solution

    Give an example database with 3 tables: 2, 3,  and 4 fields. Assume the limit is 5 fields.
    Explain the required process.
    LVL 74

    Assisted Solution

    by:Jeffrey Coachman
    <I want to include all the fields in my database so that users can select the appropriate fields/filters from a form>

    If I am reading your question correctly, this design approach is too broad, IMHO.

    Typically you will only feed the needed fields to a form for selection, not *every* field in the entire database...

    For example, if you will be filtering for certain Orders, why would you need the employee DateOfBirth field available?

    In other words, whats the need to list every field in the DB for criteria selection, instead of just the needed fields for the specific query?

    If you are trying to create some sort of "universal" filtering system to allow for filtering of any set of data, then you are opening up a can of worms with this approach...

    I have never seen this approach taken in any database, what is your reference for this design approach?



    Author Comment

    Hi Jeff, my reference for this design is my non-normalized database addled mind.  I think I've found a solution (with the help of some comments from you and other experts in other threads).  Basically I'm going to design the list selection based on the output tables that the researchers want.  So I'll have several selection tables with filters.  This will work much more easily I think.


    Author Closing Comment

    Needed to approach by another solution. The experts offered advice as best they could for a flawed approach by me.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    There are many threads here outlining different approaches to this.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now