If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Query is too complex
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. |
#3
|
|||
|
|||
Query is too complex
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. |
#4
|
|||
|
|||
Query is too complex
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. |
#5
|
|||
|
|||
Query is too complex
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. |
#6
|
|||
|
|||
Query is too complex
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. |
Thread Tools | |
Display Modes | |
|
|