Query is too complex

I am running Access 2007.

I have data in about 20 tables that I want to combine together.
This is groundwater sample data from a myriad of sample locations.
The tables are basically formatted as follows:
Location, Depth, SampleDate, [Cmpnd_1], [Cmpnd_1 Qualifer], [Cmpnd_2],
[Cmpnd_2 Qualifier], ..., [Cmpnd_N], [Cmpnd_N Qualfier]
(where Cmpnd_1, Cmpnd_2, ..., Cmpnd_N are actual compound names such as
Acetone or Benzene).

The tables all have different compounds in them.
The number of compounds in each table vary anywhere from 25-50.

My plan was to develop an reverse crosstab query against each table and then
develop a union query to union together all of the reverse crosstab queries.
So far I have only developed 2 reverse crosstab queries.

The reverse crosstab queries work and are structured as follows:
Select Location, Depth, SampleDate, "Cmpnd_1" as Compound, [Cmpnd_1] as
Result, [Cmpnd_1_Qualifier] as Qualifier from Table_1
Union All
Select Location, Depth, SampleDate, "Cmpnd_2" as Compound, [Cmpnd_2] as
Result, [Cmpnd_2_Qualifier] as Qualifier from Table_1
Union All
Select Location, Depth, SampleDate, "Cmpnd_3" as Compound, [Cmpnd_3] as
Result, [Cmpnd_3_Qualifier] as Qualifier from Table_1
....
Union All
Select Location, Depth, SampleDate, "Cmpnd_N" as Compound, [Cmpnd_N] as
Result, [Cmpnd_N_Qualifier] as Qualifier from Table_1;

and

Select Location, Depth, SampleDate, "Cmpnd_1" as Compound, [Cmpnd_1] as
Result, [Cmpnd_1_Qualifier] as Qualifier from Table_2
Union All
Select Location, Depth, SampleDate, "Cmpnd_2" as Compound, [Cmpnd_2] as
Result, [Cmpnd_2_Qualifier] as Qualifier from Table_2
Union All
Select Location, Depth, SampleDate, "Cmpnd_3" as Compound, [Cmpnd_3] as
Result, [Cmpnd_3_Qualifier] as Qualifier from Table_2
....
Union All
Select Location, Depth, SampleDate, "Cmpnd_N" as Compound, [Cmpnd_N] as
Result, [Cmpnd_N_Qualifier] as Qualifier from Table_2;

I also developed the union query to union together those 2 queries.
My union query is structured as follows:
Select Location, Depth, SampleDate, Compound, Result, Qualifier from
Rev_Xtab_Query_1
Union All
Select Location, Depth, SampleDate, Compound, Result, Qualifier from
Rev_Xtab_Query_2;

When I attempt to run the union query I get the error message "Query is too
complex."

I have even tried combining the reverse crosstab queries together in a
single query but get the same error message.


What is "too complex" about this?
Is this just too much "unioning"?
Is the SQL string too long?

Thank you for your help.

It could well be then SQL string is too long. Or maybe just too many joins. I
think that you can get around the SQL string problem by running it in code
instead of a query.

However I have another suggestion. I'm assuming that you are stuck with the
poor table structure. How about creating a temporary table and using a string
of queries to populate it as needed. Assuming that the data is somewhat
static, you could first empty the table then append the data with queries
before running any reports or using other queries on it. You may even be able
to use the Union queries that are working now. One downside is that the
database might experience bloat and need frequent Compact and Repairs.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"JP" wrote:

I am running Access 2007.

I have data in about 20 tables that I want to combine together.
This is groundwater sample data from a myriad of sample locations.
The tables are basically formatted as follows:
Location, Depth, SampleDate, [Cmpnd_1], [Cmpnd_1 Qualifer], [Cmpnd_2],
[Cmpnd_2 Qualifier], ..., [Cmpnd_N], [Cmpnd_N Qualfier]
(where Cmpnd_1, Cmpnd_2, ..., Cmpnd_N are actual compound names such as
Acetone or Benzene).

The tables all have different compounds in them.
The number of compounds in each table vary anywhere from 25-50.

My plan was to develop an reverse crosstab query against each table and then
develop a union query to union together all of the reverse crosstab queries.
So far I have only developed 2 reverse crosstab queries.

