Convert Columns to Rows using Union Query Error "Query is too complex"

Question

I am using a union all query to convert multiple columns to rows. When I exceed 49 rows of SQL statement, I am getting an error "Query too complex". I need total of about 90 lines (3 months) of SQL statement to complete my transpose, the work around I am employing it to split the query into two and then union again. Is there a smarter way of doing it? This is the SQL statement I am using.

SELECT Q_RE06_BUUN.[Activity ID], Q_RE06_BUUN.[Resource ID Name], [Q_RE06_BUUN]![Wed 3-1] AS [Budgeted Units], 43313 AS [Date], "Plan" AS Type FROM Q_RE06_BUUN;

UNION ALL SELECT Q_RE06_BUUN.[Activity ID], Q_RE06_BUUN.[Resource ID Name], [Q_RE06_BUUN]![Thr 3-2] AS [Budgeted Units], 43314 AS [Date], "Plan" AS Type FROM Q_RE06_BUUN;

UNION ALL SELECT Q_RE06_BUUN.[Activity ID], Q_RE06_BUUN.[Resource ID Name], [Q_RE06_BUUN]![Fri 3-3] AS [Budgeted Units], 43315 AS [Date], "Plan" AS Type FROM Q_RE06_BUUN;

UNION ALL SELECT Q_RE06_BUUN.[Activity ID], Q_RE06_BUUN.[Resource ID Name], [Q_RE06_BUUN]![Sat 3-4] AS [Budgeted Units], 43316 AS [Date], "Plan" AS Type FROM Q_RE06_BUUN;

UNION ALL SELECT Q_RE06_BUUN.[Activity ID], Q_RE06_BUUN.[Resource ID Name], [Q_RE06_BUUN]![Sun 3-5] AS [Budgeted Units], 43317 AS [Date], "Plan" AS Type FROM Q_RE06_BUUN;

UNION ALL SELECT Q_RE06_BUUN.[Activity ID], Q_RE06_BUUN.[Resource ID Name], [Q_RE06_BUUN]![Mon 3-6] AS [Budgeted Units], 43318 AS [Date], "Plan" AS Type FROM Q_RE06_BUUN;

UNION ALL SELECT Q_RE06_BUUN.[Activity ID], Q_RE06_BUUN.[Resource ID Name], [Q_RE06_BUUN]![Tue 3-7] AS [Budgeted Units], 43319 AS [Date], "Plan" AS Type FROM Q_RE06_BUUN;

UNION ALL SELECT Q_RE06_BUUN.[Activity ID], Q_RE06_BUUN.[Resource ID Name], [Q_RE06_BUUN]![Wed 3-8] AS [Budgeted Units], 43320 AS [Date], "Plan" AS Type FROM Q_RE06_BUUN;

UNION ALL SELECT Q_RE06_BUUN.[Activity ID], Q_RE06_BUUN.[Resource ID Name], [Q_RE06_BUUN]![Thr 3-9] AS [Budgeted Units], 43321 AS [Date], "Plan" AS Type FROM Q_RE06_BUUN;

UNION ALL SELECT Q_RE06_BUUN.[Activity ID], Q_RE06_BUUN.[Resource ID Name], [Q_RE06_BUUN]![Fri 3-10] AS [Budgeted Units], 43322 AS [Date], "Plan" AS Type FROM Q_RE06_BUUN;

Answer 1

For examples of how to correct a badly designed table which encodes data as column headings by means of VBA, take a look at UnecodeColumns.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

This little demo file includes two examples of how to recast such tables, one where the columns' values are Boolean (Yes/No) values, the other where they are quantitative values.

Answer 2

"Date" is a Reserved Word and should not be used for a column name.

If this is a one-time thing, you can use VBA code to move the data one column at a time, using recordsets. This denormalized db design is going to continue to give you grief, until it is redesigned.

Hello Tom,

Noted your recommendation on "Date". Will explore the use of VBA. Thank you for your feedback.

Clement

Answer 3

"Date" is a Reserved Word and should not be used for a column name.

If this is a one-time thing, you can use VBA code to move the data one column at a time, using recordsets. This denormalized db design is going to continue to give you grief, until it is redesigned.

Answer 4

Create and save nine union queries with ten queries each.

Then create a union query where you union the nine saved union queries.

And do use date values for dates, not fancy numbers, or the queries will not return date values:

#2018/08/01# AS [Date]

or:

DateSerial(2018, 8, 1) AS [Date]

Also, read up on normalising a databaseto prevent the need for nasty queries like this.