CODEWITHSUNDEEP
ms-accessjoinms-access-2007ms-access-2010union
George Hadley
George Hadley

Reputation: 103

Microsoft Access Queries Too Many Fields

I am dealing with approximately one dozen MS access tables that are all linked into a single database file.

Occasionally I would like to be able to get the union of these tables to generate a complete dataset. My attempts to do this so far result in a too many fields defined error.

Is there a way for me to do this using Microsoft access (I can use 2007 or 2010 versions)? If not, any other suggestions on how this could be achieved would be greatly appreciated.

Upvotes: 3

Views: 5445

Answers (2)

NewSites
NewSites

Reputation: 1739

This answer is about a different occurrence of the "Too many fields defined" error from a query, and what I did to eliminate it.

I have a query that was giving me that error. Compacting the database didn't fix it. When I split the query into three queries with fewer fields in each, two of those smaller queries worked okay, but one still gave me the error. So it appeared that the problem had nothing to do with the number of fields, but with something about the coding of the query.

The query had two similar fields coded as subqueries:

(SELECT first([Date]) FROM [C:<path>\sources.accdb].[items - subjects] WHERE [Subject name] = SubjName and Assessment like "*Buy*") AS FirstBuyRecDate

and

(SELECT first([Date]) FROM [C:<path>\sources.accdb].[items - subjects] WHERE [Subject name] = SubjName and Assessment like "*Sell*") AS FirstSellRecDate

The error first arose when the second of those fields was added to the query. The query was able to work with either of those fields present, but not both. It was also able to work if the subquery in either of the fields was replaced with a random date constant, such as #2018 12 31#. So it appeared that the problem had something to do with those subqueries.

I was able to eliminate the error by changing those subqueries to function calls using a UDF I had previously developed for another purpose:

Public Function vFtnInQry(sField As String, sSQL As String) As Variant
' Return the value of the field with the name <sField> in the select query <sSQL>.
' The purpose of this is to allow inserting a variable value into the SQL of a query.

Dim oRecSet As DAO.Recordset, nCountRecords As Long, vRetVal As Variant

Set oRecSet = CurrentDb().OpenRecordset(sSQL)
nCountRecords = oRecSet.RecordCount
If (nCountRecords = 0) Then
    vRetVal = Null
  Else
    vRetVal = oRecSet.Fields(sField).Value
  End If

vFtnInQry = vRetVal

End Function

So the field definitions changed to:

vFtnInQry("BuyRecDate", "SELECT first([Date]) as BuyRecDate FROM [C:<path>\sources.accdb].[items - subjects] WHERE [Subject name] = """ & [SubjName] & """ and Assessment like ""*Buy*""")) AS FirstBuyRecDate

and

vFtnInQry("SellRecDate", "SELECT first([Date]) as SellRecDate FROM [C:<path>\sources.accdb].[items - subjects] WHERE [Subject name] = """ & [SubjName] & """ and Assessment like ""*Sell*""")) AS FirstSellRecDate

I don't know why this solved the problem. I'm posting it here in case someone else has a mysterious occurrence of "Too many fields defined" from a query that might be solved this way. On the other hand, if someone reading this sees a reason that those subqueries were causing that error, please post about that in a comment or another answer.

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123799

According to the "QUERY" section of the "Access 2010 Specifications" topic in Access help, you are limited to 255 fields (columns) per recordset (query). If the "complete dataset" you want to produce will have more than 255 columns then you won't be able to do that in a single Access query.

One possibility might be to create several queries with no more than 255 columns each, export them to Excel, then paste them together side-by-side into a very wide Excel document. Excel_2010 worksheets can have up to 16,384 columns. It could be a bit tedious to do manually but there would be some opportunity to automate the process, at least in part. You'd also have to be careful that each "partial query" returned the same number of rows in the same order so things would "line up" when you pasted them together.

Upvotes: 3

Related Questions