The reverse crosstab queries work and are structured as follows:
Select Location, Depth, SampleDate, "Cmpnd_1" as Compound, [Cmpnd_1] as
Result, [Cmpnd_1_Qualifier] as Qualifier from Table_1
Union All
Select Location, Depth, SampleDate, "Cmpnd_2" as Compound, [Cmpnd_2] as
Result, [Cmpnd_2_Qualifier] as Qualifier from Table_1
Union All
Select Location, Depth, SampleDate, "Cmpnd_3" as Compound, [Cmpnd_3] as
Result, [Cmpnd_3_Qualifier] as Qualifier from Table_1
....
Union All
Select Location, Depth, SampleDate, "Cmpnd_N" as Compound, [Cmpnd_N] as
Result, [Cmpnd_N_Qualifier] as Qualifier from Table_1;

and

Select Location, Depth, SampleDate, "Cmpnd_1" as Compound, [Cmpnd_1] as
Result, [Cmpnd_1_Qualifier] as Qualifier from Table_2
Union All
Select Location, Depth, SampleDate, "Cmpnd_2" as Compound, [Cmpnd_2] as
Result, [Cmpnd_2_Qualifier] as Qualifier from Table_2
Union All
Select Location, Depth, SampleDate, "Cmpnd_3" as Compound, [Cmpnd_3] as
Result, [Cmpnd_3_Qualifier] as Qualifier from Table_2
....
Union All
Select Location, Depth, SampleDate, "Cmpnd_N" as Compound, [Cmpnd_N] as
Result, [Cmpnd_N_Qualifier] as Qualifier from Table_2;

I also developed the union query to union together those 2 queries.
My union query is structured as follows:
Select Location, Depth, SampleDate, Compound, Result, Qualifier from
Rev_Xtab_Query_1
Union All
Select Location, Depth, SampleDate, Compound, Result, Qualifier from
Rev_Xtab_Query_2;

When I attempt to run the union query I get the error message "Query is too
complex."

I have even tried combining the reverse crosstab queries together in a
single query but get the same error message.


What is "too complex" about this?
Is this just too much "unioning"?
Is the SQL string too long?

Thank you for your help.

JP,

You are never going to get all of that into a single SQL statement. My
recommendation would be to write a routine that loops through a list of
tables, and then loops through the fields, developing a single append query
for each compound in each table, and appending all of the results into a
single table.

This is conceptual, and totally untested:

Public Sub NormalizeSampleData

dim strSQL as string
dim aTableNames() as string
dim tdf as DAO.TableDef
dim intTableLoop as integer, intFieldLoop as integer
dim strCompound as string

aTableNames = Split("Table_1", "Table_2", "Table_3")

For intTableLoop = lbound(aTableNames) to ubound(aTableNames)

set tdf = currentdb.tabledefs(aTableNames(intTableLoop))

For intFieldLoop = 4 to tdf.fields - 1 step 2

strCompound = tdf.fields(intFieldLoop).name

strSQL = "INSERT INTO tbl_Combined_Results " _
& "(Location, Depth, SampleDate, Compound, Result,
Qualifier) " _
& "SELECT Location, Depth, SampleDate, " _
& "[" & strCompound & "], " _
& "[" & strCompount & "_Qualifier] " _
& "FROM [" & aTableNames(intTableLoop) & "]"
currentdb.execute strsql, dbFailOnError

Next intFieldLoop

Next intTableLoop

End Sub

You might even want to modify this so that you pass it a parameter array of
tables.

----
HTH
Dale



"JP" wrote:

I am running Access 2007.

I have data in about 20 tables that I want to combine together.
This is groundwater sample data from a myriad of sample locations.
The tables are basically formatted as follows:
Location, Depth, SampleDate, [Cmpnd_1], [Cmpnd_1 Qualifer], [Cmpnd_2],
[Cmpnd_2 Qualifier], ..., [Cmpnd_N], [Cmpnd_N Qualfier]
(where Cmpnd_1, Cmpnd_2, ..., Cmpnd_N are actual compound names such as
Acetone or Benzene).

The tables all have different compounds in them.
The number of compounds in each table vary anywhere from 25-50.

My plan was to develop an reverse crosstab query against each table and then
develop a union query to union together all of the reverse crosstab queries.
So far I have only developed 2 reverse crosstab queries.

The reverse crosstab queries work and are structured as follows:
Select Location, Depth, SampleDate, "Cmpnd_1" as Compound, [Cmpnd_1] as
Result, [Cmpnd_1_Qualifier] as Qualifier from Table_1
Union All
Select Location, Depth, SampleDate, "Cmpnd_2" as Compound, [Cmpnd_2] as
Result, [Cmpnd_2_Qualifier] as Qualifier from Table_1
Union All
Select Location, Depth, SampleDate, "Cmpnd_3" as Compound, [Cmpnd_3] as
Result, [Cmpnd_3_Qualifier] as Qualifier from Table_1
....
Union All
Select Location, Depth, SampleDate, "Cmpnd_N" as Compound, [Cmpnd_N] as
Result, [Cmpnd_N_Qualifier] as Qualifier from Table_1;

