Cannot run Union query

[azhian]

Hi all,

I have created a union query which joins together multiple queries into one. It was working fine until I added more than 6 queries. Now I am getting an error message saying "Could not start transaction; too many transactions already nested." I looked this up and the description said that I could not nest a transaction more than 5 levels deep. This should only be a single level deep. I tried to create union queries (from only 6 tables each) and then joined those together and it worked fine but not if I try to join them using a single union query. Additionally, I looked up the maximum number of tables allowed and 6 is well below the limit of 32 (what I found). I was wondering, is this issue because one of the fields I have included is a multivalue lookup field?

Here's what my SQL code looks like.

SELECT [210-XXXX-XXX].[Product Code], [210-XXXX-XXX].DwgNo, [210-XXXX-XXX].Title, [210-XXXX-XXX].Rev
FROM [210-XXXX-XXX]
WHERE ((([210-XXXX-XXX].[Product Code])=[Forms]![Open PRF].[Product Code]))
UNION
SELECT [270-XXXX-XXX].[Product Code], [270-XXXX-XXX].DwgNo, [270-XXXX-XXX].Title, [270-XXXX-XXX].Rev
FROM [270-XXXX-XXX]
WHERE ((([270-XXXX-XXX].[Product Code])=[Forms]![Open PRF].[Product Code]))
UNION
SELECT [7400-XXXX].[Product Code].Value, [7400-XXXX].DwgNo, [7400-XXXX].Title, [7400-XXXX].Rev
FROM [7400-XXXX]
WHERE ((([7400-XXXX].[Product Code].Value)=[Forms]![Open PRF].[Product Code]))
UNION
SELECT [950-XXXX].[Product Code].Value, [950-XXXX].DwgNo, [950-XXXX].Title, [950-XXXX].Rev
FROM [950-XXXX]
WHERE ((([950-XXXX].[Product Code].Value)=[Forms]![Open PRF].[Product Code]))
UNION
SELECT [951-XXXX].[Product Code].Value, [951-XXXX].DwgNo, [951-XXXX].Title, [951-XXXX].Rev
FROM [951-XXXX]
WHERE ((([951-XXXX].[Product Code].Value)=[Forms]![Open PRF].[Product Code]))
UNION
SELECT [ALL PRODUCTS].[Product Code], [ALL PRODUCTS].[PRF Document], [ALL PRODUCTS].[PRF Description], [ALL PRODUCTS].[PRF Rev]
FROM [ALL PRODUCTS]
WHERE ((([ALL PRODUCTS].[Product Code])=[Forms]![Open PRF].[Product Code]))
UNION SELECT [ALL PRODUCTS].[Product Code], [ALL PRODUCTS].[ISR Document], [ALL PRODUCTS].[ISR Description], [ALL PRODUCTS].[ISR Rev]
FROM [ALL PRODUCTS]
WHERE ((([ALL PRODUCTS].[Product Code])=[Forms]![Open PRF].[Product Code]));

When it's written like this, it doesn't work right but if I delete any one query (or table) from the group, then it works fine. Is there a way to make a single union query without having to make the intermediate union queries? Appreciate any help that can be provided.

 

[plog]

This sounds like a structure issue. How come you have so many tables/queries with the same structure?

 

[azhian]

Each different table is for a specific line of products. I set it up in a way so I can use the data and have it linked to visio, which is essentially the output of the data. They do all use the same structure but I separated them so I can run queries in the future more easily.

 

[CJ_London]

I was wondering, is this issue because one of the fields I have included is a multivalue lookup field?

quite possibly, although I would have thought that would have occurred when you start to refer to them around the 3rd union.

is DwgNo numeric and [PRF Document] text? if so then that is your problem - the first query determines the type

 

[azhian]

quite possibly, although I would have thought that would have occurred when you start to refer to them around the 3rd union.

is DwgNo numeric and [PRF Document] text? if so then that is your problem - the first query determines the type

No, they are both short text. If they were different types, wouldn't that cause the union query not to work even if it was less than six tables?

 

[CJ_London]

no, because the first time you use [PRF Document] is in the 6th union query

 

[azhian]

no, because the first time you use [PRF Document] is in the 6th union query

Oh, I see. They are both short text but the PRF is a calculated field (not sure if that matters). I tried to move it around so PRF was called up first but it seemed to give the same issue. I'm guessing I have no choice but to make some intermediate queries then huh?

 

[CJ_London]

Just for the fun of it to check the 6th query issue, have you tried removing the 6th query to see if the 7th (which will become 6) will run or duplicate query 5 (which you know works) as query 6?

 

[Galaxiom]

Each different table is for a specific line of products. I set it up in a way so I can use the data and have it linked to visio, which is essentially the output of the data.

I would be surprised if Visio could not link to queries just as it links to tables.

They do all use the same structure but I separated them so I can run queries in the future more easily.

You moved in the wrong direction then. You will need multiple queries where you would have only needed one with a parameter to select the product line if you had one table. And you need kludges like the Union you showed.

I strongly advise you to reconsider your decision to use multiple tables. ProductLineID should be a field in the table that holds all the data.

 

[azhian]

Just for the fun of it to check the 6th query issue, have you tried removing the 6th query to see if the 7th (which will become 6) will run or duplicate query 5 (which you know works) as query 6?

I've tried moving around the queries and made other queries making sure that all the tables work properly. The only time I get any issue with it is when I have more than 6 tables which I'm still trying to understand why.

 

[azhian]

I would be surprised if Visio could not link to queries just as it links to tables.

You moved in the wrong direction then. You will need multiple queries where you would have only needed one with a parameter to select the product line if you had one table. And you need kludges like the Union you showed.

I strongly advise you to reconsider your decision to use multiple tables. ProductLineID should be a field in the table that holds all the data.

This seems to be the popular opinion. I guess I should combine all the tables into one large table and then create queries which would then kind of replace the current tables. Does that sound like a good idea?

 

[Randy]

correct, or you could also put a "parameter" in the query and let the user decide. For example create one table, then create a query against that table, and in the query criteria for the product line field put something similar to this Like [enter Product line Id i.e. 12345]&"*" then when an end user opens the query a dialogue box will open first, and the user will need to enter the correct productlineid. The LIKE command and wild card "*" will allow some flexibility in choosing multiple options. Just a suggestion.