• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

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

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.

3 Solutions
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.

Give an example database with 3 tables: 2, 3,  and 4 fields. Assume the limit is 5 fields.
Explain the required process.
Jeffrey CoachmanCommented:
<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?


Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

BevosAuthor Commented:
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.

BevosAuthor Commented:
Needed to approach by another solution. The experts offered advice as best they could for a flawed approach by me.
Jeffrey CoachmanCommented:

There are many threads here outlining different approaches to this.


Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now