and

Select Location, Depth, SampleDate, "Cmpnd_1" as Compound, [Cmpnd_1] as
Result, [Cmpnd_1_Qualifier] as Qualifier from Table_2
Union All
Select Location, Depth, SampleDate, "Cmpnd_2" as Compound, [Cmpnd_2] as
Result, [Cmpnd_2_Qualifier] as Qualifier from Table_2
Union All
Select Location, Depth, SampleDate, "Cmpnd_3" as Compound, [Cmpnd_3] as
Result, [Cmpnd_3_Qualifier] as Qualifier from Table_2
....
Union All
Select Location, Depth, SampleDate, "Cmpnd_N" as Compound, [Cmpnd_N] as
Result, [Cmpnd_N_Qualifier] as Qualifier from Table_2;

I also developed the union query to union together those 2 queries.
My union query is structured as follows:
Select Location, Depth, SampleDate, Compound, Result, Qualifier from
Rev_Xtab_Query_1
Union All
Select Location, Depth, SampleDate, Compound, Result, Qualifier from
Rev_Xtab_Query_2;

When I attempt to run the union query I get the error message "Query is too
complex."

I have even tried combining the reverse crosstab queries together in a
single query but get the same error message.


What is "too complex" about this?
Is this just too much "unioning"?
Is the SQL string too long?

Thank you for your help.

Thanks for the suggestions Jerry.
I was really hoping to avoid making any new tables.

JP


"Jerry Whittle" wrote:

It could well be then SQL string is too long. Or maybe just too many joins. I
think that you can get around the SQL string problem by running it in code
instead of a query.

However I have another suggestion. I'm assuming that you are stuck with the
poor table structure. How about creating a temporary table and using a string
of queries to populate it as needed. Assuming that the data is somewhat
static, you could first empty the table then append the data with queries
before running any reports or using other queries on it. You may even be able
to use the Union queries that are working now. One downside is that the
database might experience bloat and need frequent Compact and Repairs.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"JP" wrote:

I am running Access 2007.

I have data in about 20 tables that I want to combine together.
This is groundwater sample data from a myriad of sample locations.
The tables are basically formatted as follows:
Location, Depth, SampleDate, [Cmpnd_1], [Cmpnd_1 Qualifer], [Cmpnd_2],
[Cmpnd_2 Qualifier], ..., [Cmpnd_N], [Cmpnd_N Qualfier]
(where Cmpnd_1, Cmpnd_2, ..., Cmpnd_N are actual compound names such as
Acetone or Benzene).

The tables all have different compounds in them.
The number of compounds in each table vary anywhere from 25-50.

My plan was to develop an reverse crosstab query against each table and then
develop a union query to union together all of the reverse crosstab queries.
So far I have only developed 2 reverse crosstab queries.

The reverse crosstab queries work and are structured as follows:
Select Location, Depth, SampleDate, "Cmpnd_1" as Compound, [Cmpnd_1] as
Result, [Cmpnd_1_Qualifier] as Qualifier from Table_1
Union All
Select Location, Depth, SampleDate, "Cmpnd_2" as Compound, [Cmpnd_2] as
Result, [Cmpnd_2_Qualifier] as Qualifier from Table_1
Union All
Select Location, Depth, SampleDate, "Cmpnd_3" as Compound, [Cmpnd_3] as
Result, [Cmpnd_3_Qualifier] as Qualifier from Table_1
....
Union All
Select Location, Depth, SampleDate, "Cmpnd_N" as Compound, [Cmpnd_N] as
Result, [Cmpnd_N_Qualifier] as Qualifier from Table_1;

and

Select Location, Depth, SampleDate, "Cmpnd_1" as Compound, [Cmpnd_1] as
Result, [Cmpnd_1_Qualifier] as Qualifier from Table_2
Union All
Select Location, Depth, SampleDate, "Cmpnd_2" as Compound, [Cmpnd_2] as
Result, [Cmpnd_2_Qualifier] as Qualifier from Table_2
Union All
Select Location, Depth, SampleDate, "Cmpnd_3" as Compound, [Cmpnd_3] as
Result, [Cmpnd_3_Qualifier] as Qualifier from Table_2
....
Union All
Select Location, Depth, SampleDate, "Cmpnd_N" as Compound, [Cmpnd_N] as
Result, [Cmpnd_N_Qualifier] as Qualifier from Table_2;

