limits of normalizing union queries

[jw]

Thank you for all your answers. Here is more info.

I am transforming data originally entered (and analyzed)
in Excel. DBH is the diameter at breast height of a
tree. For each plot and tree species, the excel
spreadsheet had a column for each DBH, ie DBH1,
DBH2...DBH162.

I imported the spreadsheet to Access and did a
normalizing union query to reformat the DBH fields into
two fields as follows

TbhDBH
DBHNum (number)
DBhValue

Example SQL follows:

Select "2003" as [Year], [Plot], [Transect],
[Species], "130" as [DBHNum], [DBH130] as [DBHValue]
From ReformatP2
Where [DBH130] is not null

Union Select "2003" as [Year], [Plot], [Transect],
[Species], "131" as [DBHNum], [DBH131] as [DBHValue]
From ReformatP2
Where [DBH131] is not null

I have had success analyzing data with this table design
in similar databases. I learned from this newsgroup
that "fields are expensive, and records are cheap." I
think it is the number of "Union selects" I use in the
query because I don't seem to be able to get more than
about 40 in the query, even when the number of records in
the destination table is quite low.

Please let me know if I am doing anything that does not
seem logical! Thanks!

 

[Van T. Dinh]

SQL String can be up to 64K characters so you are a long way from the limit.

There are some other limits if you use the QBE.

--
HTH
Van T. Dinh
MVP (Access)

Thank you for all your answers. Here is more info.

I am transforming data originally entered (and analyzed)
in Excel. DBH is the diameter at breast height of a
tree. For each plot and tree species, the excel
spreadsheet had a column for each DBH, ie DBH1,
DBH2...DBH162.

I imported the spreadsheet to Access and did a
normalizing union query to reformat the DBH fields into
two fields as follows

TbhDBH
DBHNum (number)
DBhValue

Example SQL follows:

Select "2003" as [Year], [Plot], [Transect],
[Species], "130" as [DBHNum], [DBH130] as [DBHValue]
From ReformatP2
Where [DBH130] is not null

Union Select "2003" as [Year], [Plot], [Transect],
[Species], "131" as [DBHNum], [DBH131] as [DBHValue]
From ReformatP2
Where [DBH131] is not null

I have had success analyzing data with this table design
in similar databases. I learned from this newsgroup
that "fields are expensive, and records are cheap." I
think it is the number of "Union selects" I use in the
query because I don't seem to be able to get more than
about 40 in the query, even when the number of records in
the destination table is quite low.

Please let me know if I am doing anything that does not
seem logical! Thanks!

-----Original Message-----
Hi,

I am transforming some data using normalizing union
queries. I keep needing to split the queries as I am
getting a message that indicates that the "query is too
complex."

Is the limit the number of records in the resulting
destination table? If so what is the limit of records,
about 1000? Or is the number of times you can
write "Union select..." the limit, at 40 or so?

I would like to plan ahead where I am going to split my
data/queries!

Thanks for any help!
.