Mighty MS Access 97

Question

Are there any limits on SQL-expression length in MS Access 97 ?

Answer

From: Shamil Salakhetdinov <shamil@marta.darts.spb.ru>
To: ACCESS-L <ACCESS-L@PEACH.EASE.LSOFT.COM>
Subject: Mighty MS Access 97
Date: 18 February 1998 2:03

Hi All,

Probably, you find it curious (at least I didn't expect MS Access 97 can do that) - enclosed is a ***one*** union query which uses ~43 group by queries based on select queries from ~43 base tables where each select query has subselect in the "where in (" clause - I counted ~ 200 queires and base tables in one query. And MS Access 97 is able to execure such query on today's average PC Pentium 133/32MB /Win95. Queried database (.mdb file) has ~70,000 records.

Bravo MS!

Best wishes,
Shamil

P.S.

- Union query:

select "tblAccDocEvent" as [TableName],"AccDocId" as [RefFieldName],[AccDocId] as [RefFldValue]  from
[ztqry_GroupBy_tblAccDocEvent_AccDocId]
union select "tblAccDocEvent" as [TableName],"EmpId" as [RefFieldName],[EmpId] as [RefFldValue]  from
[ztqry_GroupBy_tblAccDocEvent_EmpId]  
union select "tblAccDoc" as [TableName],"AccDocAccountCompId" as [RefFieldName],[AccDocAccountCompId] as [RefFldValue] 
from [ztqry_GroupBy_tblAccDoc_AccDocAccountCompId]  
union select "tblAccDoc" as [TableName],"AccDocDlvrPersId" as [RefFieldName],[AccDocDlvrPersId] as [RefFldValue]  from
[ztqry_GroupBy_tblAccDoc_AccDocDlvrPersId]  
union select "tblAccDoc" as [TableName],"AccDocFromCompId" as [RefFieldName],[AccDocFromCompId] as [RefFldValue]  from
[ztqry_GroupBy_tblAccDoc_AccDocFromCompId]  
union select "tblAccDoc" as [TableName],"AccDocToCompId" as [RefFieldName],[AccDocToCompId] as [RefFldValue]  from
[ztqry_GroupBy_tblAccDoc_AccDocToCompId]  
union select "tblAccDoc" as [TableName],"BalanceId" as [RefFieldName],[BalanceId] as [RefFldValue]  from
[ztqry_GroupBy_tblAccDoc_BalanceId]  
union select "tblAccDocRelations" as [TableName],"AccDocId2" as [RefFieldName],[AccDocId2] as [RefFldValue]  from
[ztqry_GroupBy_tblAccDocRelations_AccDocId2]  
union select "tblAccEventOrder" as [TableName],"AccEventOrderType" as [RefFieldName],[AccEventOrderType] as
[RefFldValue]  from [ztqry_GroupBy_tblAccEventOrder_AccEventOrderType]  
union select "tblAccEventOrder" as [TableName],"RelType" as [RefFieldName],[RelType] as [RefFldValue]  from
[ztqry_GroupBy_tblAccEventOrder_RelType]  
union select "tblAccOpDetail" as [TableName],"AccOpDetCredCode" as [RefFieldName],[AccOpDetCredCode] as [RefFldValue] 
from [ztqry_GroupBy_tblAccOpDetail_AccOpDetCredCode]  
union select "tblAccount" as [TableName],"CurrCode" as [RefFieldName],[CurrCode] as [RefFldValue]  from
[ztqry_GroupBy_tblAccount_CurrCode]  
union select "tblAccServDetail" as [TableName],"AccServType" as [RefFieldName],[AccServType] as [RefFldValue]  from
[ztqry_GroupBy_tblAccServDetail_AccServType]  
union select "tblAccServDetail" as [TableName],"ContrId" as [RefFieldName],[ContrId] as [RefFldValue]  from
[ztqry_GroupBy_tblAccServDetail_ContrId]  
union select "tblAccTaxDetail" as [TableName],"AccTaxDetCode" as [RefFieldName],[AccTaxDetCode] as [RefFldValue]  from
[ztqry_GroupBy_tblAccTaxDetail_AccTaxDetCode]  
union select "tblCalculation" as [TableName],"CalcObjectSubType" as [RefFieldName],[CalcObjectSubType] as [RefFldValue]
 from [ztqry_GroupBy_tblCalculation_CalcObjectSubType]  
union select "tblCalculation" as [TableName],"CalcObjectType" as [RefFieldName],[CalcObjectType] as [RefFldValue]  from
[ztqry_GroupBy_tblCalculation_CalcObjectType]  
union select "tblCompBankAttr" as [TableName],"CompId" as [RefFieldName],[CompId] as [RefFldValue]  from
[ztqry_GroupBy_tblCompBankAttr_CompId]  
union select "tblCompany" as [TableName],"CountryCode" as [RefFieldName],[CountryCode] as [RefFldValue]  from
[ztqry_GroupBy_tblCompany_CountryCode]  
union select "tblEmpHistory" as [TableName],"DivId" as [RefFieldName],[DivId] as [RefFldValue]  from
[ztqry_GroupBy_tblEmpHistory_DivId]  
union select "tblEmployee" as [TableName],"DivId" as [RefFieldName],[DivId] as [RefFldValue]  from
[ztqry_GroupBy_tblEmployee_DivId]  
union select "tblGoodsMove" as [TableName],"AccDocId" as [RefFieldName],[AccDocId] as [RefFldValue]  from
[ztqry_GroupBy_tblGoodsMove_AccDocId]  
union select "tblGoodsMove" as [TableName],"AccOpCode" as [RefFieldName],[AccOpCode] as [RefFldValue]  from
[ztqry_GroupBy_tblGoodsMove_AccOpCode]  
union select "tblGoodsMove" as [TableName],"MsngType" as [RefFieldName],[MsngType] as [RefFldValue]  from
[ztqry_GroupBy_tblGoodsMove_MsngType]  
union select "tblGoodsMove" as [TableName],"PriceId" as [RefFieldName],[PriceId] as [RefFldValue]  from
[ztqry_GroupBy_tblGoodsMove_PriceId]  
union select "tblGoodsMove" as [TableName],"ProdItemId" as [RefFieldName],[ProdItemId] as [RefFldValue]  from
[ztqry_GroupBy_tblGoodsMove_ProdItemId]  
union select "tblGoodsMove" as [TableName],"ProdUnitId" as [RefFieldName],[ProdUnitId] as [RefFldValue]  from
[ztqry_GroupBy_tblGoodsMove_ProdUnitId]  
union select "tblOfficialPerson" as [TableName],"CompId" as [RefFieldName],[CompId] as [RefFldValue]  from
[ztqry_GroupBy_tblOfficialPerson_CompId]  
union select "tblOfficialPerson" as [TableName],"OffPersonType" as [RefFieldName],[OffPersonType] as [RefFldValue] 
from [ztqry_GroupBy_tblOfficialPerson_OffPersonType]  
union select "tblPartSaldo" as [TableName],"BalanceId" as [RefFieldName],[BalanceId] as [RefFldValue]  from
[ztqry_GroupBy_tblPartSaldo_BalanceId]  
union select "tblPartSaldo" as [TableName],"CompId" as [RefFieldName],[CompId] as [RefFldValue]  from
[ztqry_GroupBy_tblPartSaldo_CompId]  
union select "tblPartSaldo" as [TableName],"CurrCode" as [RefFieldName],[CurrCode] as [RefFldValue]  from
[ztqry_GroupBy_tblPartSaldo_CurrCode]  
union select "tblPartSaldoTurn" as [TableName],"CompId" as [RefFieldName],[CompId] as [RefFldValue]  from
[ztqry_GroupBy_tblPartSaldoTurn_CompId]  
union select "tblPartSaldoTurn" as [TableName],"CurrCode" as [RefFieldName],[CurrCode] as [RefFldValue]  from
[ztqry_GroupBy_tblPartSaldoTurn_CurrCode]  
union select "tblPosting" as [TableName],"AccDocId" as [RefFieldName],[AccDocId] as [RefFldValue]  from
[ztqry_GroupBy_tblPosting_AccDocId]  
union select "tblPosting" as [TableName],"BalanceId" as [RefFieldName],[BalanceId] as [RefFldValue]  from
[ztqry_GroupBy_tblPosting_BalanceId]  
union select "tblPosting" as [TableName],"PostCreditAccCode" as [RefFieldName],[PostCreditAccCode] as [RefFldValue] 
from [ztqry_GroupBy_tblPosting_PostCreditAccCode]  
union select "tblPosting" as [TableName],"PostDebitAccCode" as [RefFieldName],[PostDebitAccCode] as [RefFldValue]  from
[ztqry_GroupBy_tblPosting_PostDebitAccCode]  
union select "tblProdClassRel" as [TableName],"ClassType1" as [RefFieldName],[ClassType1] as [RefFldValue]  from
[ztqry_GroupBy_tblProdClassRel_ClassType1]  
union select "tblProdClassRel" as [TableName],"ClassType2" as [RefFieldName],[ClassType2] as [RefFldValue]  from
[ztqry_GroupBy_tblProdClassRel_ClassType2]  
union select "tblProdItem" as [TableName],"MsngType" as [RefFieldName],[MsngType] as [RefFldValue]  from
[ztqry_GroupBy_tblProdItem_MsngType]  
union select "tblProdItem" as [TableName],"ProdItemTaxCode2" as [RefFieldName],[ProdItemTaxCode2] as [RefFldValue] 
from [ztqry_GroupBy_tblProdItem_ProdItemTaxCode2]  
UNION select "tblGlossItem" as [TableName],"GlossCode" as [RefFieldName],[GlossCode] as [RefFldValue]  from
[ztqry_GroupBy_tblGlossItem_GlossCode];

- one of group by queries - ztqry_GroupBy_tblAccDoc_AccDocAccountCompId:

SELECT [AccDocAccountCompId] AS Expr1
FROM ztqrytblAccDoc_AccDocAccountCompId
GROUP BY [AccDocAccountCompId

- one of base queries - ztqrytblAccDoc_AccDocAccountCompId:

SELECT [AccDocId], [AccDocAccountCompId]
FROM tblAccDoc
WHERE ((([AccDocId]) In (select [IdFldValue] from [_zttblBrokenRefs] where ([TableName]="tblAccDoc" and 
[RefFldName]="AccDocAccountCompId"))))