I also developed the union query to union together those 2 queries.
My union query is structured as follows:
Select Location, Depth, SampleDate, Compound, Result, Qualifier from
Rev_Xtab_Query_1
Union All
Select Location, Depth, SampleDate, Compound, Result, Qualifier from
Rev_Xtab_Query_2;

When I attempt to run the union query I get the error message "Query is too
complex."

I have even tried combining the reverse crosstab queries together in a
single query but get the same error message.


What is "too complex" about this?
Is this just too much "unioning"?
Is the SQL string too long?

Thank you for your help.

Dale,

Thanks for the suggestion and the sample code.

JP

"Dale Fye" wrote:

JP,

You are never going to get all of that into a single SQL statement. My
recommendation would be to write a routine that loops through a list of
tables, and then loops through the fields, developing a single append query
for each compound in each table, and appending all of the results into a
single table.

This is conceptual, and totally untested:

Public Sub NormalizeSampleData

dim strSQL as string
dim aTableNames() as string
dim tdf as DAO.TableDef
dim intTableLoop as integer, intFieldLoop as integer
dim strCompound as string

aTableNames = Split("Table_1", "Table_2", "Table_3")

For intTableLoop = lbound(aTableNames) to ubound(aTableNames)

set tdf = currentdb.tabledefs(aTableNames(intTableLoop))

For intFieldLoop = 4 to tdf.fields - 1 step 2

strCompound = tdf.fields(intFieldLoop).name

strSQL = "INSERT INTO tbl_Combined_Results " _
& "(Location, Depth, SampleDate, Compound, Result,
Qualifier) " _
& "SELECT Location, Depth, SampleDate, " _
& "[" & strCompound & "], " _
& "[" & strCompount & "_Qualifier] " _
& "FROM [" & aTableNames(intTableLoop) & "]"
currentdb.execute strsql, dbFailOnError

Next intFieldLoop

Next intTableLoop

End Sub

You might even want to modify this so that you pass it a parameter array of
tables.

----
HTH
Dale



"JP" wrote:

I am running Access 2007.

I have data in about 20 tables that I want to combine together.
This is groundwater sample data from a myriad of sample locations.
The tables are basically formatted as follows:
Location, Depth, SampleDate, [Cmpnd_1], [Cmpnd_1 Qualifer], [Cmpnd_2],
[Cmpnd_2 Qualifier], ..., [Cmpnd_N], [Cmpnd_N Qualfier]
(where Cmpnd_1, Cmpnd_2, ..., Cmpnd_N are actual compound names such as
Acetone or Benzene).

The tables all have different compounds in them.
The number of compounds in each table vary anywhere from 25-50.

My plan was to develop an reverse crosstab query against each table and then
develop a union query to union together all of the reverse crosstab queries.
So far I have only developed 2 reverse crosstab queries.

The reverse crosstab queries work and are structured as follows:
Select Location, Depth, SampleDate, "Cmpnd_1" as Compound, [Cmpnd_1] as
Result, [Cmpnd_1_Qualifier] as Qualifier from Table_1
Union All
Select Location, Depth, SampleDate, "Cmpnd_2" as Compound, [Cmpnd_2] as
Result, [Cmpnd_2_Qualifier] as Qualifier from Table_1
Union All
Select Location, Depth, SampleDate, "Cmpnd_3" as Compound, [Cmpnd_3] as
Result, [Cmpnd_3_Qualifier] as Qualifier from Table_1
....
Union All
Select Location, Depth, SampleDate, "Cmpnd_N" as Compound, [Cmpnd_N] as
Result, [Cmpnd_N_Qualifier] as Qualifier from Table_1;

and

Select Location, Depth, SampleDate, "Cmpnd_1" as Compound, [Cmpnd_1] as
Result, [Cmpnd_1_Qualifier] as Qualifier from Table_2
Union All
Select Location, Depth, SampleDate, "Cmpnd_2" as Compound, [Cmpnd_2] as
Result, [Cmpnd_2_Qualifier] as Qualifier from Table_2
Union All
Select Location, Depth, SampleDate, "Cmpnd_3" as Compound, [Cmpnd_3] as
Result, [Cmpnd_3_Qualifier] as Qualifier from Table_2
....
Union All
Select Location, Depth, SampleDate, "Cmpnd_N" as Compound, [Cmpnd_N] as
Result, [Cmpnd_N_Qualifier] as Qualifier from Table_2;

I also developed the union query to union together those 2 queries.
My union query is structured as follows:
Select Location, Depth, SampleDate, Compound, Result, Qualifier from
Rev_Xtab_Query_1
Union All
Select Location, Depth, SampleDate, Compound, Result, Qualifier from
Rev_Xtab_Query_2;

When I attempt to run the union query I get the error message "Query is too
complex."

I have even tried combining the reverse crosstab queries together in a
single query but get the same error message.


What is "too complex" about this?
Is this just too much "unioning"?
Is the SQL string too long?

Thank you for your help.

I'm with Jerry ... the table structure you're implying seems ...
spreadsheetly. Access is optimized for well-normalized data.

As long as you try to feed it 'sheet data, both you and Access will have to
work overtime to come up with work-arounds. In the long run, it might take
you much less time to normalize the data structure first, then use Access as
designed.

And if your table (or tables) have one field for each compound, won't you
have to modify the table structure every time there's a change in the number
of compounds? And your queries? And your forms? And your reports? And
....

That's a lot of maintenance!

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"JP" wrote in message
...
Thanks for the suggestions Jerry.
I was really hoping to avoid making any new tables.

JP


"Jerry Whittle" wrote:

It could well be then SQL string is too long. Or maybe just too many
joins. I
think that you can get around the SQL string problem by running it in
code
instead of a query.

However I have another suggestion. I'm assuming that you are stuck with
the
poor table structure. How about creating a temporary table and using a
string
of queries to populate it as needed. Assuming that the data is somewhat
static, you could first empty the table then append the data with queries
before running any reports or using other queries on it. You may even be
able
to use the Union queries that are working now. One downside is that the
database might experience bloat and need frequent Compact and Repairs.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"JP" wrote:

I am running Access 2007.

I have data in about 20 tables that I want to combine together.
This is groundwater sample data from a myriad of sample locations.
The tables are basically formatted as follows:
Location, Depth, SampleDate, [Cmpnd_1], [Cmpnd_1 Qualifer],
[Cmpnd_2],
[Cmpnd_2 Qualifier], ..., [Cmpnd_N], [Cmpnd_N Qualfier]
(where Cmpnd_1, Cmpnd_2, ..., Cmpnd_N are actual compound names such
as
Acetone or Benzene).

The tables all have different compounds in them.
The number of compounds in each table vary anywhere from 25-50.

My plan was to develop an reverse crosstab query against each table and
then
develop a union query to union together all of the reverse crosstab
queries.
So far I have only developed 2 reverse crosstab queries.

The reverse crosstab queries work and are structured as follows:
Select Location, Depth, SampleDate, "Cmpnd_1" as Compound, [Cmpnd_1] as
Result, [Cmpnd_1_Qualifier] as Qualifier from Table_1
Union All
Select Location, Depth, SampleDate, "Cmpnd_2" as Compound, [Cmpnd_2] as
Result, [Cmpnd_2_Qualifier] as Qualifier from Table_1
Union All
Select Location, Depth, SampleDate, "Cmpnd_3" as Compound, [Cmpnd_3] as
Result, [Cmpnd_3_Qualifier] as Qualifier from Table_1
....
Union All
Select Location, Depth, SampleDate, "Cmpnd_N" as Compound, [Cmpnd_N] as
Result, [Cmpnd_N_Qualifier] as Qualifier from Table_1;

and

Select Location, Depth, SampleDate, "Cmpnd_1" as Compound, [Cmpnd_1] as
Result, [Cmpnd_1_Qualifier] as Qualifier from Table_2
Union All
Select Location, Depth, SampleDate, "Cmpnd_2" as Compound, [Cmpnd_2] as
Result, [Cmpnd_2_Qualifier] as Qualifier from Table_2
Union All
Select Location, Depth, SampleDate, "Cmpnd_3" as Compound, [Cmpnd_3] as
Result, [Cmpnd_3_Qualifier] as Qualifier from Table_2
....
Union All
Select Location, Depth, SampleDate, "Cmpnd_N" as Compound, [Cmpnd_N] as
Result, [Cmpnd_N_Qualifier] as Qualifier from Table_2;

I also developed the union query to union together those 2 queries.
My union query is structured as follows:
Select Location, Depth, SampleDate, Compound, Result, Qualifier from
Rev_Xtab_Query_1
Union All
Select Location, Depth, SampleDate, Compound, Result, Qualifier from
Rev_Xtab_Query_2;

When I attempt to run the union query I get the error message "Query is
too
complex."

I have even tried combining the reverse crosstab queries together in a
single query but get the same error message.


What is "too complex" about this?
Is this just too much "unioning"?
Is the SQL string too long?

Thank you for your help.