Query is too complex

Guest · Mar 23, 2006

I have a query that produces 1000's of rows of data and I need to create
several smaller queries by indicating specific criteria. The first criteria
will be the "month" and this changes everytime I run the query. Then the
rows need to match 2 different field names. This is what I have and it is
giving me the error "Query is too complex" Any ideas?

SELECT [APAR 1].Month, [APAR 1].Client, [APAR 1].Group, [APAR 1].Region,
[APAR 1].Referenceable, [APAR 1].[Client Name], [APAR 1].[Client Position],
[APAR 1].[Interviewer Name], [APAR 1].[Scope of Services], [APAR 1].[CSC
Meets Overall Service Delivery Requirements], [APAR 1].[Mainframe service],
[APAR 1].[Midrange Service], [APAR 1].[Desktop/Laptop Service], [APAR
1].[Network Services], [APAR 1].[CSS Helpdesk], [APAR 1].[Collaborative
Computing], [APAR 1].Security, [APAR 1].[Distributed print/file servers],
[APAR 1].[Business Continuity], [APAR 1].[Project Management], [APAR
1].Applications, [APAR 1].[CSC delivers services within financial
expectations], [APAR 1].[CSC delivers services within expected schedule],
[APAR 2].[CSC service facilitates effective and efficient operation], [APAR
2].[CSC is seen as an innovator in our Partnership exploring], [APAR 2].[CSC
provides creative solutions to my business problems], [APAR 2].[Demonstrate
teamwork and collaboration], [APAR 2].[Proactively communicate pertinent
information], [APAR 2].[Are flexible in adapting to my changing business
priorities], [APAR 2].[Understand my objectives and goals], [APAR 2].[Key
Initiative 1], [APAR 2].[Status 1], [APAR 2].[Key Initiative 2], [APAR
2].[Status 2], [APAR 2].[Key Initiative 3], [APAR 2].[Status 3], [APAR
2].[Key Initiative 4], [APAR 2].[Status 4], [APAR 2].[Key Initiative 5],
[APAR 2].[Status 5], [APAR 2].[Key Initiative 6], [APAR 2].[Status 6], [APAR
2].[Key Initiative 7], [APAR 2].[Status 7], [APAR 2].[Key Initiative 8],
[APAR 2].[Status 8]
FROM [APAR 1] INNER JOIN [APAR 2] ON ([APAR 1].Group=[APAR 2].Group) AND
([APAR 1].Client=[APAR 2].Client) AND ([APAR 1].Month=[APAR 2].Month)
WHERE ((([APAR 1].Month)=[What month would you like the results for?])) And
((([APAR 1].Client)="Aerostructures") AND (([APAR 1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="Agency for Govt Mgmt") AND (([APAR
1].Group)="Denmark")) OR
((([APAR 1].Client)="Air Systems") AND (([APAR 1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="Aircraft Services Group") AND (([APAR 1].Group)="BAE
SYSTEMS")) OR
((([APAR 1].Client)="Albert Heijn") AND (([APAR 1].Group)="Netherlands")) OR
((([APAR 1].Client)="Alka") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Allianz") AND (([APAR 1].Group)="Allianz")) OR
((([APAR 1].Client)="AMP") AND (([APAR 1].Group)="Customer Service")) OR
((([APAR 1].Client)="AMP") AND (([APAR 1].Group)="Product Manufacturing")) OR
((([APAR 1].Client)="Anglian Water") AND (([APAR 1].Group)="UK Commercial"))
OR
((([APAR 1].Client)="AP-Pension") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="BAE Systems") AND (([APAR 1].Group)="BAE Systems
IEWS")) OR
((([APAR 1].Client)="Baker & Taylor") AND (([APAR 1].Group)="Baker & Taylor
- Overall")) OR
((([APAR 1].Client)="Bank Insinger de Beaufort") AND (([APAR
1].Group)="Netherlands")) OR
((([APAR 1].Client)="BankensBetalingssentral") AND (([APAR
1].Group)="Norway")) OR
((([APAR 1].Client)="Basell") AND (([APAR 1].Group)="Basell - Overall")) OR
((([APAR 1].Client)="Basell") AND (([APAR 1].Group)="Global Accounts")) OR
((([APAR 1].Client)="Belron") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton -
GSAP Services")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton Base
Metals - Cannington")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton Base
Metals - Cerro Colorado")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton Base
Metals - Chile Inc")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton Base
Metals - Global")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton Base
Metals - Minera Escondida")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton Base
Metals - Tintaya")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Carbon Steel Materials - BMA Coal")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Carbon Steel Materials - Collieries")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Carbon Steel Materials - Iron Ore")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Corporate - London")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Enterprise Architecture")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Petrolem - Global")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Petroleum - Australia")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Petroleum - Houston")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Petroleum - London")) OR
((([APAR 1].Client)="BHS") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="BlueScope Steel") AND (([APAR 1].Group)="Bluescope
Steel - Global")) OR
((([APAR 1].Client)="BNFL") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Bombardier Transportation") AND (([APAR
1].Group)="Global Accounts")) OR
((([APAR 1].Client)="BP") AND (([APAR 1].Group)="Austria")) OR
((([APAR 1].Client)="bp") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Capita") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Cluster 3") AND (([APAR 1].Group)="AEC")) OR
((([APAR 1].Client)="Codan") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="County of San Diego") AND (([APAR 1].Group)="CoSD -
Overall")) OR
((([APAR 1].Client)="CPR") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="CSC - Asia") AND (([APAR 1].Group)="CSC Internal
Systems")) OR
((([APAR 1].Client)="CSC - Australia") AND (([APAR 1].Group)="CSC Internal
Systems")) OR
((([APAR 1].Client)="CSC - Consulting") AND (([APAR 1].Group)="CSC Internal
Systems")) OR
((([APAR 1].Client)="CSC - Corporate Shared Services") AND (([APAR
1].Group)="CSC Internal Systems")) OR
((([APAR 1].Client)="CSC - EMEA") AND (([APAR 1].Group)="CSC - Internal")) OR
((([APAR 1].Client)="CSC - EMEA Central") AND (([APAR 1].Group)="CSC -
Internal")) OR
((([APAR 1].Client)="CSC - EMEA Nordic") AND (([APAR 1].Group)="CSC -
Internal")) OR
((([APAR 1].Client)="CSC - EMEA North") AND (([APAR 1].Group)="CSC -
Internal")) OR
((([APAR 1].Client)="CSC - EMEA South") AND (([APAR 1].Group)="CSC -
Internal")) OR
((([APAR 1].Client)="CSC - EMEA West") AND (([APAR 1].Group)="CSC -
Internal")) OR
((([APAR 1].Client)="CSC - Federal Sector") AND (([APAR 1].Group)="CSC
Internal Systems")) OR
((([APAR 1].Client)="CSC - FSG Banking Solutions") AND (([APAR
1].Group)="CSC Internal Systems")) OR
((([APAR 1].Client)="CSC - FSG Global Support") AND (([APAR 1].Group)="CSC
Internal Systems")) OR
((([APAR 1].Client)="CSC - FSG Life and Annuities") AND (([APAR
1].Group)="CSC Internal Systems")) OR
((([APAR 1].Client)="CSC - FSG Property and Casualty") AND (([APAR
1].Group)="CSC Internal Systems")) OR
((([APAR 1].Client)="CSC - India") AND (([APAR 1].Group)="CSC Internal
Systems")) OR
((([APAR 1].Client)="CSC - SUN") AND (([APAR 1].Group)="CSC Internal
Systems")) OR
((([APAR 1].Client)="Customer Solutions & Support") AND (([APAR
1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="Customs & Tax") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Danisco") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Danish Police") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Delphi") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Dept of Health") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="DLR") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="DNB Finans") AND (([APAR 1].Group)="Norway")) OR
((([APAR 1].Client)="DNB Kort") AND (([APAR 1].Group)="Norway")) OR
((([APAR 1].Client)="Dun & Bradstreet") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="DuPont") AND (([APAR 1].Group)="DuPont - Overall")) OR
((([APAR 1].Client)="DuPont") AND (([APAR 1].Group)="DuPont - Performance
Coatings")) OR
((([APAR 1].Client)="DuPont") AND (([APAR 1].Group)="Global Accounts")) OR
((([APAR 1].Client)="Elidis") AND (([APAR 1].Group)="France")) OR
((([APAR 1].Client)="Eurofighter") AND (([APAR 1].Group)="Germany")) OR
((([APAR 1].Client)="F van Lanschot Bankiers") AND (([APAR
1].Group)="Belgium")) OR
((([APAR 1].Client)="Falck") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="FNAC") AND (([APAR 1].Group)="France")) OR
((([APAR 1].Client)="Ford") AND (([APAR 1].Group)="Germany")) OR
((([APAR 1].Client)="Frequentis") AND (([APAR 1].Group)="Austria")) OR
((([APAR 1].Client)="FSG - City of Westminster Assurance") AND (([APAR
1].Group)="UK Commercial")) OR
((([APAR 1].Client)="FSG - Royal London") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="FSG - Stonebridge") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="FSG - Swiss Re") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="Gallaher") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="GE") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="Canada")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="Gen Dyn -
Bath Iron Works")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="Gen Dyn -
Corporate/SRI")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="Gen Dyn -
Electric Boat")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="Gen Dyn -
GDAIS")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="Gen Dyn -
GDATP")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="Gen Dyn -
Network Systems")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="General
Dynamics - Overall")) OR
((([APAR 1].Client)="GEPS Nuovo Pignone") AND (([APAR 1].Group)="Italy")) OR
((([APAR 1].Client)="GHI") AND (([APAR 1].Group)="GHI - Overall")) OR
((([APAR 1].Client)="Global Collect") AND (([APAR 1].Group)="Netherlands")) OR
((([APAR 1].Client)="Goodrich") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Goteborgs Sparvagar") AND (([APAR 1].Group)="Sweden")) OR
((([APAR 1].Client)="Gripen International") AND (([APAR 1].Group)="Sweden"))
OR
((([APAR 1].Client)="Grundfos") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="GTI") AND (([APAR 1].Group)="Netherlands")) OR
((([APAR 1].Client)="Haldor Topsoe") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Head Office & Shared Services") AND (([APAR
1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="HPC 97") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Hyatt Corporation") AND (([APAR 1].Group)="Hyatt
Corporation - Overall")) OR
((([APAR 1].Client)="ICI") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Insyte") AND (([APAR 1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="Interbrew") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Interpolis") AND (([APAR 1].Group)="Netherlands")) OR
((([APAR 1].Client)="ISS") AND (([APAR 1].Group)="Global Accounts")) OR
((([APAR 1].Client)="IT-Papier") AND (([APAR 1].Group)="Austria")) OR
((([APAR 1].Client)="KLP") AND (([APAR 1].Group)="Norway")) OR
((([APAR 1].Client)="Land Registry") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Land Rover") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Landstinget I Ostergotland") AND (([APAR
1].Group)="Sweden")) OR
((([APAR 1].Client)="Marconi") AND (([APAR 1].Group)="Global Accounts")) OR
((([APAR 1].Client)="Marconi") AND (([APAR 1].Group)="Marconi - Americas")) OR
((([APAR 1].Client)="MBDA") AND (([APAR 1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="Merlot - Nedbank") AND (([APAR 1].Group)="South
Africa")) OR
((([APAR 1].Client)="Merlot - Old Mutual") AND (([APAR 1].Group)="South
Africa")) OR
((([APAR 1].Client)="Motorola") AND (([APAR 1].Group)="Global Accounts")) OR
((([APAR 1].Client)="MRV") AND (([APAR 1].Group)="Germany")) OR
((([APAR 1].Client)="National Grid") AND (([APAR 1].Group)="UK Commercial"))
OR
((([APAR 1].Client)="Naval Ships") AND (([APAR 1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="NHS Birmingham and the Black Country SHA") AND (([APAR
1].Group)="NHS Overall")) OR
((([APAR 1].Client)="NHS Cheshire and Merseyside SHA") AND (([APAR
1].Group)="NHS Overall")) OR
((([APAR 1].Client)="NHS Cumbria and Lancashire SHA") AND (([APAR
1].Group)="NHS Overall")) OR
((([APAR 1].Client)="NHS Greater Manchester SHA") AND (([APAR 1].Group)="NHS
Overall")) OR
((([APAR 1].Client)="NHS NPfIT") AND (([APAR 1].Group)="NHS Overall")) OR
((([APAR 1].Client)="NHS Shropshire and Staffordshire SHA") AND (([APAR
1].Group)="NHS Overall")) OR
((([APAR 1].Client)="NHS West Midlands South SHA") AND (([APAR
1].Group)="NHS Overall")) OR
((([APAR 1].Client)="NKT") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Nordea Liv Norge AS") AND (([APAR 1].Group)="Norway")) OR
((([APAR 1].Client)="NS") AND (([APAR 1].Group)="Netherlands")) OR
((([APAR 1].Client)="Nynashamns Kommun") AND (([APAR 1].Group)="Sweden")) OR
((([APAR 1].Client)="oest. Staatsdruckerei") AND (([APAR
1].Group)="Austria")) OR
((([APAR 1].Client)="Old Mutual") AND (([APAR 1].Group)="South Africa")) OR
((([APAR 1].Client)="Panavia") AND (([APAR 1].Group)="Germany")) OR
((([APAR 1].Client)="PFA Pension") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Pfizer") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Platform Solutions(Avionics)") AND (([APAR
1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="Quaker Chemical") AND (([APAR 1].Group)="Netherlands"))
OR
((([APAR 1].Client)="Quest") AND (([APAR 1].Group)="Netherlands")) OR
((([APAR 1].Client)="Renault") AND (([APAR 1].Group)="France")) OR
((([APAR 1].Client)="Reynolds & Reynolds") AND (([APAR 1].Group)="Reynolds &
Reynolds - Overall")) OR
((([APAR 1].Client)="RMG - Enterprise") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="RMG - Logistics & Transport") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="RMG - Parcelforce Worldwide") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="RMG - POL") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="RMG - RM-International") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="RMG - RM-UK") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Royal Ordnance Defence") AND (([APAR 1].Group)="BAE
SYSTEMS")) OR
((([APAR 1].Client)="Saab Aerospace AB") AND (([APAR 1].Group)="Sweden")) OR
((([APAR 1].Client)="Saab Rosemount") AND (([APAR 1].Group)="Sweden")) OR
((([APAR 1].Client)="SAS - Cargo") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS - CIO Function/Headoffice") AND (([APAR
1].Group)="SAS")) OR
((([APAR 1].Client)="SAS - Corporate (Finance)") AND (([APAR
1].Group)="SAS")) OR
((([APAR 1].Client)="SAS - SGS Ground Services") AND (([APAR
1].Group)="SAS")) OR
((([APAR 1].Client)="SAS - STS Technical Services") AND (([APAR
1].Group)="SAS")) OR
((([APAR 1].Client)="SAS Airline IT") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS Braathens") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS Corp. HR") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS DK") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS EAG") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS Production systems") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS SE") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS/STOOM") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SBI") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Scandihealth") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Scapa") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Schroders") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="SELEX S&AS") AND (([APAR 1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="Siemens Westinghouse Power Corp") AND (([APAR
1].Group)="Siemens Westinghouse - Overall")) OR
((([APAR 1].Client)="Sodertalje Kommun") AND (([APAR 1].Group)="Sweden")) OR
((([APAR 1].Client)="Sparebank1") AND (([APAR 1].Group)="Norway")) OR
((([APAR 1].Client)="St. Vincent Catholic Medical Center") AND (([APAR
1].Group)="St. Vincent - Overall")) OR
((([APAR 1].Client)="Standard Register Co.") AND (([APAR 1].Group)="Standard
Register - Overall")) OR
((([APAR 1].Client)="Statoil") AND (([APAR 1].Group)="Norway")) OR
((([APAR 1].Client)="Submarines") AND (([APAR 1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="SUN") AND (([APAR 1].Group)="CSC - Internal")) OR
((([APAR 1].Client)="Sun Microsystems Inc.") AND (([APAR 1].Group)="Sun -
Overall")) OR
((([APAR 1].Client)="Sydkraft") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="TDC") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Techspace Aero") AND (([APAR 1].Group)="Belgium")) OR
((([APAR 1].Client)="Textron") AND (([APAR 1].Group)="Global Accounts")) OR
((([APAR 1].Client)="Textron") AND (([APAR 1].Group)="Textron - Bell
Helicopters")) OR
((([APAR 1].Client)="Textron") AND (([APAR 1].Group)="Textron - Cessna")) OR
((([APAR 1].Client)="Textron") AND (([APAR 1].Group)="Textron - Financial"))
OR
((([APAR 1].Client)="Textron") AND (([APAR 1].Group)="Textron - Systems")) OR
((([APAR 1].Client)="Truworths") AND (([APAR 1].Group)="South Africa")) OR
((([APAR 1].Client)="TRYG") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Unisys Payment Services & Solutions") AND (([APAR
1].Group)="Netherlands")) OR
((([APAR 1].Client)="United Technology Corp.") AND (([APAR 1].Group)="UTC -
Overall")) OR
((([APAR 1].Client)="United Technology Corp.") AND (([APAR 1].Group)="UTC -
Sikorsky")) OR
((([APAR 1].Client)="Universal Music Industries") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="UTC EMEA") AND (([APAR 1].Group)="Global Accounts")) OR
((([APAR 1].Client)="UWS") AND (([APAR 1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="V&S Vin & Sprit AB") AND (([APAR 1].Group)="Sweden")) OR
((([APAR 1].Client)="Vesta") AND (([APAR 1].Group)="Norway")) OR
((([APAR 1].Client)="Vital") AND (([APAR 1].Group)="Norway")) OR
((([APAR 1].Client)="Westinghouse Electric") AND (([APAR
1].Group)="Westinghouse Electric - Overall")) OR
((([APAR 1].Client)="Whitbread") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Woolworths") AND (([APAR 1].Group)="South Africa")) OR
((([APAR 1].Client)="Zuger KB") AND (([APAR 1].Group)="Switzerland"))
ORDER BY [APAR 1].Client, [APAR 1].Group;

Guest · Mar 23, 2006

Congradulations. I've never seen such a query. It has over 16000 characters!
Multiple combinations or AND and OR statements. Plus a structure that is
obviously not normalized. I tell my students that just because you have have
64 characters in a field name, you shouldn't.

Below are some limits to a query. I'm thinking that you have violated at
least one, possibly the second or forth from the bottom.

Query Attribute Maximum
Number of enforced relationships 32 per table minus the number of indexes
that are on the table for fields or combinations of fields that are not
involved in relationships
Number of tables in a query 32
Number of fields in a recordset 255
Recordset size 1 gigabyte
Sort limit 255 characters in one or more fields
Number of levels of nested queries 50
Number of characters in a cell in the query design grid 1,024
Number of characters for a parameter in a parameter query 255
Number of ANDs in a WHERE or HAVING clause 99
Number of characters in an SQL statement 64000

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Bev said:
I have a query that produces 1000's of rows of data and I need to create
several smaller queries by indicating specific criteria. The first criteria
will be the "month" and this changes everytime I run the query. Then the
rows need to match 2 different field names. This is what I have and it is
giving me the error "Query is too complex" Any ideas?

SELECT [APAR 1].Month, [APAR 1].Client, [APAR 1].Group, [APAR 1].Region,
[APAR 1].Referenceable, [APAR 1].[Client Name], [APAR 1].[Client Position],
[APAR 1].[Interviewer Name], [APAR 1].[Scope of Services], [APAR 1].[CSC
Meets Overall Service Delivery Requirements], [APAR 1].[Mainframe service],
[APAR 1].[Midrange Service], [APAR 1].[Desktop/Laptop Service], [APAR
1].[Network Services], [APAR 1].[CSS Helpdesk], [APAR 1].[Collaborative
Computing], [APAR 1].Security, [APAR 1].[Distributed print/file servers],
[APAR 1].[Business Continuity], [APAR 1].[Project Management], [APAR
1].Applications, [APAR 1].[CSC delivers services within financial
expectations], [APAR 1].[CSC delivers services within expected schedule],
[APAR 2].[CSC service facilitates effective and efficient operation], [APAR
2].[CSC is seen as an innovator in our Partnership exploring], [APAR 2].[CSC
provides creative solutions to my business problems], [APAR 2].[Demonstrate
teamwork and collaboration], [APAR 2].[Proactively communicate pertinent
information], [APAR 2].[Are flexible in adapting to my changing business
priorities], [APAR 2].[Understand my objectives and goals], [APAR 2].[Key
Initiative 1], [APAR 2].[Status 1], [APAR 2].[Key Initiative 2], [APAR
2].[Status 2], [APAR 2].[Key Initiative 3], [APAR 2].[Status 3], [APAR
2].[Key Initiative 4], [APAR 2].[Status 4], [APAR 2].[Key Initiative 5],
[APAR 2].[Status 5], [APAR 2].[Key Initiative 6], [APAR 2].[Status 6], [APAR
2].[Key Initiative 7], [APAR 2].[Status 7], [APAR 2].[Key Initiative 8],
[APAR 2].[Status 8]
FROM [APAR 1] INNER JOIN [APAR 2] ON ([APAR 1].Group=[APAR 2].Group) AND
([APAR 1].Client=[APAR 2].Client) AND ([APAR 1].Month=[APAR 2].Month)
WHERE ((([APAR 1].Month)=[What month would you like the results for?])) And
((([APAR 1].Client)="Aerostructures") AND (([APAR 1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="Agency for Govt Mgmt") AND (([APAR
1].Group)="Denmark")) OR
((([APAR 1].Client)="Air Systems") AND (([APAR 1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="Aircraft Services Group") AND (([APAR 1].Group)="BAE
SYSTEMS")) OR
((([APAR 1].Client)="Albert Heijn") AND (([APAR 1].Group)="Netherlands")) OR
((([APAR 1].Client)="Alka") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Allianz") AND (([APAR 1].Group)="Allianz")) OR
((([APAR 1].Client)="AMP") AND (([APAR 1].Group)="Customer Service")) OR
((([APAR 1].Client)="AMP") AND (([APAR 1].Group)="Product Manufacturing")) OR
((([APAR 1].Client)="Anglian Water") AND (([APAR 1].Group)="UK Commercial"))
OR
((([APAR 1].Client)="AP-Pension") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="BAE Systems") AND (([APAR 1].Group)="BAE Systems
IEWS")) OR
((([APAR 1].Client)="Baker & Taylor") AND (([APAR 1].Group)="Baker & Taylor
- Overall")) OR
((([APAR 1].Client)="Bank Insinger de Beaufort") AND (([APAR
1].Group)="Netherlands")) OR
((([APAR 1].Client)="BankensBetalingssentral") AND (([APAR
1].Group)="Norway")) OR
((([APAR 1].Client)="Basell") AND (([APAR 1].Group)="Basell - Overall")) OR
((([APAR 1].Client)="Basell") AND (([APAR 1].Group)="Global Accounts")) OR
((([APAR 1].Client)="Belron") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton -
GSAP Services")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton Base
Metals - Cannington")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton Base
Metals - Cerro Colorado")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton Base
Metals - Chile Inc")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton Base
Metals - Global")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton Base
Metals - Minera Escondida")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton Base
Metals - Tintaya")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Carbon Steel Materials - BMA Coal")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Carbon Steel Materials - Collieries")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Carbon Steel Materials - Iron Ore")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Corporate - London")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Enterprise Architecture")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Petrolem - Global")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Petroleum - Australia")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Petroleum - Houston")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Petroleum - London")) OR
((([APAR 1].Client)="BHS") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="BlueScope Steel") AND (([APAR 1].Group)="Bluescope
Steel - Global")) OR
((([APAR 1].Client)="BNFL") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Bombardier Transportation") AND (([APAR
1].Group)="Global Accounts")) OR
((([APAR 1].Client)="BP") AND (([APAR 1].Group)="Austria")) OR
((([APAR 1].Client)="bp") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Capita") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Cluster 3") AND (([APAR 1].Group)="AEC")) OR
((([APAR 1].Client)="Codan") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="County of San Diego") AND (([APAR 1].Group)="CoSD -
Overall")) OR
((([APAR 1].Client)="CPR") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="CSC - Asia") AND (([APAR 1].Group)="CSC Internal
Systems")) OR
((([APAR 1].Client)="CSC - Australia") AND (([APAR 1].Group)="CSC Internal
Systems")) OR
((([APAR 1].Client)="CSC - Consulting") AND (([APAR 1].Group)="CSC Internal
Systems")) OR
((([APAR 1].Client)="CSC - Corporate Shared Services") AND (([APAR
1].Group)="CSC Internal Systems")) OR
((([APAR 1].Client)="CSC - EMEA") AND (([APAR 1].Group)="CSC - Internal")) OR
((([APAR 1].Client)="CSC - EMEA Central") AND (([APAR 1].Group)="CSC -
Internal")) OR
((([APAR 1].Client)="CSC - EMEA Nordic") AND (([APAR 1].Group)="CSC -
Internal")) OR
((([APAR 1].Client)="CSC - EMEA North") AND (([APAR 1].Group)="CSC -
Internal")) OR
((([APAR 1].Client)="CSC - EMEA South") AND (([APAR 1].Group)="CSC -
Internal")) OR
((([APAR 1].Client)="CSC - EMEA West") AND (([APAR 1].Group)="CSC -
Internal")) OR
((([APAR 1].Client)="CSC - Federal Sector") AND (([APAR 1].Group)="CSC
Internal Systems")) OR
((([APAR 1].Client)="CSC - FSG Banking Solutions") AND (([APAR
1].Group)="CSC Internal Systems")) OR
((([APAR 1].Client)="CSC - FSG Global Support") AND (([APAR 1].Group)="CSC
Internal Systems")) OR
((([APAR 1].Client)="CSC - FSG Life and Annuities") AND (([APAR
1].Group)="CSC Internal Systems")) OR
((([APAR 1].Client)="CSC - FSG Property and Casualty") AND (([APAR
1].Group)="CSC Internal Systems")) OR
((([APAR 1].Client)="CSC - India") AND (([APAR 1].Group)="CSC Internal
Systems")) OR
((([APAR 1].Client)="CSC - SUN") AND (([APAR 1].Group)="CSC Internal
Systems")) OR
((([APAR 1].Client)="Customer Solutions & Support") AND (([APAR
1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="Customs & Tax") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Danisco") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Danish Police") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Delphi") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Dept of Health") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="DLR") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="DNB Finans") AND (([APAR 1].Group)="Norway")) OR
((([APAR 1].Client)="DNB Kort") AND (([APAR 1].Group)="Norway")) OR
((([APAR 1].Client)="Dun & Bradstreet") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="DuPont") AND (([APAR 1].Group)="DuPont - Overall")) OR
((([APAR 1].Client)="DuPont") AND (([APAR 1].Group)="DuPont - Performance
Coatings")) OR
((([APAR 1].Client)="DuPont") AND (([APAR 1].Group)="Global Accounts")) OR
((([APAR 1].Client)="Elidis") AND (([APAR 1].Group)="France")) OR
((([APAR 1].Client)="Eurofighter") AND (([APAR 1].Group)="Germany")) OR
((([APAR 1].Client)="F van Lanschot Bankiers") AND (([APAR
1].Group)="Belgium")) OR
((([APAR 1].Client)="Falck") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="FNAC") AND (([APAR 1].Group)="France")) OR
((([APAR 1].Client)="Ford") AND (([APAR 1].Group)="Germany")) OR
((([APAR 1].Client)="Frequentis") AND (([APAR 1].Group)="Austria")) OR
((([APAR 1].Client)="FSG - City of Westminster Assurance") AND (([APAR
1].Group)="UK Commercial")) OR
((([APAR 1].Client)="FSG - Royal London") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="FSG - Stonebridge") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="FSG - Swiss Re") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="Gallaher") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="GE") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="Canada")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="Gen Dyn -
Bath Iron Works")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="Gen Dyn -
Corporate/SRI")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="Gen Dyn -
Electric Boat")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="Gen Dyn -
GDAIS")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="Gen Dyn -
GDATP")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="Gen Dyn -
Network Systems")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="General
Dynamics - Overall")) OR
((([APAR 1].Client)="GEPS Nuovo Pignone") AND (([APAR 1].Group)="Italy")) OR
((([APAR 1].Client)="GHI") AND (([APAR 1].Group)="GHI - Overall")) OR
((([APAR 1].Client)="Global Collect") AND (([APAR 1].Group)="Netherlands")) OR
((([APAR 1].Client)="Goodrich") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Goteborgs Sparvagar") AND (([APAR 1].Group)="Sweden")) OR
((([APAR 1].Client)="Gripen International") AND (([APAR 1].Group)="Sweden"))
OR
((([APAR 1].Client)="Grundfos") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="GTI") AND (([APAR 1].Group)="Netherlands")) OR
((([APAR 1].Client)="Haldor Topsoe") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Head Office & Shared Services") AND (([APAR
1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="HPC 97") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Hyatt Corporation") AND (([APAR 1].Group)="Hyatt
Corporation - Overall")) OR
((([APAR 1].Client)="ICI") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Insyte") AND (([APAR 1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="Interbrew") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Interpolis") AND (([APAR 1].Group)="Netherlands")) OR
((([APAR 1].Client)="ISS") AND (([APAR 1].Group)="Global Accounts")) OR
((([APAR 1].Client)="IT-Papier") AND (([APAR 1].Group)="Austria")) OR
((([APAR 1].Client)="KLP") AND (([APAR 1].Group)="Norway")) OR
((([APAR 1].Client)="Land Registry") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Land Rover") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Landstinget I Ostergotland") AND (([APAR
1].Group)="Sweden")) OR
((([APAR 1].Client)="Marconi") AND (([APAR 1].Group)="Global Accounts")) OR
((([APAR 1].Client)="Marconi") AND (([APAR 1].Group)="Marconi - Americas")) OR
((([APAR 1].Client)="MBDA") AND (([APAR 1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="Merlot - Nedbank") AND (([APAR 1].Group)="South
Africa")) OR
((([APAR 1].Client)="Merlot - Old Mutual") AND (([APAR 1].Group)="South
Africa")) OR
((([APAR 1].Client)="Motorola") AND (([APAR 1].Group)="Global Accounts")) OR
((([APAR 1].Client)="MRV") AND (([APAR 1].Group)="Germany")) OR
((([APAR 1].Client)="National Grid") AND (([APAR 1].Group)="UK Commercial"))
OR
((([APAR 1].Client)="Naval Ships") AND (([APAR 1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="NHS Birmingham and the Black Country SHA") AND (([APAR
1].Group)="NHS Overall")) OR
((([APAR 1].Client)="NHS Cheshire and Merseyside SHA") AND (([APAR
1].Group)="NHS Overall")) OR
((([APAR 1].Client)="NHS Cumbria and Lancashire SHA") AND (([APAR
1].Group)="NHS Overall")) OR
((([APAR 1].Client)="NHS Greater Manchester SHA") AND (([APAR 1].Group)="NHS
Overall")) OR
((([APAR 1].Client)="NHS NPfIT") AND (([APAR 1].Group)="NHS Overall")) OR
((([APAR 1].Client)="NHS Shropshire and Staffordshire SHA") AND (([APAR
1].Group)="NHS Overall")) OR
((([APAR 1].Client)="NHS West Midlands South SHA") AND (([APAR
1].Group)="NHS Overall")) OR
((([APAR 1].Client)="NKT") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Nordea Liv Norge AS") AND (([APAR 1].Group)="Norway")) OR
((([APAR 1].Client)="NS") AND (([APAR 1].Group)="Netherlands")) OR
((([APAR 1].Client)="Nynashamns Kommun") AND (([APAR 1].Group)="Sweden")) OR
((([APAR 1].Client)="oest. Staatsdruckerei") AND (([APAR
1].Group)="Austria")) OR
((([APAR 1].Client)="Old Mutual") AND (([APAR 1].Group)="South Africa")) OR
((([APAR 1].Client)="Panavia") AND (([APAR 1].Group)="Germany")) OR
((([APAR 1].Client)="PFA Pension") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Pfizer") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Platform Solutions(Avionics)") AND (([APAR
1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="Quaker Chemical") AND (([APAR 1].Group)="Netherlands"))
OR
((([APAR 1].Client)="Quest") AND (([APAR 1].Group)="Netherlands")) OR
((([APAR 1].Client)="Renault") AND (([APAR 1].Group)="France")) OR
((([APAR 1].Client)="Reynolds & Reynolds") AND (([APAR 1].Group)="Reynolds &
Reynolds - Overall")) OR
((([APAR 1].Client)="RMG - Enterprise") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="RMG - Logistics & Transport") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="RMG - Parcelforce Worldwide") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="RMG - POL") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="RMG - RM-International") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="RMG - RM-UK") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Royal Ordnance Defence") AND (([APAR 1].Group)="BAE
SYSTEMS")) OR
((([APAR 1].Client)="Saab Aerospace AB") AND (([APAR 1].Group)="Sweden")) OR
((([APAR 1].Client)="Saab Rosemount") AND (([APAR 1].Group)="Sweden")) OR
((([APAR 1].Client)="SAS - Cargo") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS - CIO Function/Headoffice") AND (([APAR
1].Group)="SAS")) OR
((([APAR 1].Client)="SAS - Corporate (Finance)") AND (([APAR
1].Group)="SAS")) OR
((([APAR 1].Client)="SAS - SGS Ground Services") AND (([APAR
1].Group)="SAS")) OR
((([APAR 1].Client)="SAS - STS Technical Services") AND (([APAR
1].Group)="SAS")) OR
((([APAR 1].Client)="SAS Airline IT") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS Braathens") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS Corp. HR") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS DK") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS EAG") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS Production systems") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS SE") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS/STOOM") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SBI") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Scandihealth") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Scapa") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Schroders") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="SELEX S&AS") AND (([APAR 1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="Siemens Westinghouse Power Corp") AND (([APAR
1].Group)="Siemens Westinghouse - Overall")) OR
((([APAR 1].Client)="Sodertalje Kommun") AND (([APAR 1].Group)="Sweden")) OR
((([APAR 1].Client)="Sparebank1") AND (([APAR 1].Group)="Norway")) OR
((([APAR 1].Client)="St. Vincent Catholic Medical Center") AND (([APAR
1].Group)="St. Vincent - Overall")) OR
((([APAR 1].Client)="Standard Register Co.") AND (([APAR 1].Group)="Standard
Register - Overall")) OR
((([APAR 1].Client)="Statoil") AND (([APAR 1].Group)="Norway")) OR
((([APAR 1].Client)="Submarines") AND (([APAR 1].Group)="BAE SYSTEMS")) OR

John Spencer · Mar 23, 2006

I would simplify this whole thing by creating a new table of allowable Client
and Group combinations and then including that in the query as an additional
table in the join. That would sure make life easier when you need to add a new
combination or remove a client group combination. Rewriting your query using
aliases for the table names and including the "new" table, you end up with the
following query.

SELECT A.Month, A.Client, A.Group, A.Region,
A.Referenceable, A.[Client Name], A.[Client Position],
A.[Interviewer Name], A.[Scope of Services],
A.[CSC Meets Overall Service Delivery Requirements], A.[Mainframe service],
A.[Midrange Service], A.[Desktop/Laptop Service], A.[Network Services],
A.[CSS Helpdesk], A.[Collaborative Computing], A.Security,
A.[Distributed print/file servers],
A.[Business Continuity], A.[Project Management], A.Applications,
A.[CSC delivers services within financial expectations],
A.[CSC delivers services within expected schedule],
A2.[CSC service facilitates effective and efficient operation],
A2.[CSC is seen as an innovator in our Partnership exploring],
A2.[CSC provides creative solutions to my business problems],
A2.[Demonstrate teamwork and collaboration],
A2.[Proactively communicate pertinent information],
A2.[Are flexible in adapting to my changing business priorities],
A2.[Understand my objectives and goals],
A2.[Key Initiative 1], A2.[Status 1], A2.[Key Initiative 2], A2.[Status 2],
A2.[Key Initiative 3], A2.[Status 3], A2.[Key Initiative 4], A2.[Status 4],
A2.[Key Initiative 5], A2.[Status 5], A2.[Key Initiative 6], A2.[Status 6],
A2.[Key Initiative 7], A2.[Status 7], A2.[Key Initiative 8],
A2.[Status 8]
FROM ([Apar 1] as A Inner Join [TheNewTable] as N
ON A.Client = N.Client and A.Group = N.Group)
INNER JOIN [Apar 2] as A2
ON A.Client = A2.Client AND A.Group = A2.Group AND A.Month = A2.Month
WHERE A1.Month = [What month would you like the results for?]
ORDER BY A.Client, A.Group

I have a query that produces 1000's of rows of data and I need to create
several smaller queries by indicating specific criteria. The first criteria
will be the "month" and this changes everytime I run the query. Then the
rows need to match 2 different field names. This is what I have and it is
giving me the error "Query is too complex" Any ideas?

SELECT [APAR 1].Month, [APAR 1].Client, [APAR 1].Group, [APAR 1].Region,
[APAR 1].Referenceable, [APAR 1].[Client Name], [APAR 1].[Client Position],
[APAR 1].[Interviewer Name], [APAR 1].[Scope of Services], [APAR 1].[CSC
Meets Overall Service Delivery Requirements], [APAR 1].[Mainframe service],
[APAR 1].[Midrange Service], [APAR 1].[Desktop/Laptop Service], [APAR
1].[Network Services], [APAR 1].[CSS Helpdesk], [APAR 1].[Collaborative
Computing], [APAR 1].Security, [APAR 1].[Distributed print/file servers],
[APAR 1].[Business Continuity], [APAR 1].[Project Management], [APAR
1].Applications, [APAR 1].[CSC delivers services within financial
expectations], [APAR 1].[CSC delivers services within expected schedule],
[APAR 2].[CSC service facilitates effective and efficient operation], [APAR
2].[CSC is seen as an innovator in our Partnership exploring], [APAR 2].[CSC
provides creative solutions to my business problems], [APAR 2].[Demonstrate
teamwork and collaboration], [APAR 2].[Proactively communicate pertinent
information], [APAR 2].[Are flexible in adapting to my changing business
priorities], [APAR 2].[Understand my objectives and goals], [APAR 2].[Key
Initiative 1], [APAR 2].[Status 1], [APAR 2].[Key Initiative 2], [APAR
2].[Status 2], [APAR 2].[Key Initiative 3], [APAR 2].[Status 3], [APAR
2].[Key Initiative 4], [APAR 2].[Status 4], [APAR 2].[Key Initiative 5],
[APAR 2].[Status 5], [APAR 2].[Key Initiative 6], [APAR 2].[Status 6], [APAR
2].[Key Initiative 7], [APAR 2].[Status 7], [APAR 2].[Key Initiative 8],
[APAR 2].[Status 8]
FROM [APAR 1] INNER JOIN [APAR 2] ON ([APAR 1].Group=[APAR 2].Group) AND
([APAR 1].Client=[APAR 2].Client) AND ([APAR 1].Month=[APAR 2].Month)
WHERE ((([APAR 1].Month)=[What month would you like the results for?])) And
((([APAR 1].Client)="Aerostructures") AND (([APAR 1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="Agency for Govt Mgmt") AND (([APAR
1].Group)="Denmark")) OR
((([APAR 1].Client)="Air Systems") AND (([APAR 1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="Aircraft Services Group") AND (([APAR 1].Group)="BAE
SYSTEMS")) OR
((([APAR 1].Client)="Albert Heijn") AND (([APAR 1].Group)="Netherlands")) OR
((([APAR 1].Client)="Alka") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Allianz") AND (([APAR 1].Group)="Allianz")) OR
((([APAR 1].Client)="AMP") AND (([APAR 1].Group)="Customer Service")) OR
((([APAR 1].Client)="AMP") AND (([APAR 1].Group)="Product Manufacturing")) OR
((([APAR 1].Client)="Anglian Water") AND (([APAR 1].Group)="UK Commercial"))
OR
((([APAR 1].Client)="AP-Pension") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="BAE Systems") AND (([APAR 1].Group)="BAE Systems
IEWS")) OR
((([APAR 1].Client)="Baker & Taylor") AND (([APAR 1].Group)="Baker & Taylor
- Overall")) OR
((([APAR 1].Client)="Bank Insinger de Beaufort") AND (([APAR
1].Group)="Netherlands")) OR
((([APAR 1].Client)="BankensBetalingssentral") AND (([APAR
1].Group)="Norway")) OR
((([APAR 1].Client)="Basell") AND (([APAR 1].Group)="Basell - Overall")) OR
((([APAR 1].Client)="Basell") AND (([APAR 1].Group)="Global Accounts")) OR
((([APAR 1].Client)="Belron") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton -
GSAP Services")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton Base
Metals - Cannington")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton Base
Metals - Cerro Colorado")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton Base
Metals - Chile Inc")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton Base
Metals - Global")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton Base
Metals - Minera Escondida")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton Base
Metals - Tintaya")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Carbon Steel Materials - BMA Coal")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Carbon Steel Materials - Collieries")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Carbon Steel Materials - Iron Ore")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Corporate - London")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Enterprise Architecture")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Petrolem - Global")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Petroleum - Australia")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Petroleum - Houston")) OR
((([APAR 1].Client)="BHP Billiton") AND (([APAR 1].Group)="BHP Billiton
Petroleum - London")) OR
((([APAR 1].Client)="BHS") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="BlueScope Steel") AND (([APAR 1].Group)="Bluescope
Steel - Global")) OR
((([APAR 1].Client)="BNFL") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Bombardier Transportation") AND (([APAR
1].Group)="Global Accounts")) OR
((([APAR 1].Client)="BP") AND (([APAR 1].Group)="Austria")) OR
((([APAR 1].Client)="bp") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Capita") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Cluster 3") AND (([APAR 1].Group)="AEC")) OR
((([APAR 1].Client)="Codan") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="County of San Diego") AND (([APAR 1].Group)="CoSD -
Overall")) OR
((([APAR 1].Client)="CPR") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="CSC - Asia") AND (([APAR 1].Group)="CSC Internal
Systems")) OR
((([APAR 1].Client)="CSC - Australia") AND (([APAR 1].Group)="CSC Internal
Systems")) OR
((([APAR 1].Client)="CSC - Consulting") AND (([APAR 1].Group)="CSC Internal
Systems")) OR
((([APAR 1].Client)="CSC - Corporate Shared Services") AND (([APAR
1].Group)="CSC Internal Systems")) OR
((([APAR 1].Client)="CSC - EMEA") AND (([APAR 1].Group)="CSC - Internal")) OR
((([APAR 1].Client)="CSC - EMEA Central") AND (([APAR 1].Group)="CSC -
Internal")) OR
((([APAR 1].Client)="CSC - EMEA Nordic") AND (([APAR 1].Group)="CSC -
Internal")) OR
((([APAR 1].Client)="CSC - EMEA North") AND (([APAR 1].Group)="CSC -
Internal")) OR
((([APAR 1].Client)="CSC - EMEA South") AND (([APAR 1].Group)="CSC -
Internal")) OR
((([APAR 1].Client)="CSC - EMEA West") AND (([APAR 1].Group)="CSC -
Internal")) OR
((([APAR 1].Client)="CSC - Federal Sector") AND (([APAR 1].Group)="CSC
Internal Systems")) OR
((([APAR 1].Client)="CSC - FSG Banking Solutions") AND (([APAR
1].Group)="CSC Internal Systems")) OR
((([APAR 1].Client)="CSC - FSG Global Support") AND (([APAR 1].Group)="CSC
Internal Systems")) OR
((([APAR 1].Client)="CSC - FSG Life and Annuities") AND (([APAR
1].Group)="CSC Internal Systems")) OR
((([APAR 1].Client)="CSC - FSG Property and Casualty") AND (([APAR
1].Group)="CSC Internal Systems")) OR
((([APAR 1].Client)="CSC - India") AND (([APAR 1].Group)="CSC Internal
Systems")) OR
((([APAR 1].Client)="CSC - SUN") AND (([APAR 1].Group)="CSC Internal
Systems")) OR
((([APAR 1].Client)="Customer Solutions & Support") AND (([APAR
1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="Customs & Tax") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Danisco") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Danish Police") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Delphi") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Dept of Health") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="DLR") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="DNB Finans") AND (([APAR 1].Group)="Norway")) OR
((([APAR 1].Client)="DNB Kort") AND (([APAR 1].Group)="Norway")) OR
((([APAR 1].Client)="Dun & Bradstreet") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="DuPont") AND (([APAR 1].Group)="DuPont - Overall")) OR
((([APAR 1].Client)="DuPont") AND (([APAR 1].Group)="DuPont - Performance
Coatings")) OR
((([APAR 1].Client)="DuPont") AND (([APAR 1].Group)="Global Accounts")) OR
((([APAR 1].Client)="Elidis") AND (([APAR 1].Group)="France")) OR
((([APAR 1].Client)="Eurofighter") AND (([APAR 1].Group)="Germany")) OR
((([APAR 1].Client)="F van Lanschot Bankiers") AND (([APAR
1].Group)="Belgium")) OR
((([APAR 1].Client)="Falck") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="FNAC") AND (([APAR 1].Group)="France")) OR
((([APAR 1].Client)="Ford") AND (([APAR 1].Group)="Germany")) OR
((([APAR 1].Client)="Frequentis") AND (([APAR 1].Group)="Austria")) OR
((([APAR 1].Client)="FSG - City of Westminster Assurance") AND (([APAR
1].Group)="UK Commercial")) OR
((([APAR 1].Client)="FSG - Royal London") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="FSG - Stonebridge") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="FSG - Swiss Re") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="Gallaher") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="GE") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="Canada")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="Gen Dyn -
Bath Iron Works")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="Gen Dyn -
Corporate/SRI")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="Gen Dyn -
Electric Boat")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="Gen Dyn -
GDAIS")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="Gen Dyn -
GDATP")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="Gen Dyn -
Network Systems")) OR
((([APAR 1].Client)="General Dynamics") AND (([APAR 1].Group)="General
Dynamics - Overall")) OR
((([APAR 1].Client)="GEPS Nuovo Pignone") AND (([APAR 1].Group)="Italy")) OR
((([APAR 1].Client)="GHI") AND (([APAR 1].Group)="GHI - Overall")) OR
((([APAR 1].Client)="Global Collect") AND (([APAR 1].Group)="Netherlands")) OR
((([APAR 1].Client)="Goodrich") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Goteborgs Sparvagar") AND (([APAR 1].Group)="Sweden")) OR
((([APAR 1].Client)="Gripen International") AND (([APAR 1].Group)="Sweden"))
OR
((([APAR 1].Client)="Grundfos") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="GTI") AND (([APAR 1].Group)="Netherlands")) OR
((([APAR 1].Client)="Haldor Topsoe") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Head Office & Shared Services") AND (([APAR
1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="HPC 97") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Hyatt Corporation") AND (([APAR 1].Group)="Hyatt
Corporation - Overall")) OR
((([APAR 1].Client)="ICI") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Insyte") AND (([APAR 1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="Interbrew") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Interpolis") AND (([APAR 1].Group)="Netherlands")) OR
((([APAR 1].Client)="ISS") AND (([APAR 1].Group)="Global Accounts")) OR
((([APAR 1].Client)="IT-Papier") AND (([APAR 1].Group)="Austria")) OR
((([APAR 1].Client)="KLP") AND (([APAR 1].Group)="Norway")) OR
((([APAR 1].Client)="Land Registry") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Land Rover") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Landstinget I Ostergotland") AND (([APAR
1].Group)="Sweden")) OR
((([APAR 1].Client)="Marconi") AND (([APAR 1].Group)="Global Accounts")) OR
((([APAR 1].Client)="Marconi") AND (([APAR 1].Group)="Marconi - Americas")) OR
((([APAR 1].Client)="MBDA") AND (([APAR 1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="Merlot - Nedbank") AND (([APAR 1].Group)="South
Africa")) OR
((([APAR 1].Client)="Merlot - Old Mutual") AND (([APAR 1].Group)="South
Africa")) OR
((([APAR 1].Client)="Motorola") AND (([APAR 1].Group)="Global Accounts")) OR
((([APAR 1].Client)="MRV") AND (([APAR 1].Group)="Germany")) OR
((([APAR 1].Client)="National Grid") AND (([APAR 1].Group)="UK Commercial"))
OR
((([APAR 1].Client)="Naval Ships") AND (([APAR 1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="NHS Birmingham and the Black Country SHA") AND (([APAR
1].Group)="NHS Overall")) OR
((([APAR 1].Client)="NHS Cheshire and Merseyside SHA") AND (([APAR
1].Group)="NHS Overall")) OR
((([APAR 1].Client)="NHS Cumbria and Lancashire SHA") AND (([APAR
1].Group)="NHS Overall")) OR
((([APAR 1].Client)="NHS Greater Manchester SHA") AND (([APAR 1].Group)="NHS
Overall")) OR
((([APAR 1].Client)="NHS NPfIT") AND (([APAR 1].Group)="NHS Overall")) OR
((([APAR 1].Client)="NHS Shropshire and Staffordshire SHA") AND (([APAR
1].Group)="NHS Overall")) OR
((([APAR 1].Client)="NHS West Midlands South SHA") AND (([APAR
1].Group)="NHS Overall")) OR
((([APAR 1].Client)="NKT") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Nordea Liv Norge AS") AND (([APAR 1].Group)="Norway")) OR
((([APAR 1].Client)="NS") AND (([APAR 1].Group)="Netherlands")) OR
((([APAR 1].Client)="Nynashamns Kommun") AND (([APAR 1].Group)="Sweden")) OR
((([APAR 1].Client)="oest. Staatsdruckerei") AND (([APAR
1].Group)="Austria")) OR
((([APAR 1].Client)="Old Mutual") AND (([APAR 1].Group)="South Africa")) OR
((([APAR 1].Client)="Panavia") AND (([APAR 1].Group)="Germany")) OR
((([APAR 1].Client)="PFA Pension") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Pfizer") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Platform Solutions(Avionics)") AND (([APAR
1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="Quaker Chemical") AND (([APAR 1].Group)="Netherlands"))
OR
((([APAR 1].Client)="Quest") AND (([APAR 1].Group)="Netherlands")) OR
((([APAR 1].Client)="Renault") AND (([APAR 1].Group)="France")) OR
((([APAR 1].Client)="Reynolds & Reynolds") AND (([APAR 1].Group)="Reynolds &
Reynolds - Overall")) OR
((([APAR 1].Client)="RMG - Enterprise") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="RMG - Logistics & Transport") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="RMG - Parcelforce Worldwide") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="RMG - POL") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="RMG - RM-International") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="RMG - RM-UK") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Royal Ordnance Defence") AND (([APAR 1].Group)="BAE
SYSTEMS")) OR
((([APAR 1].Client)="Saab Aerospace AB") AND (([APAR 1].Group)="Sweden")) OR
((([APAR 1].Client)="Saab Rosemount") AND (([APAR 1].Group)="Sweden")) OR
((([APAR 1].Client)="SAS - Cargo") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS - CIO Function/Headoffice") AND (([APAR
1].Group)="SAS")) OR
((([APAR 1].Client)="SAS - Corporate (Finance)") AND (([APAR
1].Group)="SAS")) OR
((([APAR 1].Client)="SAS - SGS Ground Services") AND (([APAR
1].Group)="SAS")) OR
((([APAR 1].Client)="SAS - STS Technical Services") AND (([APAR
1].Group)="SAS")) OR
((([APAR 1].Client)="SAS Airline IT") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS Braathens") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS Corp. HR") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS DK") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS EAG") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS Production systems") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS SE") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SAS/STOOM") AND (([APAR 1].Group)="SAS")) OR
((([APAR 1].Client)="SBI") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Scandihealth") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Scapa") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Schroders") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="SELEX S&AS") AND (([APAR 1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="Siemens Westinghouse Power Corp") AND (([APAR
1].Group)="Siemens Westinghouse - Overall")) OR
((([APAR 1].Client)="Sodertalje Kommun") AND (([APAR 1].Group)="Sweden")) OR
((([APAR 1].Client)="Sparebank1") AND (([APAR 1].Group)="Norway")) OR
((([APAR 1].Client)="St. Vincent Catholic Medical Center") AND (([APAR
1].Group)="St. Vincent - Overall")) OR
((([APAR 1].Client)="Standard Register Co.") AND (([APAR 1].Group)="Standard
Register - Overall")) OR
((([APAR 1].Client)="Statoil") AND (([APAR 1].Group)="Norway")) OR
((([APAR 1].Client)="Submarines") AND (([APAR 1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="SUN") AND (([APAR 1].Group)="CSC - Internal")) OR
((([APAR 1].Client)="Sun Microsystems Inc.") AND (([APAR 1].Group)="Sun -
Overall")) OR
((([APAR 1].Client)="Sydkraft") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="TDC") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Techspace Aero") AND (([APAR 1].Group)="Belgium")) OR
((([APAR 1].Client)="Textron") AND (([APAR 1].Group)="Global Accounts")) OR
((([APAR 1].Client)="Textron") AND (([APAR 1].Group)="Textron - Bell
Helicopters")) OR
((([APAR 1].Client)="Textron") AND (([APAR 1].Group)="Textron - Cessna")) OR
((([APAR 1].Client)="Textron") AND (([APAR 1].Group)="Textron - Financial"))
OR
((([APAR 1].Client)="Textron") AND (([APAR 1].Group)="Textron - Systems")) OR
((([APAR 1].Client)="Truworths") AND (([APAR 1].Group)="South Africa")) OR
((([APAR 1].Client)="TRYG") AND (([APAR 1].Group)="Denmark")) OR
((([APAR 1].Client)="Unisys Payment Services & Solutions") AND (([APAR
1].Group)="Netherlands")) OR
((([APAR 1].Client)="United Technology Corp.") AND (([APAR 1].Group)="UTC -
Overall")) OR
((([APAR 1].Client)="United Technology Corp.") AND (([APAR 1].Group)="UTC -
Sikorsky")) OR
((([APAR 1].Client)="Universal Music Industries") AND (([APAR 1].Group)="UK
Commercial")) OR
((([APAR 1].Client)="UTC EMEA") AND (([APAR 1].Group)="Global Accounts")) OR
((([APAR 1].Client)="UWS") AND (([APAR 1].Group)="BAE SYSTEMS")) OR
((([APAR 1].Client)="V&S Vin & Sprit AB") AND (([APAR 1].Group)="Sweden")) OR
((([APAR 1].Client)="Vesta") AND (([APAR 1].Group)="Norway")) OR
((([APAR 1].Client)="Vital") AND (([APAR 1].Group)="Norway")) OR
((([APAR 1].Client)="Westinghouse Electric") AND (([APAR
1].Group)="Westinghouse Electric - Overall")) OR
((([APAR 1].Client)="Whitbread") AND (([APAR 1].Group)="UK Commercial")) OR
((([APAR 1].Client)="Woolworths") AND (([APAR 1].Group)="South Africa")) OR
((([APAR 1].Client)="Zuger KB") AND (([APAR 1].Group)="Switzerland"))
ORDER BY [APAR 1].Client, [APAR 1].Group;

Brian Wilson · Mar 23, 2006

Jerry Whittle said:
Congradulations. I've never seen such a query. It has over 16000
characters!
Multiple combinations or AND and OR statements. Plus a structure that is
obviously not normalized. I tell my students that just because you have
have
64 characters in a field name, you shouldn't.

<snip>

My first step would be to ensure this is not a hoax.

I suspect that by telling the Access community that you have a field named:

[APAR 2].[Are flexible in adapting to my changing business priorities]

the OP is simply trying to see how many Access Developers will drop dead -
either from a heart attack or from rolling around the floor with laughter -
and I suspect they don't care!

Guest · Mar 24, 2006

It's pretty obvious that the data is being put in the column name. It seems
to be a questionaire and not normalized properly. It looks like a criminal
case of commiting spreadsheet.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Brian Wilson said:
Jerry Whittle said:

Congradulations. I've never seen such a query. It has over 16000
characters!
Multiple combinations or AND and OR statements. Plus a structure that is
obviously not normalized. I tell my students that just because you have
have
64 characters in a field name, you shouldn't.

Click to expand...

<snip>

My first step would be to ensure this is not a hoax.

I suspect that by telling the Access community that you have a field named:

[APAR 2].[Are flexible in adapting to my changing business priorities]

the OP is simply trying to see how many Access Developers will drop dead -
either from a heart attack or from rolling around the floor with laughter -
and I suspect they don't care!

.

Brian Wilson · Mar 24, 2006

Jerry Whittle said:
It's pretty obvious that the data is being put in the column name. It
seems
to be a questionaire and not normalized properly. It looks like a criminal
case of commiting spreadsheet.

Agreed.

AlwaysFroosh! · Jan 25, 2008

I wonder if anyone of you is still watching this post, I guess this thread is
a couple years old now!

I too am having a problem with my query being too complex, maybe someone
could help me simplify it?

We use a database to house technical information on equipment. In this case,
the piece of equipment is a heat exchanger. To generate a datasheet for this
exchanger, I create a query that produces one record, the record of all the
information for that particular heat exchanger.

I use a series of what I think of as "piggy backing" queries. First I create
a recordset of the commodity properties that are coming into and going out of
the exchanger. Then I build another query using the commodities query and a
bunch of tables with data that is specific to that exchanger. Then I have
another query that numbers all of the fields so that I can backup all queries
for different equipment into a table with just numbers as the column names.
Finally my last query looks at the numbered query, and the backup table and
compare values so that the revisions can be flagged if there's a difference.

Here are the queries:

Commodity Query:
SELECT SOLODATA.SOLODATALINK, PRIM_IN_COMMONDATA.COMMODITY AS [PRIMIN
COMMODITY],
IIf([PRIM_IN_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([PRIM_IN_COMMONDATA].[MASS_FLOW_1],[PRIM_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([PRIM_IN_COMMONDATA].[VOL_FLOW_1],[PRIM_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [PRIMIN FLOW 1],
IIf([PRIM_IN_COMMONDATA].[VOL_FLOW_3]=0,NumberWithUnits([PRIM_IN_COMMONDATA].[MASS_FLOW_3],[PRIM_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([PRIM_IN_COMMONDATA].[VOL_FLOW_3],[PRIM_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [PRIMIN FLOW 2],
NumberWithUnits([PRIM_IN_COMMONDATA].[OPERTEMPERATURE],[PRIM_IN_COMMONDATA].[TEMPERATUREUNITS])
AS [PRIMIN OPERATING TEMPERATURE 1],
NumberWithUnits([PRIM_IN_COMMONDATA].[TEMPERATURE_3],[PRIM_IN_COMMONDATA].[TEMPERATUREUNITS])
AS [PRIMIN OPERATING TEMPERATURE 2],
NumberWithUnits([PRIM_IN_COMMONDATA].[OPERPRESSURE],[PRIM_IN_COMMONDATA].[PRESSUREUNITS])
AS [PRIM INLET PRESSURE 1],
NumberWithUnits([PRIM_IN_COMMONDATA].[PRESSURE_3],[PRIM_IN_COMMONDATA].[PRESSUREUNITS])
AS [PRIM INLET PRESSURE 2],
NumberWithUnits([PRIM_IN_COMMONDATA].[DENSITY_1],[PRIM_IN_COMMONDATA].[DENSITY_UNITS])
AS [PRIMIN DENSITY 1],
NumberWithUnits([PRIM_IN_COMMONDATA].[DENSITY_2],[PRIM_IN_COMMONDATA].[DENSITY_UNITS])
AS [PRIMIN DENSITY 2],
NumberWithUnits([PRIM_IN_COMMONDATA].[VISCOSITY_1],[PRIM_IN_COMMONDATA].[VISCOSITY_UNITS])
AS [PRIMIN VISCOSITY 1],
NumberWithUnits([PRIM_IN_COMMONDATA].[VISCOSITY_2],[PRIM_IN_COMMONDATA].[VISCOSITY_UNITS])
AS [PRIMIN VISCOSITY 2], PRIM_IN_COMMONDATA.CEXTA3 AS [PRIMIN SPEC HEAT 1],
PRIM_IN_COMMONDATA.CEXTA6 AS [PRIMIN SPEC HEAT 2], PRIM_IN_COMMONDATA.CEXTA5
AS [PRIMIN THERMAL COND 1], PRIM_IN_COMMONDATA.CEXTA8 AS [PRIMIN THERMAL COND
2], PRIM_OUT_COMMONDATA.COMMODITY AS [PRIMOUT COMMODITY],
IIf([PRIM_OUT_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([PRIM_OUT_COMMONDATA].[MASS_FLOW_1],[PRIM_OUT_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([PRIM_OUT_COMMONDATA].[VOL_FLOW_1],[PRIM_OUT_COMMONDATA].[VOL_FLOW_UNITS]))
AS [PRIMOUT FLOW 1],
IIf([PRIM_OUT_COMMONDATA].[VOL_FLOW_3]=0,NumberWithUnits([PRIM_OUT_COMMONDATA].[MASS_FLOW_3],[PRIM_OUT_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([PRIM_OUT_COMMONDATA].[VOL_FLOW_3],[PRIM_OUT_COMMONDATA].[VOL_FLOW_UNITS]))
AS [PRIMOUT FLOW 2],
NumberWithUnits([PRIM_OUT_COMMONDATA].[OPERTEMPERATURE],[PRIM_OUT_COMMONDATA].[TEMPERATUREUNITS])
AS [PRIMOUT OPERATING TEMPERATURE 1],
NumberWithUnits([PRIM_OUT_COMMONDATA].[TEMPERATURE_3],[PRIM_OUT_COMMONDATA].[TEMPERATUREUNITS])
AS [PRIMOUT OPERATING TEMPERATURE 2],
NumberWithUnits(CDbl(Nz([PRIM_OUT_COMMONDATA].[OPERPRESSURE],0))-CDbl(Nz([PRIM_IN_COMMONDATA].[OPERPRESSURE],0)),[PRIM_OUT_COMMONDATA].[PRESSUREUNITS])
AS [PRIM DIFFERENTIAL PRESSURE 1],
NumberWithUnits(CDbl(Nz([PRIM_OUT_COMMONDATA].[PRESSURE_3],0))-CDbl(Nz([PRIM_IN_COMMONDATA].[PRESSURE_3],0)),[PRIM_OUT_COMMONDATA].[PRESSUREUNITS])
AS [PRIM DIFFERENTIAL PRESSURE 2],
NumberWithUnits([PRIM_OUT_COMMONDATA].[DENSITY_1],[PRIM_OUT_COMMONDATA].[DENSITY_UNITS])
AS [PRIMOUT DENSITY 1],
NumberWithUnits([PRIM_OUT_COMMONDATA].[DENSITY_2],[PRIM_OUT_COMMONDATA].[DENSITY_UNITS])
AS [PRIMOUT DENSITY 2],
NumberWithUnits([PRIM_OUT_COMMONDATA].[VISCOSITY_1],[PRIM_OUT_COMMONDATA].[VISCOSITY_UNITS])
AS [PRIMOUT VISCOSITY 1],
NumberWithUnits([PRIM_OUT_COMMONDATA].[VISCOSITY_2],[PRIM_OUT_COMMONDATA].[VISCOSITY_UNITS])
AS [PRIMOUT VISCOSITY 2], PRIM_OUT_COMMONDATA.CEXTA3 AS [PRIMOUT SPEC HEAT
1], PRIM_OUT_COMMONDATA.CEXTA6 AS [PRIMOUT SPEC HEAT 2],
PRIM_OUT_COMMONDATA.CEXTA5 AS [PRIMOUT THERMAL COND 1],
PRIM_OUT_COMMONDATA.CEXTA8 AS [PRIMOUT THERMAL COND 2],
SEC_IN_COMMONDATA.COMMODITY AS [SECIN COMMODITY],
IIf([SEC_IN_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([SEC_IN_COMMONDATA].[MASS_FLOW_1],[SEC_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([SEC_IN_COMMONDATA].[VOL_FLOW_1],[SEC_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [SECIN FLOW 1],
IIf([SEC_IN_COMMONDATA].[VOL_FLOW_3]=0,NumberWithUnits([SEC_IN_COMMONDATA].[MASS_FLOW_3],[SEC_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([SEC_IN_COMMONDATA].[VOL_FLOW_3],[SEC_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [SECIN FLOW 2],
NumberWithUnits([SEC_IN_COMMONDATA].[OPERTEMPERATURE],[SEC_IN_COMMONDATA].[TEMPERATUREUNITS])
AS [SECIN OPERATING TEMPERATURE 1],
NumberWithUnits([SEC_IN_COMMONDATA].[TEMPERATURE_3],[SEC_IN_COMMONDATA].[TEMPERATUREUNITS])
AS [SECIN OPERATING TEMPERATURE 2],
NumberWithUnits([SEC_IN_COMMONDATA].[OPERPRESSURE],[SEC_IN_COMMONDATA].[PRESSUREUNITS])
AS [SEC INLET PRESSURE 1],
NumberWithUnits([SEC_IN_COMMONDATA].[PRESSURE_3],[SEC_IN_COMMONDATA].[PRESSUREUNITS])
AS [SEC INLET PRESSURE 2],
NumberWithUnits([SEC_IN_COMMONDATA].[DENSITY_1],[SEC_IN_COMMONDATA].[DENSITY_UNITS])
AS [SECIN DENSITY 1],
NumberWithUnits([SEC_IN_COMMONDATA].[DENSITY_2],[SEC_IN_COMMONDATA].[DENSITY_UNITS])
AS [SECIN DENSITY 2],
NumberWithUnits([SEC_IN_COMMONDATA].[VISCOSITY_1],[SEC_IN_COMMONDATA].[VISCOSITY_UNITS])
AS [SECIN VISCOSITY 1],
NumberWithUnits([SEC_IN_COMMONDATA].[VISCOSITY_2],[SEC_IN_COMMONDATA].[VISCOSITY_UNITS])
AS [SECIN VISCOSITY 2], SEC_IN_COMMONDATA.CEXTA3 AS [SECIN SPEC HEAT 1],
SEC_IN_COMMONDATA.CEXTA6 AS [SECIN SPEC HEAT 2], SEC_IN_COMMONDATA.CEXTA5 AS
[SECIN THERMAL COND 1], SEC_IN_COMMONDATA.CEXTA8 AS [SECIN THERMAL COND 2],
SEC_OUT_COMMONDATA.COMMODITY AS [SECOUT COMMODITY],
IIf([SEC_OUT_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([SEC_OUT_COMMONDATA].[MASS_FLOW_1],[SEC_OUT_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([SEC_OUT_COMMONDATA].[VOL_FLOW_1],[SEC_OUT_COMMONDATA].[VOL_FLOW_UNITS]))
AS [SECOUT FLOW 1],
IIf([SEC_OUT_COMMONDATA].[VOL_FLOW_3]=0,NumberWithUnits([SEC_OUT_COMMONDATA].[MASS_FLOW_3],[SEC_OUT_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([SEC_OUT_COMMONDATA].[VOL_FLOW_3],[SEC_OUT_COMMONDATA].[VOL_FLOW_UNITS]))
AS [SECOUT FLOW 2],
NumberWithUnits([SEC_OUT_COMMONDATA].[OPERTEMPERATURE],[SEC_OUT_COMMONDATA].[TEMPERATUREUNITS])
AS [SECOUT OPERATING TEMPERATURE 1],
NumberWithUnits([SEC_OUT_COMMONDATA].[TEMPERATURE_3],[SEC_OUT_COMMONDATA].[TEMPERATUREUNITS])
AS [SECOUT OPERATING TEMPERATURE 2],
NumberWithUnits(CDbl(Nz([SEC_OUT_COMMONDATA].[OPERPRESSURE],0))-CDbl(Nz([SEC_IN_COMMONDATA].[OPERPRESSURE],0)),[SEC_OUT_COMMONDATA].[PRESSUREUNITS])
AS [SEC DIFFERENTIAL PRESSURE 1],
NumberWithUnits(CDbl(Nz([SEC_OUT_COMMONDATA].[PRESSURE_3],0))-CDbl(Nz([SEC_IN_COMMONDATA].[PRESSURE_3],0)),[SEC_OUT_COMMONDATA].[PRESSUREUNITS])
AS [SEC DIFFERENTIAL PRESSURE 2],
NumberWithUnits([SEC_OUT_COMMONDATA].[DENSITY_1],[SEC_OUT_COMMONDATA].[DENSITY_UNITS])
AS [SECOUT DENSITY 1],
NumberWithUnits([SEC_OUT_COMMONDATA].[DENSITY_2],[SEC_OUT_COMMONDATA].[DENSITY_UNITS])
AS [SECOUT DENSITY 2],
NumberWithUnits([SEC_OUT_COMMONDATA].[VISCOSITY_1],[SEC_OUT_COMMONDATA].[VISCOSITY_UNITS])
AS [SECOUT VISCOSITY 1],
NumberWithUnits([SEC_OUT_COMMONDATA].[VISCOSITY_2],[SEC_OUT_COMMONDATA].[VISCOSITY_UNITS])
AS [SECOUT VISCOSITY 2], SEC_OUT_COMMONDATA.CEXTA3 AS [SECOUT SPEC HEAT 1],
SEC_OUT_COMMONDATA.CEXTA6 AS [SECOUT SPEC HEAT 2], SEC_OUT_COMMONDATA.CEXTA5
AS [SECOUT THERMAL COND 1], SEC_OUT_COMMONDATA.CEXTA8 AS [SECOUT THERMAL COND
2], JACK_IN_COMMONDATA.COMMODITY AS [JACKIN COMMODITY],
IIf([JACK_IN_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([JACK_IN_COMMONDATA].[MASS_FLOW_1],[JACK_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([JACK_IN_COMMONDATA].[VOL_FLOW_1],[JACK_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [JACKIN FLOW 1],
IIf([JACK_IN_COMMONDATA].[VOL_FLOW_3]=0,NumberWithUnits([JACK_IN_COMMONDATA].[MASS_FLOW_3],[JACK_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([JACK_IN_COMMONDATA].[VOL_FLOW_3],[JACK_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [JACKIN FLOW 2],
NumberWithUnits([JACK_IN_COMMONDATA].[OPERTEMPERATURE],[JACK_IN_COMMONDATA].[TEMPERATUREUNITS])
AS [JACKIN OPERATING TEMPERATURE 1],
NumberWithUnits([JACK_IN_COMMONDATA].[TEMPERATURE_3],[JACK_IN_COMMONDATA].[TEMPERATUREUNITS])
AS [JACKIN OPERATING TEMPERATURE 2],
NumberWithUnits([JACK_IN_COMMONDATA].[OPERPRESSURE],[JACK_IN_COMMONDATA].[PRESSUREUNITS])
AS [JACK INLET PRESSURE 1],
NumberWithUnits([JACK_IN_COMMONDATA].[PRESSURE_3],[JACK_IN_COMMONDATA].[PRESSUREUNITS])
AS [JACK INLET PRESSURE 2],
NumberWithUnits([JACK_IN_COMMONDATA].[DENSITY_1],[JACK_IN_COMMONDATA].[DENSITY_UNITS])
AS [JACKIN DENSITY 1],
NumberWithUnits([JACK_IN_COMMONDATA].[DENSITY_2],[JACK_IN_COMMONDATA].[DENSITY_UNITS])
AS [JACKIN DENSITY 2],
NumberWithUnits([JACK_IN_COMMONDATA].[VISCOSITY_1],[JACK_IN_COMMONDATA].[VISCOSITY_UNITS])
AS [JACKIN VISCOSITY 1],
NumberWithUnits([JACK_IN_COMMONDATA].[VISCOSITY_2],[JACK_IN_COMMONDATA].[VISCOSITY_UNITS])
AS [JACKIN VISCOSITY 2], JACK_IN_COMMONDATA.CEXTA3 AS [JACKIN SPEC HEAT 1],
JACK_IN_COMMONDATA.CEXTA6 AS [JACKIN SPEC HEAT 2], JACK_IN_COMMONDATA.CEXTA5
AS [JACKIN THERMAL COND 1], JACK_IN_COMMONDATA.CEXTA8 AS [JACKIN THERMAL COND
2], JACK_OUT_COMMONDATA.COMMODITY AS [JACKOUT COMMODITY],
IIf([JACK_OUT_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([JACK_OUT_COMMONDATA].[MASS_FLOW_1],[JACK_OUT_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([JACK_OUT_COMMONDATA].[VOL_FLOW_1],[JACK_OUT_COMMONDATA].[VOL_FLOW_UNITS]))
AS [JACKOUT FLOW 1],
IIf([JACK_OUT_COMMONDATA].[VOL_FLOW_3]=0,NumberWithUnits([JACK_OUT_COMMONDATA].[MASS_FLOW_3],[JACK_OUT_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([JACK_OUT_COMMONDATA].[VOL_FLOW_3],[JACK_OUT_COMMONDATA].[VOL_FLOW_UNITS]))
AS [JACKOUT FLOW 2],
NumberWithUnits([JACK_OUT_COMMONDATA].[OPERTEMPERATURE],[JACK_OUT_COMMONDATA].[TEMPERATUREUNITS])
AS [JACKOUT OPERATING TEMPERATURE 1],
NumberWithUnits([JACK_OUT_COMMONDATA].[TEMPERATURE_3],[JACK_OUT_COMMONDATA].[TEMPERATUREUNITS])
AS [JACKOUT OPERATING TEMPERATURE 2],
NumberWithUnits(CDbl(Nz([JACK_OUT_COMMONDATA].[OPERPRESSURE],0))-CDbl(Nz([JACK_IN_COMMONDATA].[OPERPRESSURE],0)),[JACK_OUT_COMMONDATA].[PRESSUREUNITS])
AS [JACK DIFFERENTIAL PRESSURE 1],
NumberWithUnits(CDbl(Nz([JACK_OUT_COMMONDATA].[PRESSURE_3],0))-CDbl(Nz([JACK_IN_COMMONDATA].[PRESSURE_3],0)),[JACK_OUT_COMMONDATA].[PRESSUREUNITS])
AS [JACK DIFFERENTIAL PRESSURE 2],
NumberWithUnits([JACK_OUT_COMMONDATA].[DENSITY_1],[JACK_OUT_COMMONDATA].[DENSITY_UNITS])
AS [JACKOUT DENSITY 1],
NumberWithUnits([JACK_OUT_COMMONDATA].[DENSITY_2],[JACK_OUT_COMMONDATA].[DENSITY_UNITS])
AS [JACKOUT DENSITY 2],
NumberWithUnits([JACK_OUT_COMMONDATA].[VISCOSITY_1],[JACK_OUT_COMMONDATA].[VISCOSITY_UNITS])
AS [JACKOUT VISCOSITY 1],
NumberWithUnits([JACK_OUT_COMMONDATA].[VISCOSITY_2],[JACK_OUT_COMMONDATA].[VISCOSITY_UNITS])
AS [JACKOUT VISCOSITY 2], JACK_OUT_COMMONDATA.CEXTA3 AS [JACKOUT SPEC HEAT
1], JACK_OUT_COMMONDATA.CEXTA6 AS [JACKOUT SPEC HEAT 2],
JACK_OUT_COMMONDATA.CEXTA5 AS [JACKOUT THERMAL COND 1],
JACK_OUT_COMMONDATA.CEXTA8 AS [JACKOUT THERMAL COND 2]
FROM (((((SOLODATA LEFT JOIN COMMONDATA AS PRIM_IN_COMMONDATA ON
SOLODATA.EXTN1 = PRIM_IN_COMMONDATA.COMMONDATALINK) LEFT JOIN COMMONDATA AS
PRIM_OUT_COMMONDATA ON SOLODATA.EXTN2 = PRIM_OUT_COMMONDATA.COMMONDATALINK)
LEFT JOIN COMMONDATA AS SEC_IN_COMMONDATA ON SOLODATA.EXTN3 =
SEC_IN_COMMONDATA.COMMONDATALINK) LEFT JOIN COMMONDATA AS SEC_OUT_COMMONDATA
ON SOLODATA.EXTN4 = SEC_OUT_COMMONDATA.COMMONDATALINK) LEFT JOIN COMMONDATA
AS JACK_IN_COMMONDATA ON SOLODATA.EXTN5 = JACK_IN_COMMONDATA.COMMONDATALINK)
LEFT JOIN COMMONDATA AS JACK_OUT_COMMONDATA ON SOLODATA.EXTN6 =
JACK_OUT_COMMONDATA.COMMONDATALINK;

Equipment Query:
SELECT TitleCase([SOLODATA].[COMPONENTDESCRIPTION]) AS [COMPONENT
DESCRIPTION], ("EDS-" & GetProjectNumber() & "-" & [SOLODATA].[TAG2] &
Left([SOLODATA].[TAG3],GetClientTagLength())) AS EDS, "PROJECT " &
GetProjectNumber() AS PROJECT, SOLODATA2_V.EXTA140 AS [CLIENT DS], "GPP-" &
GetProjectNumber() & "-" & [SOLODATA].[EXTA1] AS GPP, SOLODATA.EXTA2 AS PO,
TitleCase([PROJDATA].[PROJECT_NAME]) AS PROJECT_NAME,
TitleCase([PROJDATA].[CLIENT]) AS CLIENT, PROJDATA.CLIENT_PROJECT_NUM,
PROJDATA.CLIENT_PO, PROJDATA.SITE, SOLODATA.MANUFACTURER, SOLODATA.EXTA11 AS
[PRIMARY FLUID ALLOCATION], COMMODITIES.[PRIMIN COMMODITY] AS [PRIMIN
COMMODITY], IIf([SOLODATA].[EXTN12],[PRIMIN FLOW 2],[PRIMIN FLOW 1]) AS
[PRIMIN FLOW], IIf([SOLODATA].[EXTN12],[PRIMIN OPERATING TEMPERATURE
2],[PRIMIN OPERATING TEMPERATURE 1]) AS [PRIMIN OPERATING TEMPERATURE],
IIf([SOLODATA].[EXTN12],[PRIM INLET PRESSURE 2],[PRIM INLET PRESSURE 1]) AS
[PRIM INLET PRESSURE], IIf([SOLODATA].[EXTN12],[PRIMIN DENSITY 2],[PRIMIN
DENSITY 1]) AS [PRIMIN DENSITY], IIf([SOLODATA].[EXTN12],[PRIMIN VISCOSITY
2],[PRIMIN VISCOSITY 1]) AS [PRIMIN VISCOSITY],
IIf([SOLODATA].[EXTN12],[PRIMIN SPEC HEAT 2],[PRIMIN SPEC HEAT 1]) AS [PRIMIN
SPEC HEAT], IIf([SOLODATA].[EXTN12],[PRIMIN THERMAL COND 2],[PRIMIN THERMAL
COND 1]) AS [PRIMIN THERMAL COND], COMMODITIES.[PRIMOUT COMMODITY] AS
[PRIMOUT COMMODITY], IIf([SOLODATA].[EXTN12],[PRIMOUT FLOW 2],[PRIMOUT FLOW
1]) AS [PRIMOUT FLOW], IIf([SOLODATA].[EXTN12],[PRIMOUT OPERATING TEMPERATURE
2],[PRIMOUT OPERATING TEMPERATURE 1]) AS [PRIMOUT OPERATING TEMPERATURE],
IIf([SOLODATA].[EXTN12],[PRIM DIFFERENTIAL PRESSURE 2],[PRIM DIFFERENTIAL
PRESSURE 1]) AS [PRIM DIFFERENTIAL PRESSURE],
IIf([SOLODATA].[EXTN12],[PRIMOUT DENSITY 2],[PRIMOUT DENSITY 1]) AS [PRIMOUT
DENSITY], IIf([SOLODATA].[EXTN12],[PRIMOUT VISCOSITY 2],[PRIMOUT VISCOSITY
1]) AS [PRIMOUT VISCOSITY], IIf([SOLODATA].[EXTN12],[PRIMOUT SPEC HEAT
2],[PRIMOUT SPEC HEAT 1]) AS [PRIMOUT SPEC HEAT],
IIf([SOLODATA].[EXTN12],[PRIMOUT THERMAL COND 2],[PRIMOUT THERMAL COND 1]) AS
[PRIMOUT THERMAL COND], SOLODATA.EXTA12 AS [SECONDARY FLUID ALLOCATION],
COMMODITIES.[SECIN COMMODITY] AS [SECIN COMMODITY],
IIf([SOLODATA].[EXTN12],[SECIN FLOW 2],[SECIN FLOW 1]) AS [SECIN FLOW],
IIf([SOLODATA].[EXTN12],[SECIN OPERATING TEMPERATURE 2],[SECIN OPERATING
TEMPERATURE 1]) AS [SECIN OPERATING TEMPERATURE],
IIf([SOLODATA].[EXTN12],[SEC INLET PRESSURE 2],[SEC INLET PRESSURE 1]) AS
[SEC INLET PRESSURE], IIf([SOLODATA].[EXTN12],[SECIN DENSITY 2],[SECIN
DENSITY 1]) AS [SECIN DENSITY], IIf([SOLODATA].[EXTN12],[SECIN VISCOSITY
2],[SECIN VISCOSITY 1]) AS [SECIN VISCOSITY], IIf([SOLODATA].[EXTN12],[SECIN
SPEC HEAT 2],[SECIN SPEC HEAT 1]) AS [SECIN SPEC HEAT],
IIf([SOLODATA].[EXTN12],[SECIN THERMAL COND 2],[SECIN THERMAL COND 1]) AS
[SECIN THERMAL COND], COMMODITIES.[SECOUT COMMODITY] AS [SECOUT COMMODITY],
IIf([SOLODATA].[EXTN12],[SECOUT FLOW 2],[SECOUT FLOW 1]) AS [SECOUT FLOW],
IIf([SOLODATA].[EXTN12],[SECOUT OPERATING TEMPERATURE 2],[SECOUT OPERATING
TEMPERATURE 1]) AS [SECOUT OPERATING TEMPERATURE],
IIf([SOLODATA].[EXTN12],[SEC DIFFERENTIAL PRESSURE 2],[SEC DIFFERENTIAL
PRESSURE 1]) AS [SEC DIFFERENTIAL PRESSURE], IIf([SOLODATA].[EXTN12],[SECOUT
DENSITY 2],[SECOUT DENSITY 1]) AS [SECOUT DENSITY],
IIf([SOLODATA].[EXTN12],[SECOUT VISCOSITY 2],[SECOUT VISCOSITY 1]) AS [SECOUT
VISCOSITY], IIf([SOLODATA].[EXTN12],[SECOUT SPEC HEAT 2],[SECOUT SPEC HEAT
1]) AS [SECOUT SPEC HEAT], IIf([SOLODATA].[EXTN12],[SECOUT THERMAL COND
2],[SECOUT THERMAL COND 1]) AS [SECOUT THERMAL COND],
NumberWithUnits([COMMONDATA].[PRESSURE_3],[COMMONDATA].[PRESSUREUNITS]) AS
[PRIM DESIGN DP],
NumberWithUnits([COMMONDATA].[PRESSURE_4],[COMMONDATA].[PRESSUREUNITS]) AS
[SEC DESIGN DP],
NumberWithUnits([SOLODATA].[DISTANCE_2],[SOLODATA].[DISTANCE_UNITS] & "/s")
AS [PRIM VELOCITY],
NumberWithUnits([SOLODATA].[DISTANCE_3],[SOLODATA].[DISTANCE_UNITS] & "/s")
AS [SEC VELOCITY], SOLODATA.EXTA16 AS [PRIM FOULING RESISTANCE],
SOLODATA.EXTA17 AS [SEC FOULING RESISTANCE],
NumberWithUnits([SOLODATA].[POWER_1],[SOLODATA].[POWER_UNITS]) AS [HEAT
EXCHANGED], NumberWithUnits([SOLODATA].[EXTA13],[SOLODATA].[EXTA24]) AS
[SERVICE TRANSFER RATE],
NumberWithUnits([SOLODATA].[EXTA14],[SOLODATA].[EXTA24]) AS [DIRTY TRANSFER
RATE], NumberWithUnits([SOLODATA].[EXTA15],[SOLODATA].[EXTA24]) AS [CLEAN
TRANSFER RATE],
NumberWithUnits([SOLODATA].[TEMPERATURE_3],[SOLODATA].[TEMPERATURE_UNITS]) AS
[MEAN TEMPERATURE DIFFERENCE], IIf([SOLODATA].[EXTN10],"JACKET","") AS
[JACKET ALLOCATION], IIf([SOLODATA].[EXTN10],"IN","") AS [JACKET IN HEADING],
IIf([SOLODATA].[EXTN10],"OUT","") AS [JACKET OUT HEADING],
IIf([SOLODATA].[EXTN10],COMMODITIES.[JACKIN COMMODITY],"") AS [JACKIN
COMMODITY], IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKIN FLOW
2],[JACKIN FLOW 1]),"") AS [JACKIN FLOW],
IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKIN OPERATING TEMPERATURE
2],[JACKIN OPERATING TEMPERATURE 1]),"") AS [JACKIN OPERATING TEMPERATURE],
IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACK INLET PRESSURE 2],[JACK
INLET PRESSURE 1]),"") AS [JACK INLET PRESSURE],
IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKIN DENSITY 2],[JACKIN
DENSITY 1]),"") AS [JACKIN DENSITY],
IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKIN VISCOSITY 2],[JACKIN
VISCOSITY 1]),"") AS [JACKIN VISCOSITY],
IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKIN SPEC HEAT 2],[JACKIN
SPEC HEAT 1]),"") AS [JACKIN SPEC HEAT],
IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKIN THERMAL COND
2],[JACKIN THERMAL COND 1]),"") AS [JACKIN THERMAL COND],
IIf([SOLODATA].[EXTN10],COMMODITIES.[JACKOUT COMMODITY],"") AS [JACKOUT
COMMODITY], IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKOUT FLOW
2],[JACKOUT FLOW 1]),"") AS [JACKOUT FLOW],
IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKOUT OPERATING
TEMPERATURE 2],[JACKOUT OPERATING TEMPERATURE 1]),"") AS [JACKOUT OPERATING
TEMPERATURE], IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACK
DIFFERENTIAL PRESSURE 2],[JACK DIFFERENTIAL PRESSURE 1]),"") AS [JACK
DIFFERENTIAL PRESSURE],
IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKOUT DENSITY 2],[JACKOUT
DENSITY 1]),"") AS [JACKOUT DENSITY],
IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKOUT VISCOSITY
2],[JACKOUT VISCOSITY 1]),"") AS [JACKOUT VISCOSITY],
IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKOUT SPEC HEAT
2],[JACKOUT SPEC HEAT 1]),"") AS [JACKOUT SPEC HEAT],
IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKOUT THERMAL COND
2],[JACKOUT THERMAL COND 1]),"") AS [JACKOUT THERMAL COND],
ShapeDimen([SOLODATA].[SDISTANCE_6],[SOLODATA].[SDISTANCE_9],[SOLODATA].[SDISTANCE_7],[SOLODATA].[SDISTANCE_8],[SOLODATA].[SDISTANCE_UNITS])
AS [SIZE],
ShapeDimenCap([SOLODATA].[SDISTANCE_6],[SOLODATA].[SDISTANCE_9],[SOLODATA].[SDISTANCE_7],[SOLODATA].[SDISTANCE_8])
AS [SIZE CAPTION], SOLODATA.EXTA3 AS TYPE,
NumberWithUnits([SOLODATA].[DISTANCE_1],[SOLODATA].[DISTANCE_UNITS] & "Â²") AS
[HEAT TRANSFER SURFACE AREA],
NumberWithUnits([SOLODATA].[PRESSURE_2],[SOLODATA].[PRESSURE_UNITS] & " " &
[SOLODATA2_V].[PRESSURE_TYPE]) AS [PRIM DESIGN PRESSURE],
NumberWithUnits([SOLODATA].[PRESSURE_1],[SOLODATA].[PRESSURE_UNITS] & " " &
[SOLODATA2_V].[PRESSURE_TYPE]) AS [PRIM TEST PRESSURE],
NumberWithUnits([SOLODATA].[PRESSURE_4],[SOLODATA].[PRESSURE_UNITS] & " " &
[SOLODATA2_V].[PRESSURE_TYPE]) AS [SEC DESIGN PRESSURE],
NumberWithUnits([SOLODATA].[PRESSURE_3],[SOLODATA].[PRESSURE_UNITS] & " " &
[SOLODATA2_V].[PRESSURE_TYPE]) AS [SEC TEST PRESSURE],
IIf([SOLODATA].[EXTN10],NumberWithUnits([SOLODATA].[PRESSURE_6],[SOLODATA].[PRESSURE_UNITS]
& " " & [SOLODATA2_V].[PRESSURE_TYPE]),"") AS [JACKET DESIGN PRESSURE],
IIf([SOLODATA].[EXTN10],NumberWithUnits([SOLODATA].[PRESSURE_5],[SOLODATA].[PRESSURE_UNITS]
& " " & [SOLODATA2_V].[PRESSURE_TYPE]),"") AS [JACKET TEST PRESSURE],
NumberWithUnits([SOLODATA].[TEMPERATURE_2],[SOLODATA].[TEMPERATURE_UNITS]) AS
[PRIM DESIGN TEMPERATURE],
NumberWithUnits([SOLODATA].[TEMPERATURE_4],[SOLODATA].[TEMPERATURE_UNITS]) AS
[SEC DESIGN TEMPERATURE],
IIf([SOLODATA].[EXTN10],NumberWithUnits([SOLODATA].[TEMPERATURE_6],[SOLODATA].[TEMPERATURE_UNITS]),"")
AS [JACKET DESIGN TEMPERATURE],
NumberWithUnits([SOLODATA].[TEMPERATURE_1],[SOLODATA].[TEMPERATURE_UNITS]) AS
[MINIMUM DESIGN TEMPERATURE],
IIf([SOLODATA].[EXTN10],NumberWithUnits([SOLODATA].[TEMPERATURE_1],[SOLODATA].[TEMPERATURE_UNITS]),"")
AS [JACKET MINIMUM DESIGN TEMPERATURE],
NumberWithUnits([SOLODATA].[SDISTANCE_4],[SOLODATA].[SDISTANCE_UNITS]) AS
[PRIM CORROSION ALLOWANCE],
NumberWithUnits([SOLODATA].[SDISTANCE_5],[SOLODATA].[SDISTANCE_UNITS]) AS
[SEC CORROSION ALLOWANCE],
IIf([SOLODATA].[EXTN10],NumberWithUnits([SOLODATA].[SDISTANCE_1],[SOLODATA].[SDISTANCE_UNITS]),"")
AS [JACKET CORROSION ALLOWANCE], SOLODATA.EXTA18 AS [PRIM IN CONNECTION
DETAILS], SOLODATA.EXTA19 AS [PRIM OUT CONNECTION DETAILS], SOLODATA.EXTA20
AS [SEC IN CONNECTION DETAILS], SOLODATA.EXTA21 AS [SEC OUT CONNECTION
DETAILS], IIf([SOLODATA].[EXTN10],[SOLODATA].[EXTA22],"") AS [JACKET IN
CONNECTION DETAILS], IIf([SOLODATA].[EXTN10],[SOLODATA].[EXTA23],"") AS
[JACKET OUT CONNECTION DETAILS], SOLODATA.EXTN11 AS [TUBE COUNT],
NumberWithUnits([SOLODATA].[SDISTANCE_2],[SOLODATA].[SDISTANCE_UNITS]) AS
[TUBE OD],
NumberWithUnits([SOLODATA].[SDISTANCE_3],[SOLODATA].[SDISTANCE_UNITS]) AS
[WALL THICKNESS],
NumberWithUnits([SOLODATA].[DISTANCE_4],[SOLODATA].[DISTANCE_UNITS]) AS [TUBE
LENGTH], NumberWithUnits([SOLODATA].[MASS_1],[SOLODATA].[MASS_UNITS]) AS
[SHELL WEIGHT], NumberWithUnits([SOLODATA].[MASS_2],[SOLODATA].[MASS_UNITS])
AS [TUBE BUNDLE WEIGHT],
NumberWithUnits([SOLODATA].[MASS_3],[SOLODATA].[MASS_UNITS]) AS [FILLED WITH
WATER WEIGHT], SOLODATA.EXTA25 AS [TUBE MATERIAL], SOLODATA.EXTA26 AS [TUBE
TYPE], SOLODATA.EXTA27 AS [SHELL MATERIAL],
IIF(SOLODATA.EXTN10,SOLODATA.EXTA28,"N/A") AS [JACKET MATERIAL],
SOLODATA.EXTA29 AS [HEAD 1 MATERIAL], SOLODATA.EXTA30 AS [HEAD 2 MATERIAL],
SOLODATA.EXTA31 AS [STATIONARY TUBESHEET MATERIAL], SOLODATA.EXTA32 AS
[FLOATING TUBESHEET MATERIAL], SOLODATA.EXTA33 AS [BAFFLES - CROSS MATERIAL],
SOLODATA.EXTA34 AS [BAFFLES - TYPE], SOLODATA.EXTA35 AS [BAFFLES - % CUT],
NumberWithUnits([SOLODATA].[SDISTANCE_10],[SOLODATA].[SDISTANCE_UNITS]) AS
[CENTER TO CENTER SPACING],
NumberWithUnits([SOLODATA].[SDISTANCE_11],[SOLODATA].[SDISTANCE_UNITS]) AS
[INLET SIZE], SOLODATA.EXTA36 AS [TUBE SUPPORTS MATERIAL], SOLODATA.EXTA37 AS
[TUBE/TUBESHEET JOINT DETAILS], SOLODATA.EXTA38 AS [EXPANSION JOINT DETAILS],
SOLODATA.EXTA39 AS [EXPANSION JOINT TYPE], SOLODATA.EXTA40 AS [PVÂ² - INLET
NOZZLE], SOLODATA.EXTA41 AS [SHELL SIDE GASKET], SOLODATA.EXTA42 AS [TUBE
SIDE GASKET], SOLODATA.EXTA43 AS [FLOATING HEAD GASKET], SOLODATA.EXTA44 AS
[CODE REQUIREMENTS]
FROM PROJDATA, [Exchanger Sub] AS COMMODITIES INNER JOIN (((SOLODATA INNER
JOIN (COMMONDATA INNER JOIN COMPONENTS ON COMMONDATA.COMMONDATALINK =
COMPONENTS.COMMONDATALINK) ON SOLODATA.SOLODATALINK =
COMPONENTS.SOLODATALINK) INNER JOIN DRAWINGLIST ON COMPONENTS.DRAWINGID =
DRAWINGLIST.DRAWINGID) INNER JOIN SOLODATA2_V ON SOLODATA.SOLODATALINK =
SOLODATA2_V.SOLODATALINK) ON COMMODITIES.SOLODATALINK = SOLODATA.SOLODATALINK
WHERE (((Asc(CStr(Right([SOLODATA].[SECONDTAGNUMBER],1))))=65 Or
(Asc(CStr(Right([SOLODATA].[SECONDTAGNUMBER],1))))>=48 And
(Asc(CStr(Right([SOLODATA].[SECONDTAGNUMBER],1))))<=57) AND
((Left([SOLODATA].[SECONDTAGNUMBER],InStr([SOLODATA].[SECONDTAGNUMBER],"-")+3))=GetEnersulTag())
AND ((Left(Right([DRAWINGLIST].[NAME],4),3))="G20" Or
(Left(Right([DRAWINGLIST].[NAME],4),3))="G40") AND
((DRAWINGLIST.APPLICATION)="PID" Or (DRAWINGLIST.APPLICATION)="EQUIP") AND
((COMPONENTS.PARENTID)<1) AND ((DRAWINGLIST.DRAWINGTYPE)=0));

Oh no! Iâ€™ve even exceeded the post limit! Looks like Iâ€™ll have to post the
rest after.

AlwaysFroosh! · Jan 25, 2008

And continued:

Numbering Query:
SELECT [FEDS-E-101].[COMPONENT DESCRIPTION] AS 150, [FEDS-E-101].PROJECT AS
152, [FEDS-E-101].[CLIENT DS] AS 153, [FEDS-E-101].GPP AS 2, [FEDS-E-101].PO
AS 3, [FEDS-E-101].PROJECT_NAME AS 4, [FEDS-E-101].CLIENT AS 5,
[FEDS-E-101].CLIENT_PROJECT_NUM AS 6, [FEDS-E-101].CLIENT_PO AS 7,
[FEDS-E-101].SITE AS 8, [FEDS-E-101].MANUFACTURER AS 9, [FEDS-E-101].[PRIMARY
FLUID ALLOCATION] AS 11, [FEDS-E-101].[PRIMIN COMMODITY] AS 12,
[FEDS-E-101].[PRIMIN FLOW] AS 13, [FEDS-E-101].[PRIMIN OPERATING TEMPERATURE]
AS 14, [FEDS-E-101].[PRIM INLET PRESSURE] AS 19, [FEDS-E-101].[PRIMIN
DENSITY] AS 15, [FEDS-E-101].[PRIMIN VISCOSITY] AS 16, [FEDS-E-101].[PRIMIN
SPEC HEAT] AS 17, [FEDS-E-101].[PRIMIN THERMAL COND] AS 18,
[FEDS-E-101].[PRIMOUT OPERATING TEMPERATURE] AS 160, [FEDS-E-101].[PRIM
DIFFERENTIAL PRESSURE] AS 20, [FEDS-E-101].[PRIMOUT DENSITY] AS 161,
[FEDS-E-101].[PRIMOUT VISCOSITY] AS 162, [FEDS-E-101].[PRIMOUT SPEC HEAT] AS
163, [FEDS-E-101].[PRIMOUT THERMAL COND] AS 164, [FEDS-E-101].[SECONDARY
FLUID ALLOCATION] AS 166, [FEDS-E-101].[SECIN COMMODITY] AS 167,
[FEDS-E-101].[SECIN FLOW] AS 168, [FEDS-E-101].[SECIN OPERATING TEMPERATURE]
AS 169, [FEDS-E-101].[SEC INLET PRESSURE] AS 174, [FEDS-E-101].[SECIN
DENSITY] AS 170, [FEDS-E-101].[SECIN VISCOSITY] AS 171, [FEDS-E-101].[SECIN
SPEC HEAT] AS 172, [FEDS-E-101].[SECIN THERMAL COND] AS 173,
[FEDS-E-101].[SECOUT OPERATING TEMPERATURE] AS 178, [FEDS-E-101].[SEC
DIFFERENTIAL PRESSURE] AS 175, [FEDS-E-101].[SECOUT DENSITY] AS 179,
[FEDS-E-101].[SECOUT VISCOSITY] AS 180, [FEDS-E-101].[SECOUT SPEC HEAT] AS
181, [FEDS-E-101].[SECOUT THERMAL COND] AS 182, [FEDS-E-101].[PRIM DESIGN DP]
AS 165, [FEDS-E-101].[SEC DESIGN DP] AS 183, [FEDS-E-101].[PRIM VELOCITY] AS
21, [FEDS-E-101].[SEC VELOCITY] AS 176, [FEDS-E-101].[PRIM FOULING
RESISTANCE] AS 22, [FEDS-E-101].[SEC FOULING RESISTANCE] AS 177,
[FEDS-E-101].[HEAT EXCHANGED] AS 23, [FEDS-E-101].[SERVICE TRANSFER RATE] AS
24, [FEDS-E-101].[DIRTY TRANSFER RATE] AS 25, [FEDS-E-101].[CLEAN TRANSFER
RATE] AS 26, [FEDS-E-101].[MEAN TEMPERATURE DIFFERENCE] AS 27,
[FEDS-E-101].[JACKET ALLOCATION] AS 184, [FEDS-E-101].[JACKET IN HEADING] AS
220, [FEDS-E-101].[JACKET OUT HEADING] AS 221, [FEDS-E-101].[JACKIN
COMMODITY] AS 185, [FEDS-E-101].[JACKIN FLOW] AS 186, [FEDS-E-101].[JACKIN
OPERATING TEMPERATURE] AS 187, [FEDS-E-101].[JACK INLET PRESSURE] AS 192,
[FEDS-E-101].[JACKIN DENSITY] AS 188, [FEDS-E-101].[JACKIN VISCOSITY] AS 189,
[FEDS-E-101].[JACKIN SPEC HEAT] AS 190, [FEDS-E-101].[JACKIN THERMAL COND] AS
191, [FEDS-E-101].[JACKOUT OPERATING TEMPERATURE] AS 196, [FEDS-E-101].[JACK
DIFFERENTIAL PRESSURE] AS 193, [FEDS-E-101].[JACKOUT DENSITY] AS 197,
[FEDS-E-101].[JACKOUT VISCOSITY] AS 198, [FEDS-E-101].[JACKOUT SPEC HEAT] AS
199, [FEDS-E-101].[JACKOUT THERMAL COND] AS 200, [FEDS-E-101].SIZE AS 28,
[FEDS-E-101].[SIZE CAPTION] AS 159, [FEDS-E-101].TYPE AS 29,
[FEDS-E-101].[HEAT TRANSFER SURFACE AREA] AS 30, [FEDS-E-101].[PRIM DESIGN
PRESSURE] AS 32, [FEDS-E-101].[PRIM TEST PRESSURE] AS 202, [FEDS-E-101].[SEC
DESIGN PRESSURE] AS 204, [FEDS-E-101].[SEC TEST PRESSURE] AS 209,
[FEDS-E-101].[JACKET DESIGN PRESSURE] AS 210, [FEDS-E-101].[JACKET TEST
PRESSURE] AS 215, [FEDS-E-101].[PRIM DESIGN TEMPERATURE] AS 33,
[FEDS-E-101].[SEC DESIGN TEMPERATURE] AS 205, [FEDS-E-101].[JACKET DESIGN
TEMPERATURE] AS 211, [FEDS-E-101].[MINIMUM DESIGN TEMPERATURE] AS 203,
[FEDS-E-101].[JACKET MINIMUM DESIGN TEMPERATURE] AS 216, [FEDS-E-101].[PRIM
CORROSION ALLOWANCE] AS 34, [FEDS-E-101].[SEC CORROSION ALLOWANCE] AS 206,
[FEDS-E-101].[JACKET CORROSION ALLOWANCE] AS 212, [FEDS-E-101].[PRIM IN
CONNECTION DETAILS] AS 35, [FEDS-E-101].[PRIM OUT CONNECTION DETAILS] AS 36,
[FEDS-E-101].[SEC IN CONNECTION DETAILS] AS 207, [FEDS-E-101].[SEC OUT
CONNECTION DETAILS] AS 208, [FEDS-E-101].[JACKET IN CONNECTION DETAILS] AS
213, [FEDS-E-101].[JACKET OUT CONNECTION DETAILS] AS 214, [FEDS-E-101].[TUBE
COUNT] AS 37, [FEDS-E-101].[TUBE OD] AS 38, [FEDS-E-101].[WALL THICKNESS] AS
39, [FEDS-E-101].[TUBE LENGTH] AS 40, [FEDS-E-101].[SHELL WEIGHT] AS 41,
[FEDS-E-101].[TUBE BUNDLE WEIGHT] AS 42, [FEDS-E-101].[FILLED WITH WATER
WEIGHT] AS 43, [FEDS-E-101].[TUBE MATERIAL] AS 44, [FEDS-E-101].[TUBE TYPE]
AS 45, [FEDS-E-101].[SHELL MATERIAL] AS 46, [FEDS-E-101].[JACKET MATERIAL] AS
47, [FEDS-E-101].[HEAD 1 MATERIAL] AS 48, [FEDS-E-101].[HEAD 2 MATERIAL] AS
49, [FEDS-E-101].[STATIONARY TUBESHEET MATERIAL] AS 50,
[FEDS-E-101].[FLOATING TUBESHEET MATERIAL] AS 51, [FEDS-E-101].[BAFFLES -
CROSS MATERIAL] AS 52, [FEDS-E-101].[BAFFLES - TYPE] AS 53,
[FEDS-E-101].[BAFFLES - % CUT] AS 54, [FEDS-E-101].[CENTER TO CENTER SPACING]
AS 55, [FEDS-E-101].[INLET SIZE] AS 56, [FEDS-E-101].[TUBE SUPPORTS MATERIAL]
AS 57, [FEDS-E-101].[TUBE/TUBESHEET JOINT DETAILS] AS 58,
[FEDS-E-101].[EXPANSION JOINT DETAILS] AS 59, [FEDS-E-101].[EXPANSION JOINT
TYPE] AS 60, [FEDS-E-101].[PVÂ² - INLET NOZZLE] AS 61, [FEDS-E-101].[SHELL
SIDE GASKET] AS 62, [FEDS-E-101].[TUBE SIDE GASKET] AS 63,
[FEDS-E-101].[FLOATING HEAD GASKET] AS 64, [FEDS-E-101].[CODE REQUIREMENTS]
AS 65
FROM [FEDS-E-101];

And finally the Comparison Query:
SELECT Compare([NEDS-E-101].[150],[TDREV_LINES].[150],[TDREV_1].[Revision])
AS 150R, Compare([NEDS-E-101].[152],[TDREV_LINES].[152],[TDREV_1].[Revision])
AS 152R, Compare([NEDS-E-101].[153],[TDREV_LINES].[153],[TDREV_1].[Revision])
AS 153R, Compare([NEDS-E-101].[2],[TDREV_LINES].[2],[TDREV_1].[Revision]) AS
2R, Compare([NEDS-E-101].[3],[TDREV_LINES].[3],[TDREV_1].[Revision]) AS 3R,
Compare([NEDS-E-101].[4],[TDREV_LINES].[4],[TDREV_1].[Revision]) AS 4R,
Compare([NEDS-E-101].[5],[TDREV_LINES].[5],[TDREV_1].[Revision]) AS 5R,
Compare([NEDS-E-101].[6],[TDREV_LINES].[6],[TDREV_1].[Revision]) AS 6R,
Compare([NEDS-E-101].[7],[TDREV_LINES].[7],[TDREV_1].[Revision]) AS 7R,
Compare([NEDS-E-101].[8],[TDREV_LINES].[8],[TDREV_1].[Revision]) AS 8R,
Compare([NEDS-E-101].[9],[TDREV_LINES].[9],[TDREV_1].[Revision]) AS 9R,
Compare([NEDS-E-101].[11],[TDREV_LINES].[11],[TDREV_1].[Revision]) AS 11R,
Compare([NEDS-E-101].[12],[TDREV_LINES].[12],[TDREV_1].[Revision]) AS 12R,
Compare([NEDS-E-101].[13],[TDREV_LINES].[13],[TDREV_1].[Revision]) AS 13R,
Compare([NEDS-E-101].[14],[TDREV_LINES].[14],[TDREV_1].[Revision]) AS 14R,
Compare([NEDS-E-101].[19],[TDREV_LINES].[19],[TDREV_1].[Revision]) AS 19R,
Compare([NEDS-E-101].[15],[TDREV_LINES].[15],[TDREV_1].[Revision]) AS 15R,
Compare([NEDS-E-101].[16],[TDREV_LINES].[16],[TDREV_1].[Revision]) AS 16R,
Compare([NEDS-E-101].[17],[TDREV_LINES].[17],[TDREV_1].[Revision]) AS 17R,
Compare([NEDS-E-101].[18],[TDREV_LINES].[18],[TDREV_1].[Revision]) AS 18R,
Compare([NEDS-E-101].[160],[TDREV_LINES].[160],[TDREV_1].[Revision]) AS 160R,
Compare([NEDS-E-101].[20],[TDREV_LINES].[20],[TDREV_1].[Revision]) AS 20R,
Compare([NEDS-E-101].[161],[TDREV_LINES].[161],[TDREV_1].[Revision]) AS 161R,
Compare([NEDS-E-101].[162],[TDREV_LINES].[162],[TDREV_1].[Revision]) AS 162R,
Compare([NEDS-E-101].[163],[TDREV_LINES].[163],[TDREV_1].[Revision]) AS 163R,
Compare([NEDS-E-101].[164],[TDREV_LINES].[164],[TDREV_1].[Revision]) AS 164R,
Compare([NEDS-E-101].[166],[TDREV_LINES].[166],[TDREV_1].[Revision]) AS 166R,
Compare([NEDS-E-101].[167],[TDREV_LINES].[167],[TDREV_1].[Revision]) AS 167R,
Compare([NEDS-E-101].[168],[TDREV_LINES].[168],[TDREV_1].[Revision]) AS 168R,
Compare([NEDS-E-101].[169],[TDREV_LINES].[169],[TDREV_1].[Revision]) AS 169R,
Compare([NEDS-E-101].[174],[TDREV_LINES].[174],[TDREV_1].[Revision]) AS 174R,
Compare([NEDS-E-101].[170],[TDREV_LINES].[170],[TDREV_1].[Revision]) AS 170R,
Compare([NEDS-E-101].[171],[TDREV_LINES].[171],[TDREV_1].[Revision]) AS 171R,
Compare([NEDS-E-101].[172],[TDREV_LINES].[172],[TDREV_1].[Revision]) AS 172R,
Compare([NEDS-E-101].[173],[TDREV_LINES].[173],[TDREV_1].[Revision]) AS 173R,
Compare([NEDS-E-101].[178],[TDREV_LINES].[178],[TDREV_1].[Revision]) AS 178R,
Compare([NEDS-E-101].[175],[TDREV_LINES].[175],[TDREV_1].[Revision]) AS 175R,
Compare([NEDS-E-101].[179],[TDREV_LINES].[179],[TDREV_1].[Revision]) AS 179R,
Compare([NEDS-E-101].[180],[TDREV_LINES].[180],[TDREV_1].[Revision]) AS 180R,
Compare([NEDS-E-101].[181],[TDREV_LINES].[181],[TDREV_1].[Revision]) AS 181R,
Compare([NEDS-E-101].[182],[TDREV_LINES].[182],[TDREV_1].[Revision]) AS 182R,
Compare([NEDS-E-101].[165],[TDREV_LINES].[165],[TDREV_1].[Revision]) AS 165R,
Compare([NEDS-E-101].[183],[TDREV_LINES].[183],[TDREV_1].[Revision]) AS 183R,
Compare([NEDS-E-101].[21],[TDREV_LINES].[21],[TDREV_1].[Revision]) AS 21R,
Compare([NEDS-E-101].[176],[TDREV_LINES].[176],[TDREV_1].[Revision]) AS 176R,
Compare([NEDS-E-101].[22],[TDREV_LINES].[22],[TDREV_1].[Revision]) AS 22R,
Compare([NEDS-E-101].[177],[TDREV_LINES].[177],[TDREV_1].[Revision]) AS 177R,
Compare([NEDS-E-101].[23],[TDREV_LINES].[23],[TDREV_1].[Revision]) AS 23R,
Compare([NEDS-E-101].[24],[TDREV_LINES].[24],[TDREV_1].[Revision]) AS 24R,
Compare([NEDS-E-101].[25],[TDREV_LINES].[25],[TDREV_1].[Revision]) AS 25R,
Compare([NEDS-E-101].[26],[TDREV_LINES].[26],[TDREV_1].[Revision]) AS 26R,
Compare([NEDS-E-101].[27],[TDREV_LINES].[27],[TDREV_1].[Revision]) AS 27R,
Compare([NEDS-E-101].[184],[TDREV_LINES].[184],[TDREV_1].[Revision]) AS 184R,
Compare([NEDS-E-101].[220],[TDREV_LINES].[220],[TDREV_1].[Revision]) AS 220R,
Compare([NEDS-E-101].[221],[TDREV_LINES].[221],[TDREV_1].[Revision]) AS 221R,
Compare([NEDS-E-101].[185],[TDREV_LINES].[185],[TDREV_1].[Revision]) AS 185R,
Compare([NEDS-E-101].[186],[TDREV_LINES].[186],[TDREV_1].[Revision]) AS 186R,
Compare([NEDS-E-101].[187],[TDREV_LINES].[187],[TDREV_1].[Revision]) AS 187R,
Compare([NEDS-E-101].[192],[TDREV_LINES].[192],[TDREV_1].[Revision]) AS 192R,
Compare([NEDS-E-101].[188],[TDREV_LINES].[188],[TDREV_1].[Revision]) AS 188R,
Compare([NEDS-E-101].[189],[TDREV_LINES].[189],[TDREV_1].[Revision]) AS 189R,
Compare([NEDS-E-101].[190],[TDREV_LINES].[190],[TDREV_1].[Revision]) AS 190R,
Compare([NEDS-E-101].[191],[TDREV_LINES].[191],[TDREV_1].[Revision]) AS 191R,
Compare([NEDS-E-101].[196],[TDREV_LINES].[196],[TDREV_1].[Revision]) AS 196R,
Compare([NEDS-E-101].[193],[TDREV_LINES].[193],[TDREV_1].[Revision]) AS 193R,
Compare([NEDS-E-101].[197],[TDREV_LINES].[197],[TDREV_1].[Revision]) AS 197R,
Compare([NEDS-E-101].[198],[TDREV_LINES].[198],[TDREV_1].[Revision]) AS 198R,
Compare([NEDS-E-101].[199],[TDREV_LINES].[199],[TDREV_1].[Revision]) AS 199R,
Compare([NEDS-E-101].[200],[TDREV_LINES].[200],[TDREV_1].[Revision]) AS 200R,
Compare([NEDS-E-101].[28],[TDREV_LINES].[28],[TDREV_1].[Revision]) AS 28R,
Compare([NEDS-E-101].[159],[TDREV_LINES].[159],[TDREV_1].[Revision]) AS 159R,
Compare([NEDS-E-101].[29],[TDREV_LINES].[29],[TDREV_1].[Revision]) AS 29R,
Compare([NEDS-E-101].[30],[TDREV_LINES].[30],[TDREV_1].[Revision]) AS 30R,
Compare([NEDS-E-101].[32],[TDREV_LINES].[32],[TDREV_1].[Revision]) AS 32R,
Compare([NEDS-E-101].[202],[TDREV_LINES].[202],[TDREV_1].[Revision]) AS 202R,
Compare([NEDS-E-101].[204],[TDREV_LINES].[204],[TDREV_1].[Revision]) AS 204R,
Compare([NEDS-E-101].[209],[TDREV_LINES].[209],[TDREV_1].[Revision]) AS 209R,
Compare([NEDS-E-101].[210],[TDREV_LINES].[210],[TDREV_1].[Revision]) AS 210R,
Compare([NEDS-E-101].[215],[TDREV_LINES].[215],[TDREV_1].[Revision]) AS 215R,
Compare([NEDS-E-101].[33],[TDREV_LINES].[33],[TDREV_1].[Revision]) AS 33R,
Compare([NEDS-E-101].[205],[TDREV_LINES].[205],[TDREV_1].[Revision]) AS 205R,
Compare([NEDS-E-101].[211],[TDREV_LINES].[211],[TDREV_1].[Revision]) AS 211R,
Compare([NEDS-E-101].[203],[TDREV_LINES].[203],[TDREV_1].[Revision]) AS 203R,
Compare([NEDS-E-101].[216],[TDREV_LINES].[216],[TDREV_1].[Revision]) AS 216R,
Compare([NEDS-E-101].[34],[TDREV_LINES].[34],[TDREV_1].[Revision]) AS 34R,
Compare([NEDS-E-101].[206],[TDREV_LINES].[206],[TDREV_1].[Revision]) AS 206R,
Compare([NEDS-E-101].[212],[TDREV_LINES].[212],[TDREV_1].[Revision]) AS 212R,
Compare([NEDS-E-101].[35],[TDREV_LINES].[35],[TDREV_1].[Revision]) AS 35R,
Compare([NEDS-E-101].[207],[TDREV_LINES].[207],[TDREV_1].[Revision]) AS 207R,
Compare([NEDS-E-101].[213],[TDREV_LINES].[213],[TDREV_1].[Revision]) AS 213R,
Compare([NEDS-E-101].[36],[TDREV_LINES].[36],[TDREV_1].[Revision]) AS 36R,
Compare([NEDS-E-101].[208],[TDREV_LINES].[208],[TDREV_1].[Revision]) AS 208R,
Compare([NEDS-E-101].[214],[TDREV_LINES].[214],[TDREV_1].[Revision]) AS 214R,
Compare([NEDS-E-101].[37],[TDREV_LINES].[37],[TDREV_1].[Revision]) AS 37R,
Compare([NEDS-E-101].[38],[TDREV_LINES].[38],[TDREV_1].[Revision]) AS 38R,
Compare([NEDS-E-101].[39],[TDREV_LINES].[39],[TDREV_1].[Revision]) AS 39R,
Compare([NEDS-E-101].[40],[TDREV_LINES].[40],[TDREV_1].[Revision]) AS 40R,
Compare([NEDS-E-101].[41],[TDREV_LINES].[41],[TDREV_1].[Revision]) AS 41R,
Compare([NEDS-E-101].[42],[TDREV_LINES].[42],[TDREV_1].[Revision]) AS 42R,
Compare([NEDS-E-101].[43],[TDREV_LINES].[43],[TDREV_1].[Revision]) AS 43R,
Compare([NEDS-E-101].[44],[TDREV_LINES].[44],[TDREV_1].[Revision]) AS 44R,
Compare([NEDS-E-101].[45],[TDREV_LINES].[45],[TDREV_1].[Revision]) AS 45R,
Compare([NEDS-E-101].[46],[TDREV_LINES].[46],[TDREV_1].[Revision]) AS 46R,
Compare([NEDS-E-101].[47],[TDREV_LINES].[47],[TDREV_1].[Revision]) AS 47R,
Compare([NEDS-E-101].[48],[TDREV_LINES].[48],[TDREV_1].[Revision]) AS 48R,
Compare([NEDS-E-101].[49],[TDREV_LINES].[49],[TDREV_1].[Revision]) AS 49R,
Compare([NEDS-E-101].[50],[TDREV_LINES].[50],[TDREV_1].[Revision]) AS 50R,
Compare([NEDS-E-101].[51],[TDREV_LINES].[51],[TDREV_1].[Revision]) AS 51R,
Compare([NEDS-E-101].[52],[TDREV_LINES].[52],[TDREV_1].[Revision]) AS 52R,
Compare([NEDS-E-101].[53],[TDREV_LINES].[53],[TDREV_1].[Revision]) AS 53R,
Compare([NEDS-E-101].[54],[TDREV_LINES].[54],[TDREV_1].[Revision]) AS 54R,
Compare([NEDS-E-101].[55],[TDREV_LINES].[55],[TDREV_1].[Revision]) AS 55R,
Compare([NEDS-E-101].[56],[TDREV_LINES].[56],[TDREV_1].[Revision]) AS 56R,
Compare([NEDS-E-101].[57],[TDREV_LINES].[57],[TDREV_1].[Revision]) AS 57R,
Compare([NEDS-E-101].[58],[TDREV_LINES].[58],[TDREV_1].[Revision]) AS 58R,
Compare([NEDS-E-101].[59],[TDREV_LINES].[59],[TDREV_1].[Revision]) AS 59R,
Compare([NEDS-E-101].[60],[TDREV_LINES].[60],[TDREV_1].[Revision]) AS 60R,
Compare([NEDS-E-101].[61],[TDREV_LINES].[61],[TDREV_1].[Revision]) AS 61R,
Compare([NEDS-E-101].[62],[TDREV_LINES].[62],[TDREV_1].[Revision]) AS 62R,
Compare([NEDS-E-101].[63],[TDREV_LINES].[63],[TDREV_1].[Revision]) AS 63R,
Compare([NEDS-E-101].[64],[TDREV_LINES].[64],[TDREV_1].[Revision]) AS 64R,
Compare([NEDS-E-101].[65],[TDREV_LINES].[65],[TDREV_1].[Revision]) AS 65R,
[NEDS-E-101].*
FROM [NEDS-E-101], (TDREV INNER JOIN TDREV_LINES ON TDREV.REVISIONID =
TDREV_LINES.REVISIONID) INNER JOIN TDREV AS TDREV_1 ON TDREV.DOCUMENT =
TDREV_1.DOCUMENT
WHERE (((TDREV.CURRENTISSUE)=1) AND ((TDREV.DOCUMENT)=GetDocNumber()) AND
((TDREV_1.REVISIONID)>[TDREV].[RevisionID]));

Just writing this down I realize how ridiculous this query must seem, but I
don't know how else to get the results I'm looking for. Are there tricks I
don't know about? I'm pretty sure I've violated some of the criteria Jerry
Whittle posted, but I don't know what else I'm supposed to do. Any pointers
would appreciated.

Thanks!
Graham

Ken Snell \(MVP\) · Jan 25, 2008

Your query's SQL statement is very long ... I, and I'm sure others, are not
going to go through the entire query (took two posts to show it?) and try to
understand the entire thing. Have you tried breaking the query into smaller
queries and verifying that each of the smaller queries works correctly?

Common problems that cause "too complex" error are data errors (trying to
divide by zero, exceeding the size of data that a field can hold [e.g.,
trying to put a number larger than about 33000 into an integer field],
having a date value that is not recognizable as a date, etc.).

--

Ken Snell
<MS ACCESS MVP>

AlwaysFroosh! said:
I wonder if anyone of you is still watching this post, I guess this thread
is
a couple years old now!

I too am having a problem with my query being too complex, maybe someone
could help me simplify it?

We use a database to house technical information on equipment. In this
case,
the piece of equipment is a heat exchanger. To generate a datasheet for
this
exchanger, I create a query that produces one record, the record of all
the
information for that particular heat exchanger.

I use a series of what I think of as "piggy backing" queries. First I
create
a recordset of the commodity properties that are coming into and going out
of
the exchanger. Then I build another query using the commodities query and
a
bunch of tables with data that is specific to that exchanger. Then I have
another query that numbers all of the fields so that I can backup all
queries
for different equipment into a table with just numbers as the column
names.
Finally my last query looks at the numbered query, and the backup table
and
compare values so that the revisions can be flagged if there's a
difference.

Here are the queries:

Commodity Query:
SELECT SOLODATA.SOLODATALINK, PRIM_IN_COMMONDATA.COMMODITY AS [PRIMIN
COMMODITY],
IIf([PRIM_IN_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([PRIM_IN_COMMONDATA].[MASS_FLOW_1],[PRIM_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([PRIM_IN_COMMONDATA].[VOL_FLOW_1],[PRIM_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [PRIMIN FLOW 1],
IIf([PRIM_IN_COMMONDATA].[VOL_FLOW_3]=0,NumberWithUnits([PRIM_IN_COMMONDATA].[MASS_FLOW_3],[PRIM_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([PRIM_IN_COMMONDATA].[VOL_FLOW_3],[PRIM_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [PRIMIN FLOW 2],
NumberWithUnits([PRIM_IN_COMMONDATA].[OPERTEMPERATURE],[PRIM_IN_COMMONDATA].[TEMPERATUREUNITS])
AS [PRIMIN OPERATING TEMPERATURE 1],
NumberWithUnits([PRIM_IN_COMMONDATA].[TEMPERATURE_3],[PRIM_IN_COMMONDATA].[TEMPERATUREUNITS])
AS [PRIMIN OPERATING TEMPERATURE 2],
NumberWithUnits([PRIM_IN_COMMONDATA].[OPERPRESSURE],[PRIM_IN_COMMONDATA].[PRESSUREUNITS])
AS [PRIM INLET PRESSURE 1],
NumberWithUnits([PRIM_IN_COMMONDATA].[PRESSURE_3],[PRIM_IN_COMMONDATA].[PRESSUREUNITS])
AS [PRIM INLET PRESSURE 2],
NumberWithUnits([PRIM_IN_COMMONDATA].[DENSITY_1],[PRIM_IN_COMMONDATA].[DENSITY_UNITS])
AS [PRIMIN DENSITY 1],
NumberWithUnits([PRIM_IN_COMMONDATA].[DENSITY_2],[PRIM_IN_COMMONDATA].[DENSITY_UNITS])
AS [PRIMIN DENSITY 2],
NumberWithUnits([PRIM_IN_COMMONDATA].[VISCOSITY_1],[PRIM_IN_COMMONDATA].[VISCOSITY_UNITS])
AS [PRIMIN VISCOSITY 1],
NumberWithUnits([PRIM_IN_COMMONDATA].[VISCOSITY_2],[PRIM_IN_COMMONDATA].[VISCOSITY_UNITS])
AS [PRIMIN VISCOSITY 2], PRIM_IN_COMMONDATA.CEXTA3 AS [PRIMIN SPEC HEAT
1],
PRIM_IN_COMMONDATA.CEXTA6 AS [PRIMIN SPEC HEAT 2],
PRIM_IN_COMMONDATA.CEXTA5
AS [PRIMIN THERMAL COND 1], PRIM_IN_COMMONDATA.CEXTA8 AS [PRIMIN THERMAL
COND
2], PRIM_OUT_COMMONDATA.COMMODITY AS [PRIMOUT COMMODITY],
IIf([PRIM_OUT_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([PRIM_OUT_COMMONDATA].[MASS_FLOW_1],[PRIM_OUT_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([PRIM_OUT_COMMONDATA].[VOL_FLOW_1],[PRIM_OUT_COMMONDATA].[VOL_FLOW_UNITS]))
AS [PRIMOUT FLOW 1],
IIf([PRIM_OUT_COMMONDATA].[VOL_FLOW_3]=0,NumberWithUnits([PRIM_OUT_COMMONDATA].[MASS_FLOW_3],[PRIM_OUT_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([PRIM_OUT_COMMONDATA].[VOL_FLOW_3],[PRIM_OUT_COMMONDATA].[VOL_FLOW_UNITS]))
AS [PRIMOUT FLOW 2],
NumberWithUnits([PRIM_OUT_COMMONDATA].[OPERTEMPERATURE],[PRIM_OUT_COMMONDATA].[TEMPERATUREUNITS])
AS [PRIMOUT OPERATING TEMPERATURE 1],
NumberWithUnits([PRIM_OUT_COMMONDATA].[TEMPERATURE_3],[PRIM_OUT_COMMONDATA].[TEMPERATUREUNITS])
AS [PRIMOUT OPERATING TEMPERATURE 2],
NumberWithUnits(CDbl(Nz([PRIM_OUT_COMMONDATA].[OPERPRESSURE],0))-CDbl(Nz([PRIM_IN_COMMONDATA].[OPERPRESSURE],0)),[PRIM_OUT_COMMONDATA].[PRESSUREUNITS])
AS [PRIM DIFFERENTIAL PRESSURE 1],
NumberWithUnits(CDbl(Nz([PRIM_OUT_COMMONDATA].[PRESSURE_3],0))-CDbl(Nz([PRIM_IN_COMMONDATA].[PRESSURE_3],0)),[PRIM_OUT_COMMONDATA].[PRESSUREUNITS])
AS [PRIM DIFFERENTIAL PRESSURE 2],
NumberWithUnits([PRIM_OUT_COMMONDATA].[DENSITY_1],[PRIM_OUT_COMMONDATA].[DENSITY_UNITS])
AS [PRIMOUT DENSITY 1],
NumberWithUnits([PRIM_OUT_COMMONDATA].[DENSITY_2],[PRIM_OUT_COMMONDATA].[DENSITY_UNITS])
AS [PRIMOUT DENSITY 2],
NumberWithUnits([PRIM_OUT_COMMONDATA].[VISCOSITY_1],[PRIM_OUT_COMMONDATA].[VISCOSITY_UNITS])
AS [PRIMOUT VISCOSITY 1],
NumberWithUnits([PRIM_OUT_COMMONDATA].[VISCOSITY_2],[PRIM_OUT_COMMONDATA].[VISCOSITY_UNITS])
AS [PRIMOUT VISCOSITY 2], PRIM_OUT_COMMONDATA.CEXTA3 AS [PRIMOUT SPEC HEAT
1], PRIM_OUT_COMMONDATA.CEXTA6 AS [PRIMOUT SPEC HEAT 2],
PRIM_OUT_COMMONDATA.CEXTA5 AS [PRIMOUT THERMAL COND 1],
PRIM_OUT_COMMONDATA.CEXTA8 AS [PRIMOUT THERMAL COND 2],
SEC_IN_COMMONDATA.COMMODITY AS [SECIN COMMODITY],
IIf([SEC_IN_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([SEC_IN_COMMONDATA].[MASS_FLOW_1],[SEC_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([SEC_IN_COMMONDATA].[VOL_FLOW_1],[SEC_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [SECIN FLOW 1],
IIf([SEC_IN_COMMONDATA].[VOL_FLOW_3]=0,NumberWithUnits([SEC_IN_COMMONDATA].[MASS_FLOW_3],[SEC_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([SEC_IN_COMMONDATA].[VOL_FLOW_3],[SEC_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [SECIN FLOW 2],
NumberWithUnits([SEC_IN_COMMONDATA].[OPERTEMPERATURE],[SEC_IN_COMMONDATA].[TEMPERATUREUNITS])
AS [SECIN OPERATING TEMPERATURE 1],
NumberWithUnits([SEC_IN_COMMONDATA].[TEMPERATURE_3],[SEC_IN_COMMONDATA].[TEMPERATUREUNITS])
AS [SECIN OPERATING TEMPERATURE 2],
NumberWithUnits([SEC_IN_COMMONDATA].[OPERPRESSURE],[SEC_IN_COMMONDATA].[PRESSUREUNITS])
AS [SEC INLET PRESSURE 1],
NumberWithUnits([SEC_IN_COMMONDATA].[PRESSURE_3],[SEC_IN_COMMONDATA].[PRESSUREUNITS])
AS [SEC INLET PRESSURE 2],
NumberWithUnits([SEC_IN_COMMONDATA].[DENSITY_1],[SEC_IN_COMMONDATA].[DENSITY_UNITS])
AS [SECIN DENSITY 1],
NumberWithUnits([SEC_IN_COMMONDATA].[DENSITY_2],[SEC_IN_COMMONDATA].[DENSITY_UNITS])
AS [SECIN DENSITY 2],
NumberWithUnits([SEC_IN_COMMONDATA].[VISCOSITY_1],[SEC_IN_COMMONDATA].[VISCOSITY_UNITS])
AS [SECIN VISCOSITY 1],
NumberWithUnits([SEC_IN_COMMONDATA].[VISCOSITY_2],[SEC_IN_COMMONDATA].[VISCOSITY_UNITS])
AS [SECIN VISCOSITY 2], SEC_IN_COMMONDATA.CEXTA3 AS [SECIN SPEC HEAT 1],
SEC_IN_COMMONDATA.CEXTA6 AS [SECIN SPEC HEAT 2], SEC_IN_COMMONDATA.CEXTA5
AS
[SECIN THERMAL COND 1], SEC_IN_COMMONDATA.CEXTA8 AS [SECIN THERMAL COND
2],
SEC_OUT_COMMONDATA.COMMODITY AS [SECOUT COMMODITY],
IIf([SEC_OUT_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([SEC_OUT_COMMONDATA].[MASS_FLOW_1],[SEC_OUT_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([SEC_OUT_COMMONDATA].[VOL_FLOW_1],[SEC_OUT_COMMONDATA].[VOL_FLOW_UNITS]))
AS [SECOUT FLOW 1],
IIf([SEC_OUT_COMMONDATA].[VOL_FLOW_3]=0,NumberWithUnits([SEC_OUT_COMMONDATA].[MASS_FLOW_3],[SEC_OUT_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([SEC_OUT_COMMONDATA].[VOL_FLOW_3],[SEC_OUT_COMMONDATA].[VOL_FLOW_UNITS]))
AS [SECOUT FLOW 2],
NumberWithUnits([SEC_OUT_COMMONDATA].[OPERTEMPERATURE],[SEC_OUT_COMMONDATA].[TEMPERATUREUNITS])
AS [SECOUT OPERATING TEMPERATURE 1],
NumberWithUnits([SEC_OUT_COMMONDATA].[TEMPERATURE_3],[SEC_OUT_COMMONDATA].[TEMPERATUREUNITS])
AS [SECOUT OPERATING TEMPERATURE 2],
NumberWithUnits(CDbl(Nz([SEC_OUT_COMMONDATA].[OPERPRESSURE],0))-CDbl(Nz([SEC_IN_COMMONDATA].[OPERPRESSURE],0)),[SEC_OUT_COMMONDATA].[PRESSUREUNITS])
AS [SEC DIFFERENTIAL PRESSURE 1],
NumberWithUnits(CDbl(Nz([SEC_OUT_COMMONDATA].[PRESSURE_3],0))-CDbl(Nz([SEC_IN_COMMONDATA].[PRESSURE_3],0)),[SEC_OUT_COMMONDATA].[PRESSUREUNITS])
AS [SEC DIFFERENTIAL PRESSURE 2],
NumberWithUnits([SEC_OUT_COMMONDATA].[DENSITY_1],[SEC_OUT_COMMONDATA].[DENSITY_UNITS])
AS [SECOUT DENSITY 1],
NumberWithUnits([SEC_OUT_COMMONDATA].[DENSITY_2],[SEC_OUT_COMMONDATA].[DENSITY_UNITS])
AS [SECOUT DENSITY 2],
NumberWithUnits([SEC_OUT_COMMONDATA].[VISCOSITY_1],[SEC_OUT_COMMONDATA].[VISCOSITY_UNITS])
AS [SECOUT VISCOSITY 1],
NumberWithUnits([SEC_OUT_COMMONDATA].[VISCOSITY_2],[SEC_OUT_COMMONDATA].[VISCOSITY_UNITS])
AS [SECOUT VISCOSITY 2], SEC_OUT_COMMONDATA.CEXTA3 AS [SECOUT SPEC HEAT
1],
SEC_OUT_COMMONDATA.CEXTA6 AS [SECOUT SPEC HEAT 2],
SEC_OUT_COMMONDATA.CEXTA5
AS [SECOUT THERMAL COND 1], SEC_OUT_COMMONDATA.CEXTA8 AS [SECOUT THERMAL
COND
2], JACK_IN_COMMONDATA.COMMODITY AS [JACKIN COMMODITY],
IIf([JACK_IN_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([JACK_IN_COMMONDATA].[MASS_FLOW_1],[JACK_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([JACK_IN_COMMONDATA].[VOL_FLOW_1],[JACK_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [JACKIN FLOW 1],
IIf([JACK_IN_COMMONDATA].[VOL_FLOW_3]=0,NumberWithUnits([JACK_IN_COMMONDATA].[MASS_FLOW_3],[JACK_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([JACK_IN_COMMONDATA].[VOL_FLOW_3],[JACK_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [JACKIN FLOW 2],
NumberWithUnits([JACK_IN_COMMONDATA].[OPERTEMPERATURE],[JACK_IN_COMMONDATA].[TEMPERATUREUNITS])
AS [JACKIN OPERATING TEMPERATURE 1],
NumberWithUnits([JACK_IN_COMMONDATA].[TEMPERATURE_3],[JACK_IN_COMMONDATA].[TEMPERATUREUNITS])
AS [JACKIN OPERATING TEMPERATURE 2],
NumberWithUnits([JACK_IN_COMMONDATA].[OPERPRESSURE],[JACK_IN_COMMONDATA].[PRESSUREUNITS])
AS [JACK INLET PRESSURE 1],
NumberWithUnits([JACK_IN_COMMONDATA].[PRESSURE_3],[JACK_IN_COMMONDATA].[PRESSUREUNITS])
AS [JACK INLET PRESSURE 2],
NumberWithUnits([JACK_IN_COMMONDATA].[DENSITY_1],[JACK_IN_COMMONDATA].[DENSITY_UNITS])
AS [JACKIN DENSITY 1],
NumberWithUnits([JACK_IN_COMMONDATA].[DENSITY_2],[JACK_IN_COMMONDATA].[DENSITY_UNITS])
AS [JACKIN DENSITY 2],
NumberWithUnits([JACK_IN_COMMONDATA].[VISCOSITY_1],[JACK_IN_COMMONDATA].[VISCOSITY_UNITS])
AS [JACKIN VISCOSITY 1],
NumberWithUnits([JACK_IN_COMMONDATA].[VISCOSITY_2],[JACK_IN_COMMONDATA].[VISCOSITY_UNITS])
AS [JACKIN VISCOSITY 2], JACK_IN_COMMONDATA.CEXTA3 AS [JACKIN SPEC HEAT
1],
JACK_IN_COMMONDATA.CEXTA6 AS [JACKIN SPEC HEAT 2],
JACK_IN_COMMONDATA.CEXTA5
AS [JACKIN THERMAL COND 1], JACK_IN_COMMONDATA.CEXTA8 AS [JACKIN THERMAL
COND
2], JACK_OUT_COMMONDATA.COMMODITY AS [JACKOUT COMMODITY],
IIf([JACK_OUT_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([JACK_OUT_COMMONDATA].[MASS_FLOW_1],[JACK_OUT_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([JACK_OUT_COMMONDATA].[VOL_FLOW_1],[JACK_OUT_COMMONDATA].[VOL_FLOW_UNITS]))
AS [JACKOUT FLOW 1],
IIf([JACK_OUT_COMMONDATA].[VOL_FLOW_3]=0,NumberWithUnits([JACK_OUT_COMMONDATA].[MASS_FLOW_3],[JACK_OUT_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([JACK_OUT_COMMONDATA].[VOL_FLOW_3],[JACK_OUT_COMMONDATA].[VOL_FLOW_UNITS]))
AS [JACKOUT FLOW 2],
NumberWithUnits([JACK_OUT_COMMONDATA].[OPERTEMPERATURE],[JACK_OUT_COMMONDATA].[TEMPERATUREUNITS])
AS [JACKOUT OPERATING TEMPERATURE 1],
NumberWithUnits([JACK_OUT_COMMONDATA].[TEMPERATURE_3],[JACK_OUT_COMMONDATA].[TEMPERATUREUNITS])
AS [JACKOUT OPERATING TEMPERATURE 2],
NumberWithUnits(CDbl(Nz([JACK_OUT_COMMONDATA].[OPERPRESSURE],0))-CDbl(Nz([JACK_IN_COMMONDATA].[OPERPRESSURE],0)),[JACK_OUT_COMMONDATA].[PRESSUREUNITS])
AS [JACK DIFFERENTIAL PRESSURE 1],
NumberWithUnits(CDbl(Nz([JACK_OUT_COMMONDATA].[PRESSURE_3],0))-CDbl(Nz([JACK_IN_COMMONDATA].[PRESSURE_3],0)),[JACK_OUT_COMMONDATA].[PRESSUREUNITS])
AS [JACK DIFFERENTIAL PRESSURE 2],
NumberWithUnits([JACK_OUT_COMMONDATA].[DENSITY_1],[JACK_OUT_COMMONDATA].[DENSITY_UNITS])
AS [JACKOUT DENSITY 1],
NumberWithUnits([JACK_OUT_COMMONDATA].[DENSITY_2],[JACK_OUT_COMMONDATA].[DENSITY_UNITS])
AS [JACKOUT DENSITY 2],
NumberWithUnits([JACK_OUT_COMMONDATA].[VISCOSITY_1],[JACK_OUT_COMMONDATA].[VISCOSITY_UNITS])
AS [JACKOUT VISCOSITY 1],
NumberWithUnits([JACK_OUT_COMMONDATA].[VISCOSITY_2],[JACK_OUT_COMMONDATA].[VISCOSITY_UNITS])
AS [JACKOUT VISCOSITY 2], JACK_OUT_COMMONDATA.CEXTA3 AS [JACKOUT SPEC HEAT
1], JACK_OUT_COMMONDATA.CEXTA6 AS [JACKOUT SPEC HEAT 2],
JACK_OUT_COMMONDATA.CEXTA5 AS [JACKOUT THERMAL COND 1],
JACK_OUT_COMMONDATA.CEXTA8 AS [JACKOUT THERMAL COND 2]
FROM (((((SOLODATA LEFT JOIN COMMONDATA AS PRIM_IN_COMMONDATA ON
SOLODATA.EXTN1 = PRIM_IN_COMMONDATA.COMMONDATALINK) LEFT JOIN COMMONDATA
AS
PRIM_OUT_COMMONDATA ON SOLODATA.EXTN2 =
PRIM_OUT_COMMONDATA.COMMONDATALINK)
LEFT JOIN COMMONDATA AS SEC_IN_COMMONDATA ON SOLODATA.EXTN3 =
SEC_IN_COMMONDATA.COMMONDATALINK) LEFT JOIN COMMONDATA AS
SEC_OUT_COMMONDATA
ON SOLODATA.EXTN4 = SEC_OUT_COMMONDATA.COMMONDATALINK) LEFT JOIN
COMMONDATA
AS JACK_IN_COMMONDATA ON SOLODATA.EXTN5 =
JACK_IN_COMMONDATA.COMMONDATALINK)
LEFT JOIN COMMONDATA AS JACK_OUT_COMMONDATA ON SOLODATA.EXTN6 =
JACK_OUT_COMMONDATA.COMMONDATALINK;

Equipment Query:
SELECT TitleCase([SOLODATA].[COMPONENTDESCRIPTION]) AS [COMPONENT
DESCRIPTION], ("EDS-" & GetProjectNumber() & "-" & [SOLODATA].[TAG2] &
Left([SOLODATA].[TAG3],GetClientTagLength())) AS EDS, "PROJECT " &
GetProjectNumber() AS PROJECT, SOLODATA2_V.EXTA140 AS [CLIENT DS], "GPP-"
&
GetProjectNumber() & "-" & [SOLODATA].[EXTA1] AS GPP, SOLODATA.EXTA2 AS
PO,
TitleCase([PROJDATA].[PROJECT_NAME]) AS PROJECT_NAME,
TitleCase([PROJDATA].[CLIENT]) AS CLIENT, PROJDATA.CLIENT_PROJECT_NUM,
PROJDATA.CLIENT_PO, PROJDATA.SITE, SOLODATA.MANUFACTURER, SOLODATA.EXTA11
AS
[PRIMARY FLUID ALLOCATION], COMMODITIES.[PRIMIN COMMODITY] AS [PRIMIN
COMMODITY], IIf([SOLODATA].[EXTN12],[PRIMIN FLOW 2],[PRIMIN FLOW 1]) AS
[PRIMIN FLOW], IIf([SOLODATA].[EXTN12],[PRIMIN OPERATING TEMPERATURE
2],[PRIMIN OPERATING TEMPERATURE 1]) AS [PRIMIN OPERATING TEMPERATURE],
IIf([SOLODATA].[EXTN12],[PRIM INLET PRESSURE 2],[PRIM INLET PRESSURE 1])
AS
[PRIM INLET PRESSURE], IIf([SOLODATA].[EXTN12],[PRIMIN DENSITY 2],[PRIMIN
DENSITY 1]) AS [PRIMIN DENSITY], IIf([SOLODATA].[EXTN12],[PRIMIN VISCOSITY
2],[PRIMIN VISCOSITY 1]) AS [PRIMIN VISCOSITY],
IIf([SOLODATA].[EXTN12],[PRIMIN SPEC HEAT 2],[PRIMIN SPEC HEAT 1]) AS
[PRIMIN
SPEC HEAT], IIf([SOLODATA].[EXTN12],[PRIMIN THERMAL COND 2],[PRIMIN
THERMAL
COND 1]) AS [PRIMIN THERMAL COND], COMMODITIES.[PRIMOUT COMMODITY] AS
[PRIMOUT COMMODITY], IIf([SOLODATA].[EXTN12],[PRIMOUT FLOW 2],[PRIMOUT
FLOW
1]) AS [PRIMOUT FLOW], IIf([SOLODATA].[EXTN12],[PRIMOUT OPERATING
TEMPERATURE
2],[PRIMOUT OPERATING TEMPERATURE 1]) AS [PRIMOUT OPERATING TEMPERATURE],
IIf([SOLODATA].[EXTN12],[PRIM DIFFERENTIAL PRESSURE 2],[PRIM DIFFERENTIAL
PRESSURE 1]) AS [PRIM DIFFERENTIAL PRESSURE],
IIf([SOLODATA].[EXTN12],[PRIMOUT DENSITY 2],[PRIMOUT DENSITY 1]) AS
[PRIMOUT
DENSITY], IIf([SOLODATA].[EXTN12],[PRIMOUT VISCOSITY 2],[PRIMOUT VISCOSITY
1]) AS [PRIMOUT VISCOSITY], IIf([SOLODATA].[EXTN12],[PRIMOUT SPEC HEAT
2],[PRIMOUT SPEC HEAT 1]) AS [PRIMOUT SPEC HEAT],
IIf([SOLODATA].[EXTN12],[PRIMOUT THERMAL COND 2],[PRIMOUT THERMAL COND 1])
AS
[PRIMOUT THERMAL COND], SOLODATA.EXTA12 AS [SECONDARY FLUID ALLOCATION],
COMMODITIES.[SECIN COMMODITY] AS [SECIN COMMODITY],
IIf([SOLODATA].[EXTN12],[SECIN FLOW 2],[SECIN FLOW 1]) AS [SECIN FLOW],
IIf([SOLODATA].[EXTN12],[SECIN OPERATING TEMPERATURE 2],[SECIN OPERATING
TEMPERATURE 1]) AS [SECIN OPERATING TEMPERATURE],
IIf([SOLODATA].[EXTN12],[SEC INLET PRESSURE 2],[SEC INLET PRESSURE 1]) AS
[SEC INLET PRESSURE], IIf([SOLODATA].[EXTN12],[SECIN DENSITY 2],[SECIN
DENSITY 1]) AS [SECIN DENSITY], IIf([SOLODATA].[EXTN12],[SECIN VISCOSITY
2],[SECIN VISCOSITY 1]) AS [SECIN VISCOSITY],
IIf([SOLODATA].[EXTN12],[SECIN
SPEC HEAT 2],[SECIN SPEC HEAT 1]) AS [SECIN SPEC HEAT],
IIf([SOLODATA].[EXTN12],[SECIN THERMAL COND 2],[SECIN THERMAL COND 1]) AS
[SECIN THERMAL COND], COMMODITIES.[SECOUT COMMODITY] AS [SECOUT
COMMODITY],
IIf([SOLODATA].[EXTN12],[SECOUT FLOW 2],[SECOUT FLOW 1]) AS [SECOUT FLOW],
IIf([SOLODATA].[EXTN12],[SECOUT OPERATING TEMPERATURE 2],[SECOUT OPERATING
TEMPERATURE 1]) AS [SECOUT OPERATING TEMPERATURE],
IIf([SOLODATA].[EXTN12],[SEC DIFFERENTIAL PRESSURE 2],[SEC DIFFERENTIAL
PRESSURE 1]) AS [SEC DIFFERENTIAL PRESSURE],
IIf([SOLODATA].[EXTN12],[SECOUT
DENSITY 2],[SECOUT DENSITY 1]) AS [SECOUT DENSITY],
IIf([SOLODATA].[EXTN12],[SECOUT VISCOSITY 2],[SECOUT VISCOSITY 1]) AS
[SECOUT
VISCOSITY], IIf([SOLODATA].[EXTN12],[SECOUT SPEC HEAT 2],[SECOUT SPEC HEAT
1]) AS [SECOUT SPEC HEAT], IIf([SOLODATA].[EXTN12],[SECOUT THERMAL COND
2],[SECOUT THERMAL COND 1]) AS [SECOUT THERMAL COND],
NumberWithUnits([COMMONDATA].[PRESSURE_3],[COMMONDATA].[PRESSUREUNITS]) AS
[PRIM DESIGN DP],
NumberWithUnits([COMMONDATA].[PRESSURE_4],[COMMONDATA].[PRESSUREUNITS]) AS
[SEC DESIGN DP],
NumberWithUnits([SOLODATA].[DISTANCE_2],[SOLODATA].[DISTANCE_UNITS] &
"/s")
AS [PRIM VELOCITY],
NumberWithUnits([SOLODATA].[DISTANCE_3],[SOLODATA].[DISTANCE_UNITS] &
"/s")
AS [SEC VELOCITY], SOLODATA.EXTA16 AS [PRIM FOULING RESISTANCE],
SOLODATA.EXTA17 AS [SEC FOULING RESISTANCE],
NumberWithUnits([SOLODATA].[POWER_1],[SOLODATA].[POWER_UNITS]) AS [HEAT
EXCHANGED], NumberWithUnits([SOLODATA].[EXTA13],[SOLODATA].[EXTA24]) AS
[SERVICE TRANSFER RATE],
NumberWithUnits([SOLODATA].[EXTA14],[SOLODATA].[EXTA24]) AS [DIRTY
TRANSFER
RATE], NumberWithUnits([SOLODATA].[EXTA15],[SOLODATA].[EXTA24]) AS [CLEAN
TRANSFER RATE],
NumberWithUnits([SOLODATA].[TEMPERATURE_3],[SOLODATA].[TEMPERATURE_UNITS])
AS
[MEAN TEMPERATURE DIFFERENCE], IIf([SOLODATA].[EXTN10],"JACKET","") AS
[JACKET ALLOCATION], IIf([SOLODATA].[EXTN10],"IN","") AS [JACKET IN
HEADING],
IIf([SOLODATA].[EXTN10],"OUT","") AS [JACKET OUT HEADING],
IIf([SOLODATA].[EXTN10],COMMODITIES.[JACKIN COMMODITY],"") AS [JACKIN
COMMODITY], IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKIN FLOW
2],[JACKIN FLOW 1]),"") AS [JACKIN FLOW],
IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKIN OPERATING
TEMPERATURE
2],[JACKIN OPERATING TEMPERATURE 1]),"") AS [JACKIN OPERATING
TEMPERATURE],
IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACK INLET PRESSURE
2],[JACK
INLET PRESSURE 1]),"") AS [JACK INLET PRESSURE],
IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKIN DENSITY 2],[JACKIN
DENSITY 1]),"") AS [JACKIN DENSITY],
IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKIN VISCOSITY
2],[JACKIN
VISCOSITY 1]),"") AS [JACKIN VISCOSITY],
IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKIN SPEC HEAT
2],[JACKIN
SPEC HEAT 1]),"") AS [JACKIN SPEC HEAT],
IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKIN THERMAL COND
2],[JACKIN THERMAL COND 1]),"") AS [JACKIN THERMAL COND],
IIf([SOLODATA].[EXTN10],COMMODITIES.[JACKOUT COMMODITY],"") AS [JACKOUT
COMMODITY], IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKOUT FLOW
2],[JACKOUT FLOW 1]),"") AS [JACKOUT FLOW],
IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKOUT OPERATING
TEMPERATURE 2],[JACKOUT OPERATING TEMPERATURE 1]),"") AS [JACKOUT
OPERATING
TEMPERATURE], IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACK
DIFFERENTIAL PRESSURE 2],[JACK DIFFERENTIAL PRESSURE 1]),"") AS [JACK
DIFFERENTIAL PRESSURE],
IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKOUT DENSITY
2],[JACKOUT
DENSITY 1]),"") AS [JACKOUT DENSITY],
IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKOUT VISCOSITY
2],[JACKOUT VISCOSITY 1]),"") AS [JACKOUT VISCOSITY],
IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKOUT SPEC HEAT
2],[JACKOUT SPEC HEAT 1]),"") AS [JACKOUT SPEC HEAT],
IIf([SOLODATA].[EXTN10],IIf([SOLODATA].[EXTN12],[JACKOUT THERMAL COND
2],[JACKOUT THERMAL COND 1]),"") AS [JACKOUT THERMAL COND],
ShapeDimen([SOLODATA].[SDISTANCE_6],[SOLODATA].[SDISTANCE_9],[SOLODATA].[SDISTANCE_7],[SOLODATA].[SDISTANCE_8],[SOLODATA].[SDISTANCE_UNITS])
AS [SIZE],
ShapeDimenCap([SOLODATA].[SDISTANCE_6],[SOLODATA].[SDISTANCE_9],[SOLODATA].[SDISTANCE_7],[SOLODATA].[SDISTANCE_8])
AS [SIZE CAPTION], SOLODATA.EXTA3 AS TYPE,
NumberWithUnits([SOLODATA].[DISTANCE_1],[SOLODATA].[DISTANCE_UNITS] & "²")
AS
[HEAT TRANSFER SURFACE AREA],
NumberWithUnits([SOLODATA].[PRESSURE_2],[SOLODATA].[PRESSURE_UNITS] & " "
&
[SOLODATA2_V].[PRESSURE_TYPE]) AS [PRIM DESIGN PRESSURE],
NumberWithUnits([SOLODATA].[PRESSURE_1],[SOLODATA].[PRESSURE_UNITS] & " "
&
[SOLODATA2_V].[PRESSURE_TYPE]) AS [PRIM TEST PRESSURE],
NumberWithUnits([SOLODATA].[PRESSURE_4],[SOLODATA].[PRESSURE_UNITS] & " "
&
[SOLODATA2_V].[PRESSURE_TYPE]) AS [SEC DESIGN PRESSURE],
NumberWithUnits([SOLODATA].[PRESSURE_3],[SOLODATA].[PRESSURE_UNITS] & " "
&
[SOLODATA2_V].[PRESSURE_TYPE]) AS [SEC TEST PRESSURE],
IIf([SOLODATA].[EXTN10],NumberWithUnits([SOLODATA].[PRESSURE_6],[SOLODATA].[PRESSURE_UNITS]
& " " & [SOLODATA2_V].[PRESSURE_TYPE]),"") AS [JACKET DESIGN PRESSURE],
IIf([SOLODATA].[EXTN10],NumberWithUnits([SOLODATA].[PRESSURE_5],[SOLODATA].[PRESSURE_UNITS]
& " " & [SOLODATA2_V].[PRESSURE_TYPE]),"") AS [JACKET TEST PRESSURE],
NumberWithUnits([SOLODATA].[TEMPERATURE_2],[SOLODATA].[TEMPERATURE_UNITS])
AS
[PRIM DESIGN TEMPERATURE],
NumberWithUnits([SOLODATA].[TEMPERATURE_4],[SOLODATA].[TEMPERATURE_UNITS])
AS
[SEC DESIGN TEMPERATURE],
IIf([SOLODATA].[EXTN10],NumberWithUnits([SOLODATA].[TEMPERATURE_6],[SOLODATA].[TEMPERATURE_UNITS]),"")
AS [JACKET DESIGN TEMPERATURE],
NumberWithUnits([SOLODATA].[TEMPERATURE_1],[SOLODATA].[TEMPERATURE_UNITS])
AS
[MINIMUM DESIGN TEMPERATURE],
IIf([SOLODATA].[EXTN10],NumberWithUnits([SOLODATA].[TEMPERATURE_1],[SOLODATA].[TEMPERATURE_UNITS]),"")
AS [JACKET MINIMUM DESIGN TEMPERATURE],
NumberWithUnits([SOLODATA].[SDISTANCE_4],[SOLODATA].[SDISTANCE_UNITS]) AS
[PRIM CORROSION ALLOWANCE],
NumberWithUnits([SOLODATA].[SDISTANCE_5],[SOLODATA].[SDISTANCE_UNITS]) AS
[SEC CORROSION ALLOWANCE],
IIf([SOLODATA].[EXTN10],NumberWithUnits([SOLODATA].[SDISTANCE_1],[SOLODATA].[SDISTANCE_UNITS]),"")
AS [JACKET CORROSION ALLOWANCE], SOLODATA.EXTA18 AS [PRIM IN CONNECTION
DETAILS], SOLODATA.EXTA19 AS [PRIM OUT CONNECTION DETAILS],
SOLODATA.EXTA20
AS [SEC IN CONNECTION DETAILS], SOLODATA.EXTA21 AS [SEC OUT CONNECTION
DETAILS], IIf([SOLODATA].[EXTN10],[SOLODATA].[EXTA22],"") AS [JACKET IN
CONNECTION DETAILS], IIf([SOLODATA].[EXTN10],[SOLODATA].[EXTA23],"") AS
[JACKET OUT CONNECTION DETAILS], SOLODATA.EXTN11 AS [TUBE COUNT],
NumberWithUnits([SOLODATA].[SDISTANCE_2],[SOLODATA].[SDISTANCE_UNITS]) AS
[TUBE OD],
NumberWithUnits([SOLODATA].[SDISTANCE_3],[SOLODATA].[SDISTANCE_UNITS]) AS
[WALL THICKNESS],
NumberWithUnits([SOLODATA].[DISTANCE_4],[SOLODATA].[DISTANCE_UNITS]) AS
[TUBE
LENGTH], NumberWithUnits([SOLODATA].[MASS_1],[SOLODATA].[MASS_UNITS]) AS
[SHELL WEIGHT],
NumberWithUnits([SOLODATA].[MASS_2],[SOLODATA].[MASS_UNITS])
AS [TUBE BUNDLE WEIGHT],
NumberWithUnits([SOLODATA].[MASS_3],[SOLODATA].[MASS_UNITS]) AS [FILLED
WITH
WATER WEIGHT], SOLODATA.EXTA25 AS [TUBE MATERIAL], SOLODATA.EXTA26 AS
[TUBE
TYPE], SOLODATA.EXTA27 AS [SHELL MATERIAL],
IIF(SOLODATA.EXTN10,SOLODATA.EXTA28,"N/A") AS [JACKET MATERIAL],
SOLODATA.EXTA29 AS [HEAD 1 MATERIAL], SOLODATA.EXTA30 AS [HEAD 2
MATERIAL],
SOLODATA.EXTA31 AS [STATIONARY TUBESHEET MATERIAL], SOLODATA.EXTA32 AS
[FLOATING TUBESHEET MATERIAL], SOLODATA.EXTA33 AS [BAFFLES - CROSS
MATERIAL],
SOLODATA.EXTA34 AS [BAFFLES - TYPE], SOLODATA.EXTA35 AS [BAFFLES - % CUT],
NumberWithUnits([SOLODATA].[SDISTANCE_10],[SOLODATA].[SDISTANCE_UNITS]) AS
[CENTER TO CENTER SPACING],
NumberWithUnits([SOLODATA].[SDISTANCE_11],[SOLODATA].[SDISTANCE_UNITS]) AS
[INLET SIZE], SOLODATA.EXTA36 AS [TUBE SUPPORTS MATERIAL], SOLODATA.EXTA37
AS
[TUBE/TUBESHEET JOINT DETAILS], SOLODATA.EXTA38 AS [EXPANSION JOINT
DETAILS],
SOLODATA.EXTA39 AS [EXPANSION JOINT TYPE], SOLODATA.EXTA40 AS [PV² - INLET
NOZZLE], SOLODATA.EXTA41 AS [SHELL SIDE GASKET], SOLODATA.EXTA42 AS [TUBE
SIDE GASKET], SOLODATA.EXTA43 AS [FLOATING HEAD GASKET], SOLODATA.EXTA44
AS
[CODE REQUIREMENTS]
FROM PROJDATA, [Exchanger Sub] AS COMMODITIES INNER JOIN (((SOLODATA INNER
JOIN (COMMONDATA INNER JOIN COMPONENTS ON COMMONDATA.COMMONDATALINK =
COMPONENTS.COMMONDATALINK) ON SOLODATA.SOLODATALINK =
COMPONENTS.SOLODATALINK) INNER JOIN DRAWINGLIST ON COMPONENTS.DRAWINGID =
DRAWINGLIST.DRAWINGID) INNER JOIN SOLODATA2_V ON SOLODATA.SOLODATALINK =
SOLODATA2_V.SOLODATALINK) ON COMMODITIES.SOLODATALINK =
SOLODATA.SOLODATALINK
WHERE (((Asc(CStr(Right([SOLODATA].[SECONDTAGNUMBER],1))))=65 Or
(Asc(CStr(Right([SOLODATA].[SECONDTAGNUMBER],1))))>=48 And
(Asc(CStr(Right([SOLODATA].[SECONDTAGNUMBER],1))))<=57) AND
((Left([SOLODATA].[SECONDTAGNUMBER],InStr([SOLODATA].[SECONDTAGNUMBER],"-")+3))=GetEnersulTag())
AND ((Left(Right([DRAWINGLIST].[NAME],4),3))="G20" Or
(Left(Right([DRAWINGLIST].[NAME],4),3))="G40") AND
((DRAWINGLIST.APPLICATION)="PID" Or (DRAWINGLIST.APPLICATION)="EQUIP") AND
((COMPONENTS.PARENTID)<1) AND ((DRAWINGLIST.DRAWINGTYPE)=0));

Oh no! I've even exceeded the post limit! Looks like I'll have to post the
rest after.

AlwaysFroosh! · Jan 25, 2008

Fair enough, I realized that was a little ridiculous when it wouldn't even
fit in a single post, and I really didn't expect anyone to go through it
character by character! I just often see question posters asked to post more
info about their query when the gurus are trying to help, so I was just
trying be as detailed as possible.

The smaller queries work fine, it actually only starts to be "too complex"
when I run the fourth query that compares the current values to previous
values that have been backed up in a "revisions" table if you will. The idea
is that the datasheet compares itself to the previous version, and
automatically flags itself if a piece of data has changed somewhere.

Not only that, but the query was working fine up until this morning when I
needed to added more conditions to the first query, the one that gets all the
commodity information. This required more 'if' statements, and only then did
the last query become too complex.

Do you know if there is a maximum number of if statements a query can have?
The deepest they are nested is two deep I think, certainly not significantly
more, there's just a lot of different ones.

Also another thing I was curious of, when users on this forum talk about a
maximum query size, does that include the size of the sub queries? For
example, I don't think that the commodities query became too long this
morning because it is still shorter than the equipment query. But maybe the
whole thing chained together is too long? Or does it not work that way?

Thanks,
Graham

Ken Snell (MVP) said:
Your query's SQL statement is very long ... I, and I'm sure others, are not
going to go through the entire query (took two posts to show it?) and try to
understand the entire thing. Have you tried breaking the query into smaller
queries and verifying that each of the smaller queries works correctly?

Common problems that cause "too complex" error are data errors (trying to
divide by zero, exceeding the size of data that a field can hold [e.g.,
trying to put a number larger than about 33000 into an integer field],
having a date value that is not recognizable as a date, etc.).

--

Ken Snell
<MS ACCESS MVP>

AlwaysFroosh! said:

I wonder if anyone of you is still watching this post, I guess this thread
is
a couple years old now!

I too am having a problem with my query being too complex, maybe someone
could help me simplify it?

We use a database to house technical information on equipment. In this
case,
the piece of equipment is a heat exchanger. To generate a datasheet for
this
exchanger, I create a query that produces one record, the record of all
the
information for that particular heat exchanger.

I use a series of what I think of as "piggy backing" queries. First I
create
a recordset of the commodity properties that are coming into and going out
of
the exchanger. Then I build another query using the commodities query and
a
bunch of tables with data that is specific to that exchanger. Then I have
another query that numbers all of the fields so that I can backup all
queries
for different equipment into a table with just numbers as the column
names.
Finally my last query looks at the numbered query, and the backup table
and
compare values so that the revisions can be flagged if there's a
difference.

Here are the queries:

Commodity Query:
SELECT SOLODATA.SOLODATALINK, PRIM_IN_COMMONDATA.COMMODITY AS [PRIMIN
COMMODITY],
IIf([PRIM_IN_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([PRIM_IN_COMMONDATA].[MASS_FLOW_1],[PRIM_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([PRIM_IN_COMMONDATA].[VOL_FLOW_1],[PRIM_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [PRIMIN FLOW 1],
IIf([PRIM_IN_COMMONDATA].[VOL_FLOW_3]=0,NumberWithUnits([PRIM_IN_COMMONDATA].[MASS_FLOW_3],[PRIM_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([PRIM_IN_COMMONDATA].[VOL_FLOW_3],[PRIM_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [PRIMIN FLOW 2],
NumberWithUnits([PRIM_IN_COMMONDATA].[OPERTEMPERATURE],[PRIM_IN_COMMONDATA].[TEMPERATUREUNITS])
AS [PRIMIN OPERATING TEMPERATURE 1],
NumberWithUnits([PRIM_IN_COMMONDATA].[TEMPERATURE_3],[PRIM_IN_COMMONDATA].[TEMPERATUREUNITS])
AS [PRIMIN OPERATING TEMPERATURE 2],
NumberWithUnits([PRIM_IN_COMMONDATA].[OPERPRESSURE],[PRIM_IN_COMMONDATA].[PRESSUREUNITS])
AS [PRIM INLET PRESSURE 1],
NumberWithUnits([PRIM_IN_COMMONDATA].[PRESSURE_3],[PRIM_IN_COMMONDATA].[PRESSUREUNITS])
AS [PRIM INLET PRESSURE 2],
NumberWithUnits([PRIM_IN_COMMONDATA].[DENSITY_1],[PRIM_IN_COMMONDATA].[DENSITY_UNITS])
AS [PRIMIN DENSITY 1],
NumberWithUnits([PRIM_IN_COMMONDATA].[DENSITY_2],[PRIM_IN_COMMONDATA].[DENSITY_UNITS])
AS [PRIMIN DENSITY 2],
NumberWithUnits([PRIM_IN_COMMONDATA].[VISCOSITY_1],[PRIM_IN_COMMONDATA].[VISCOSITY_UNITS])
AS [PRIMIN VISCOSITY 1],
NumberWithUnits([PRIM_IN_COMMONDATA].[VISCOSITY_2],[PRIM_IN_COMMONDATA].[VISCOSITY_UNITS])
AS [PRIMIN VISCOSITY 2], PRIM_IN_COMMONDATA.CEXTA3 AS [PRIMIN SPEC HEAT
1],
PRIM_IN_COMMONDATA.CEXTA6 AS [PRIMIN SPEC HEAT 2],
PRIM_IN_COMMONDATA.CEXTA5
AS [PRIMIN THERMAL COND 1], PRIM_IN_COMMONDATA.CEXTA8 AS [PRIMIN THERMAL
COND
2], PRIM_OUT_COMMONDATA.COMMODITY AS [PRIMOUT COMMODITY],
IIf([PRIM_OUT_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([PRIM_OUT_COMMONDATA].[MASS_FLOW_1],[PRIM_OUT_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([PRIM_OUT_COMMONDATA].[VOL_FLOW_1],[PRIM_OUT_COMMONDATA].[VOL_FLOW_UNITS]))
AS [PRIMOUT FLOW 1],
IIf([PRIM_OUT_COMMONDATA].[VOL_FLOW_3]=0,NumberWithUnits([PRIM_OUT_COMMONDATA].[MASS_FLOW_3],[PRIM_OUT_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([PRIM_OUT_COMMONDATA].[VOL_FLOW_3],[PRIM_OUT_COMMONDATA].[VOL_FLOW_UNITS]))
AS [PRIMOUT FLOW 2],
NumberWithUnits([PRIM_OUT_COMMONDATA].[OPERTEMPERATURE],[PRIM_OUT_COMMONDATA].[TEMPERATUREUNITS])
AS [PRIMOUT OPERATING TEMPERATURE 1],
NumberWithUnits([PRIM_OUT_COMMONDATA].[TEMPERATURE_3],[PRIM_OUT_COMMONDATA].[TEMPERATUREUNITS])
AS [PRIMOUT OPERATING TEMPERATURE 2],
NumberWithUnits(CDbl(Nz([PRIM_OUT_COMMONDATA].[OPERPRESSURE],0))-CDbl(Nz([PRIM_IN_COMMONDATA].[OPERPRESSURE],0)),[PRIM_OUT_COMMONDATA].[PRESSUREUNITS])
AS [PRIM DIFFERENTIAL PRESSURE 1],
NumberWithUnits(CDbl(Nz([PRIM_OUT_COMMONDATA].[PRESSURE_3],0))-CDbl(Nz([PRIM_IN_COMMONDATA].[PRESSURE_3],0)),[PRIM_OUT_COMMONDATA].[PRESSUREUNITS])
AS [PRIM DIFFERENTIAL PRESSURE 2],
NumberWithUnits([PRIM_OUT_COMMONDATA].[DENSITY_1],[PRIM_OUT_COMMONDATA].[DENSITY_UNITS])
AS [PRIMOUT DENSITY 1],
NumberWithUnits([PRIM_OUT_COMMONDATA].[DENSITY_2],[PRIM_OUT_COMMONDATA].[DENSITY_UNITS])
AS [PRIMOUT DENSITY 2],
NumberWithUnits([PRIM_OUT_COMMONDATA].[VISCOSITY_1],[PRIM_OUT_COMMONDATA].[VISCOSITY_UNITS])
AS [PRIMOUT VISCOSITY 1],
NumberWithUnits([PRIM_OUT_COMMONDATA].[VISCOSITY_2],[PRIM_OUT_COMMONDATA].[VISCOSITY_UNITS])
AS [PRIMOUT VISCOSITY 2], PRIM_OUT_COMMONDATA.CEXTA3 AS [PRIMOUT SPEC HEAT
1], PRIM_OUT_COMMONDATA.CEXTA6 AS [PRIMOUT SPEC HEAT 2],
PRIM_OUT_COMMONDATA.CEXTA5 AS [PRIMOUT THERMAL COND 1],
PRIM_OUT_COMMONDATA.CEXTA8 AS [PRIMOUT THERMAL COND 2],
SEC_IN_COMMONDATA.COMMODITY AS [SECIN COMMODITY],
IIf([SEC_IN_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([SEC_IN_COMMONDATA].[MASS_FLOW_1],[SEC_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([SEC_IN_COMMONDATA].[VOL_FLOW_1],[SEC_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [SECIN FLOW 1],
IIf([SEC_IN_COMMONDATA].[VOL_FLOW_3]=0,NumberWithUnits([SEC_IN_COMMONDATA].[MASS_FLOW_3],[SEC_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([SEC_IN_COMMONDATA].[VOL_FLOW_3],[SEC_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [SECIN FLOW 2],
NumberWithUnits([SEC_IN_COMMONDATA].[OPERTEMPERATURE],[SEC_IN_COMMONDATA].[TEMPERATUREUNITS])
AS [SECIN OPERATING TEMPERATURE 1],
NumberWithUnits([SEC_IN_COMMONDATA].[TEMPERATURE_3],[SEC_IN_COMMONDATA].[TEMPERATUREUNITS])
AS [SECIN OPERATING TEMPERATURE 2],
NumberWithUnits([SEC_IN_COMMONDATA].[OPERPRESSURE],[SEC_IN_COMMONDATA].[PRESSUREUNITS])
AS [SEC INLET PRESSURE 1],
NumberWithUnits([SEC_IN_COMMONDATA].[PRESSURE_3],[SEC_IN_COMMONDATA].[PRESSUREUNITS])
AS [SEC INLET PRESSURE 2],
NumberWithUnits([SEC_IN_COMMONDATA].[DENSITY_1],[SEC_IN_COMMONDATA].[DENSITY_UNITS])
AS [SECIN DENSITY 1],
NumberWithUnits([SEC_IN_COMMONDATA].[DENSITY_2],[SEC_IN_COMMONDATA].[DENSITY_UNITS])
AS [SECIN DENSITY 2],
NumberWithUnits([SEC_IN_COMMONDATA].[VISCOSITY_1],[SEC_IN_COMMONDATA].[VISCOSITY_UNITS])
AS [SECIN VISCOSITY 1],
NumberWithUnits([SEC_IN_COMMONDATA].[VISCOSITY_2],[SEC_IN_COMMONDATA].[VISCOSITY_UNITS])
AS [SECIN VISCOSITY 2], SEC_IN_COMMONDATA.CEXTA3 AS [SECIN SPEC HEAT 1],
SEC_IN_COMMONDATA.CEXTA6 AS [SECIN SPEC HEAT 2], SEC_IN_COMMONDATA.CEXTA5
AS
[SECIN THERMAL COND 1], SEC_IN_COMMONDATA.CEXTA8 AS [SECIN THERMAL COND
2],
SEC_OUT_COMMONDATA.COMMODITY AS [SECOUT COMMODITY],
IIf([SEC_OUT_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([SEC_OUT_COMMONDATA].[MASS_FLOW_1],[SEC_OUT_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([SEC_OUT_COMMONDATA].[VOL_FLOW_1],[SEC_OUT_COMMONDATA].[VOL_FLOW_UNITS]))
AS [SECOUT FLOW 1],
IIf([SEC_OUT_COMMONDATA].[VOL_FLOW_3]=0,NumberWithUnits([SEC_OUT_COMMONDATA].[MASS_FLOW_3],[SEC_OUT_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([SEC_OUT_COMMONDATA].[VOL_FLOW_3],[SEC_OUT_COMMONDATA].[VOL_FLOW_UNITS]))
AS [SECOUT FLOW 2],
NumberWithUnits([SEC_OUT_COMMONDATA].[OPERTEMPERATURE],[SEC_OUT_COMMONDATA].[TEMPERATUREUNITS])
AS [SECOUT OPERATING TEMPERATURE 1],
NumberWithUnits([SEC_OUT_COMMONDATA].[TEMPERATURE_3],[SEC_OUT_COMMONDATA].[TEMPERATUREUNITS])
AS [SECOUT OPERATING TEMPERATURE 2],
NumberWithUnits(CDbl(Nz([SEC_OUT_COMMONDATA].[OPERPRESSURE],0))-CDbl(Nz([SEC_IN_COMMONDATA].[OPERPRESSURE],0)),[SEC_OUT_COMMONDATA].[PRESSUREUNITS])
AS [SEC DIFFERENTIAL PRESSURE 1],
NumberWithUnits(CDbl(Nz([SEC_OUT_COMMONDATA].[PRESSURE_3],0))-CDbl(Nz([SEC_IN_COMMONDATA].[PRESSURE_3],0)),[SEC_OUT_COMMONDATA].[PRESSUREUNITS])
AS [SEC DIFFERENTIAL PRESSURE 2],
NumberWithUnits([SEC_OUT_COMMONDATA].[DENSITY_1],[SEC_OUT_COMMONDATA].[DENSITY_UNITS])
AS [SECOUT DENSITY 1],
NumberWithUnits([SEC_OUT_COMMONDATA].[DENSITY_2],[SEC_OUT_COMMONDATA].[DENSITY_UNITS])
AS [SECOUT DENSITY 2],
NumberWithUnits([SEC_OUT_COMMONDATA].[VISCOSITY_1],[SEC_OUT_COMMONDATA].[VISCOSITY_UNITS])
AS [SECOUT VISCOSITY 1],
NumberWithUnits([SEC_OUT_COMMONDATA].[VISCOSITY_2],[SEC_OUT_COMMONDATA].[VISCOSITY_UNITS])
AS [SECOUT VISCOSITY 2], SEC_OUT_COMMONDATA.CEXTA3 AS [SECOUT SPEC HEAT
1],
SEC_OUT_COMMONDATA.CEXTA6 AS [SECOUT SPEC HEAT 2],
SEC_OUT_COMMONDATA.CEXTA5
AS [SECOUT THERMAL COND 1], SEC_OUT_COMMONDATA.CEXTA8 AS [SECOUT THERMAL
COND
2], JACK_IN_COMMONDATA.COMMODITY AS [JACKIN COMMODITY],
IIf([JACK_IN_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([JACK_IN_COMMONDATA].[MASS_FLOW_1],[JACK_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([JACK_IN_COMMONDATA].[VOL_FLOW_1],[JACK_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [JACKIN FLOW 1],
IIf([JACK_IN_COMMONDATA].[VOL_FLOW_3]=0,NumberWithUnits([JACK_IN_COMMONDATA].[MASS_FLOW_3],[JACK_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([JACK_IN_COMMONDATA].[VOL_FLOW_3],[JACK_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [JACKIN FLOW 2],
NumberWithUnits([JACK_IN_COMMONDATA].[OPERTEMPERATURE],[JACK_IN_COMMONDATA].[TEMPERATUREUNITS])
AS [JACKIN OPERATING TEMPERATURE 1],
NumberWithUnits([JACK_IN_COMMONDATA].[TEMPERATURE_3],[JACK_IN_COMMONDATA].[TEMPERATUREUNITS])
AS [JACKIN OPERATING TEMPERATURE 2],
NumberWithUnits([JACK_IN_COMMONDATA].[OPERPRESSURE],[JACK_IN_COMMONDATA].[PRESSUREUNITS])
AS [JACK INLET PRESSURE 1],
NumberWithUnits([JACK_IN_COMMONDATA].[PRESSURE_3],[JACK_IN_COMMONDATA].[PRESSUREUNITS])
AS [JACK INLET PRESSURE 2],
NumberWithUnits([JACK_IN_COMMONDATA].[DENSITY_1],[JACK_IN_COMMONDATA].[DENSITY_UNITS])
AS [JACKIN DENSITY 1],
NumberWithUnits([JACK_IN_COMMONDATA].[DENSITY_2],[JACK_IN_COMMONDATA].[DENSITY_UNITS])
AS [JACKIN DENSITY 2],
NumberWithUnits([JACK_IN_COMMONDATA].[VISCOSITY_1],[JACK_IN_COMMONDATA].[VISCOSITY_UNITS])
AS [JACKIN VISCOSITY 1],
NumberWithUnits([JACK_IN_COMMONDATA].[VISCOSITY_2],[JACK_IN_COMMONDATA].[VISCOSITY_UNITS])
AS [JACKIN VISCOSITY 2], JACK_IN_COMMONDATA.CEXTA3 AS [JACKIN SPEC HEAT
1],
JACK_IN_COMMONDATA.CEXTA6 AS [JACKIN SPEC HEAT 2],
JACK_IN_COMMONDATA.CEXTA5
AS [JACKIN THERMAL COND 1], JACK_IN_COMMONDATA.CEXTA8 AS [JACKIN THERMAL
COND
2], JACK_OUT_COMMONDATA.COMMODITY AS [JACKOUT COMMODITY],
IIf([JACK_OUT_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([JACK_OUT_COMMONDATA].[MASS_FLOW_1],[JACK_OUT_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([JACK_OUT_COMMONDATA].[VOL_FLOW_1],[JACK_OUT_COMMONDATA].[VOL_FLOW_UNITS]))
AS [JACKOUT FLOW 1],
IIf([JACK_OUT_COMMONDATA].[VOL_FLOW_3]=0,NumberWithUnits([JACK_OUT_COMMONDATA].[MASS_FLOW_3],[JACK_OUT_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([JACK_OUT_COMMONDATA].[VOL_FLOW_3],[JACK_OUT_COMMONDATA].[VOL_FLOW_UNITS]))
AS [JACKOUT FLOW 2],
NumberWithUnits([JACK_OUT_COMMONDATA].[OPERTEMPERATURE],[JACK_OUT_COMMONDATA].[TEMPERATUREUNITS])
AS [JACKOUT OPERATING TEMPERATURE 1],
NumberWithUnits([JACK_OUT_COMMONDATA].[TEMPERATURE_3],[JACK_OUT_COMMONDATA].[TEMPERATUREUNITS])
AS [JACKOUT OPERATING TEMPERATURE 2],
NumberWithUnits(CDbl(Nz([JACK_OUT_COMMONDATA].[OPERPRESSURE],0))-CDbl(Nz([JACK_IN_COMMONDATA].[OPERPRESSURE],0)),[JACK_OUT_COMMONDATA].[PRESSUREUNITS])
AS [JACK DIFFERENTIAL PRESSURE 1],
NumberWithUnits(CDbl(Nz([JACK_OUT_COMMONDATA].[PRESSURE_3],0))-CDbl(Nz([JACK_IN_COMMONDATA].[PRESSURE_3],0)),[JACK_OUT_COMMONDATA].[PRESSUREUNITS])
AS [JACK DIFFERENTIAL PRESSURE 2],
NumberWithUnits([JACK_OUT_COMMONDATA].[DENSITY_1],[JACK_OUT_COMMONDATA].[DENSITY_UNITS])
AS [JACKOUT DENSITY 1],
NumberWithUnits([JACK_OUT_COMMONDATA].[DENSITY_2],[JACK_OUT_COMMONDATA].[DENSITY_UNITS])
AS [JACKOUT DENSITY 2],
NumberWithUnits([JACK_OUT_COMMONDATA].[VISCOSITY_1],[JACK_OUT_COMMONDATA].[VISCOSITY_UNITS])
AS [JACKOUT VISCOSITY 1],
NumberWithUnits([JACK_OUT_COMMONDATA].[VISCOSITY_2],[JACK_OUT_COMMONDATA].[VISCOSITY_UNITS])
AS [JACKOUT VISCOSITY 2], JACK_OUT_COMMONDATA.CEXTA3 AS [JACKOUT SPEC HEAT
1], JACK_OUT_COMMONDATA.CEXTA6 AS [JACKOUT SPEC HEAT 2],
JACK_OUT_COMMONDATA.CEXTA5 AS [JACKOUT THERMAL COND 1],
JACK_OUT_COMMONDATA.CEXTA8 AS [JACKOUT THERMAL COND 2]
FROM (((((SOLODATA LEFT JOIN COMMONDATA AS PRIM_IN_COMMONDATA ON
SOLODATA.EXTN1 = PRIM_IN_COMMONDATA.COMMONDATALINK) LEFT JOIN COMMONDATA
AS
PRIM_OUT_COMMONDATA ON SOLODATA.EXTN2 =
PRIM_OUT_COMMONDATA.COMMONDATALINK)
LEFT JOIN COMMONDATA AS SEC_IN_COMMONDATA ON SOLODATA.EXTN3 =
SEC_IN_COMMONDATA.COMMONDATALINK) LEFT JOIN COMMONDATA AS
SEC_OUT_COMMONDATA
ON SOLODATA.EXTN4 = SEC_OUT_COMMONDATA.COMMONDATALINK) LEFT JOIN
COMMONDATA
AS JACK_IN_COMMONDATA ON SOLODATA.EXTN5 =
JACK_IN_COMMONDATA.COMMONDATALINK)
LEFT JOIN COMMONDATA AS JACK_OUT_COMMONDATA ON SOLODATA.EXTN6 =
JACK_OUT_COMMONDATA.COMMONDATALINK;

Equipment Query:
SELECT TitleCase([SOLODATA].[COMPONENTDESCRIPTION]) AS [COMPONENT
DESCRIPTION], ("EDS-" & GetProjectNumber() & "-" & [SOLODATA].[TAG2] &
Left([SOLODATA].[TAG3],GetClientTagLength())) AS EDS, "PROJECT " &
GetProjectNumber() AS PROJECT, SOLODATA2_V.EXTA140 AS [CLIENT DS], "GPP-"
&
GetProjectNumber() & "-" & [SOLODATA].[EXTA1] AS GPP, SOLODATA.EXTA2 AS
PO,
TitleCase([PROJDATA].[PROJECT_NAME]) AS PROJECT_NAME,
TitleCase([PROJDATA].[CLIENT]) AS CLIENT, PROJDATA.CLIENT_PROJECT_NUM,
PROJDATA.CLIENT_PO, PROJDATA.SITE, SOLODATA.MANUFACTURER, SOLODATA.EXTA11
AS
[PRIMARY FLUID ALLOCATION], COMMODITIES.[PRIMIN COMMODITY] AS [PRIMIN
COMMODITY], IIf([SOLODATA].[EXTN12],[PRIMIN FLOW 2],[PRIMIN FLOW 1]) AS
[PRIMIN FLOW], IIf([SOLODATA].[EXTN12],[PRIMIN OPERATING TEMPERATURE
2],[PRIMIN OPERATING TEMPERATURE 1]) AS [PRIMIN OPERATING TEMPERATURE],
IIf([SOLODATA].[EXTN12],[PRIM INLET PRESSURE 2],[PRIM INLET PRESSURE 1])
AS
[PRIM INLET PRESSURE], IIf([SOLODATA].[EXTN12],[PRIMIN DENSITY 2],[PRIMIN
DENSITY 1]) AS [PRIMIN DENSITY], IIf([SOLODATA].[EXTN12],[PRIMIN VISCOSITY
2],[PRIMIN VISCOSITY 1]) AS [PRIMIN VISCOSITY],
IIf([SOLODATA].[EXTN12],[PRIMIN SPEC HEAT 2],[PRIMIN SPEC HEAT 1]) AS
[PRIMIN
SPEC HEAT], IIf([SOLODATA].[EXTN12],[PRIMIN THERMAL COND 2],[PRIMIN
THERMAL
COND 1]) AS [PRIMIN THERMAL COND], COMMODITIES.[PRIMOUT COMMODITY] AS
[PRIMOUT COMMODITY], IIf([SOLODATA].[EXTN12],[PRIMOUT FLOW 2],[PRIMOUT
FLOW
1]) AS [PRIMOUT FLOW], IIf([SOLODATA].[EXTN12],[PRIMOUT OPERATING
TEMPERATURE
2],[PRIMOUT OPERATING TEMPERATURE 1]) AS [PRIMOUT OPERATING TEMPERATURE],
IIf([SOLODATA].[EXTN12],[PRIM DIFFERENTIAL PRESSURE 2],[PRIM DIFFERENTIAL
PRESSURE 1]) AS [PRIM DIFFERENTIAL PRESSURE],
IIf([SOLODATA].[EXTN12],[PRIMOUT DENSITY 2],[PRIMOUT DENSITY 1]) AS
[PRIMOUT
DENSITY], IIf([SOLODATA].[EXTN12],[PRIMOUT VISCOSITY 2],[PRIMOUT VISCOSITY
1]) AS [PRIMOUT VISCOSITY], IIf([SOLODATA].[EXTN12],[PRIMOUT SPEC HEAT
2],[PRIMOUT SPEC HEAT 1]) AS [PRIMOUT SPEC HEAT],
IIf([SOLODATA].[EXTN12],[PRIMOUT THERMAL COND 2],[PRIMOUT THERMAL COND 1])
AS
[PRIMOUT THERMAL COND], SOLODATA.EXTA12 AS [SECONDARY FLUID ALLOCATION],
COMMODITIES.[SECIN COMMODITY] AS [SECIN COMMODITY],
IIf([SOLODATA].[EXTN12],[SECIN FLOW 2],[SECIN FLOW 1]) AS [SECIN FLOW],
IIf([SOLODATA].[EXTN12],[SECIN OPERATING TEMPERATURE 2],[SECIN OPERATING
TEMPERATURE 1]) AS [SECIN OPERATING TEMPERATURE],
IIf([SOLODATA].[EXTN12],[SEC INLET PRESSURE 2],[SEC INLET PRESSURE 1]) AS
[SEC INLET PRESSURE], IIf([SOLODATA].[EXTN12],[SECIN DENSITY 2],[SECIN
DENSITY 1]) AS [SECIN DENSITY], IIf([SOLODATA].[EXTN12],[SECIN VISCOSITY
2],[SECIN VISCOSITY 1]) AS [SECIN VISCOSITY],
IIf([SOLODATA].[EXTN12],[SECIN
SPEC HEAT 2],[SECIN SPEC HEAT 1]) AS [SECIN SPEC HEAT],
IIf([SOLODATA].[EXTN12],[SECIN THERMAL COND 2],[SECIN THERMAL COND 1]) AS
[SECIN THERMAL COND], COMMODITIES.[SECOUT COMMODITY] AS [SECOUT
COMMODITY],
IIf([SOLODATA].[EXTN12],[SECOUT FLOW 2],[SECOUT FLOW 1]) AS [SECOUT FLOW],
IIf([SOLODATA].[EXTN12],[SECOUT OPERATING TEMPERATURE 2],[SECOUT OPERATING
TEMPERATURE 1]) AS [SECOUT OPERATING TEMPERATURE],
IIf([SOLODATA].[EXTN12],[SEC DIFFERENTIAL PRESSURE 2],[SEC DIFFERENTIAL
PRESSURE 1]) AS [SEC DIFFERENTIAL PRESSURE],
IIf([SOLODATA].[EXTN12],[SECOUT
DENSITY 2],[SECOUT DENSITY 1]) AS [SECOUT DENSITY],
IIf([SOLODATA].[EXTN12],[SECOUT VISCOSITY 2],[SECOUT VISCOSITY 1]) AS
[SECOUT
VISCOSITY], IIf([SOLODATA].[EXTN12],[SECOUT SPEC HEAT 2],[SECOUT SPEC HEAT
1]) AS [SECOUT SPEC HEAT], IIf([SOLODATA].[EXTN12],[SECOUT THERMAL COND
2],[SECOUT THERMAL COND 1]) AS [SECOUT THERMAL COND],
NumberWithUnits([COMMONDATA].[PRESSURE_3],[COMMONDATA].[PRESSUREUNITS]) AS
[PRIM DESIGN DP],
NumberWithUnits([COMMONDATA].[PRESSURE_4],[COMMONDATA].[PRESSUREUNITS]) AS
[SEC DESIGN DP],
NumberWithUnits([SOLODATA].[DISTANCE_2],[SOLODATA].[DISTANCE_UNITS] &
"/s")
AS [PRIM VELOCITY],
NumberWithUnits([SOLODATA].[DISTANCE_3],[SOLODATA].[DISTANCE_UNITS] &
"/s")
AS [SEC VELOCITY], SOLODATA.EXTA16 AS [PRIM FOULING RESISTANCE],
SOLODATA.EXTA17 AS [SEC FOULING RESISTANCE],
NumberWithUnits([SOLODATA].[POWER_1],[SOLODATA].[POWER_UNITS]) AS [HEAT
EXCHANGED], NumberWithUnits([SOLODATA].[EXTA13],[SOLODATA].[EXTA24]) AS
[SERVICE TRANSFER RATE],
NumberWithUnits([SOLODATA].[EXTA14],[SOLODATA].[EXTA24]) AS [DIRTY

Click to expand...

John W. Vinson · Jan 25, 2008

Do you know if there is a maximum number of if statements a query can have?

My understanding is that the Query Too Complex error arises when the
*compiled* query exceeds a maximum size - 64KBytes I recall. This would
include subqueries. Since Microsoft has not chosen to publish details about
the compilation method or the structure of a compiled query, it's hard to
predict when a query will hit this limit.

But your query is a humungous monster, and it's not surprising to me that it
is popping a QTC.

All I can suggest is aliasing all the table and fieldnames that you can to
one- and two-letter shortcuts so that you can have

IIF([K].[V1] = 0,NumberWithUnits([K].[M1],[K].[MFU]),NumberWithUnits([K].[F1],
[K.VFU])) AS PF1,

instead of

IIf([PRIM_IN_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([PRIM_IN_COMMONDATA].[MASS_FLOW_1],[PRIM_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([PRIM_IN_COMMONDATA].[VOL_FLOW_1],[PRIM_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [PRIMIN FLOW 1],

It makes your query harder to document but may be the only way around your
problem (short of redesigning the datastructure which I suspect is not a
pleasant option).

John W. Vinson [MVP]

AlwaysFroosh! · Jan 25, 2008

Hey, now there's a great idea! When I think of the size of the query, I
always think of the number of fields I'm referencing and the operations being
performed, but I guess you're right, it would be the actual length of the
query. I'll have to try it out, I'll let you guys know if I have an luck. But
right now I want to go home, for it's the weekend, and it's Millertime!

John W. Vinson said:
Do you know if there is a maximum number of if statements a query can have?

Click to expand...

My understanding is that the Query Too Complex error arises when the
*compiled* query exceeds a maximum size - 64KBytes I recall. This would
include subqueries. Since Microsoft has not chosen to publish details about
the compilation method or the structure of a compiled query, it's hard to
predict when a query will hit this limit.

But your query is a humungous monster, and it's not surprising to me that it
is popping a QTC.

All I can suggest is aliasing all the table and fieldnames that you can to
one- and two-letter shortcuts so that you can have

IIF([K].[V1] = 0,NumberWithUnits([K].[M1],[K].[MFU]),NumberWithUnits([K].[F1],
[K.VFU])) AS PF1,

instead of

IIf([PRIM_IN_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([PRIM_IN_COMMONDATA].[MASS_FLOW_1],[PRIM_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([PRIM_IN_COMMONDATA].[VOL_FLOW_1],[PRIM_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [PRIMIN FLOW 1],

It makes your query harder to document but may be the only way around your
problem (short of redesigning the datastructure which I suspect is not a
pleasant option).

John W. Vinson [MVP]

John W. Vinson · Jan 26, 2008

On Fri, 25 Jan 2008 15:16:04 -0800, AlwaysFroosh!

It's a stopgap to some extent, since the query is still huge, but it can stave
off the dreaded day of restructureing for a while! Hope you're having a good
weekend. Maybe you should go get a *real* beer though...

John W. Vinson said:
Hey, now there's a great idea! When I think of the size of the query, I
always think of the number of fields I'm referencing and the operations being
performed, but I guess you're right, it would be the actual length of the
query. I'll have to try it out, I'll let you guys know if I have an luck. But
right now I want to go home, for it's the weekend, and it's Millertime!

John W. Vinson said:

Do you know if there is a maximum number of if statements a query can have?

Click to expand...

My understanding is that the Query Too Complex error arises when the
*compiled* query exceeds a maximum size - 64KBytes I recall. This would
include subqueries. Since Microsoft has not chosen to publish details about
the compilation method or the structure of a compiled query, it's hard to
predict when a query will hit this limit.

But your query is a humungous monster, and it's not surprising to me that it
is popping a QTC.

All I can suggest is aliasing all the table and fieldnames that you can to
one- and two-letter shortcuts so that you can have

IIF([K].[V1] = 0,NumberWithUnits([K].[M1],[K].[MFU]),NumberWithUnits([K].[F1],
[K.VFU])) AS PF1,

instead of

IIf([PRIM_IN_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([PRIM_IN_COMMONDATA].[MASS_FLOW_1],[PRIM_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([PRIM_IN_COMMONDATA].[VOL_FLOW_1],[PRIM_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [PRIMIN FLOW 1],

It makes your query harder to document but may be the only way around your
problem (short of redesigning the datastructure which I suspect is not a
pleasant option).

John W. Vinson [MVP]

Click to expand...

John W. Vinson [MVP]

Knox · Jan 26, 2008

Another approach is use a temporary table... Say you have 10 chained
queries. Take the result of queries 1 through 5 and insert into a table.
Then have queries 6 through 10 work from that table. That cuts the size of
the complied queries roughly in half.

I've done things like this and used a macro to combine several steps into
one.

Good luck!

Knox

John W. Vinson said:
On Fri, 25 Jan 2008 15:16:04 -0800, AlwaysFroosh!

It's a stopgap to some extent, since the query is still huge, but it can
stave
off the dreaded day of restructureing for a while! Hope you're having a
good
weekend. Maybe you should go get a *real* beer though...

Hey, now there's a great idea! When I think of the size of the query, I
always think of the number of fields I'm referencing and the operations
being
performed, but I guess you're right, it would be the actual length of the
query. I'll have to try it out, I'll let you guys know if I have an luck.
But
right now I want to go home, for it's the weekend, and it's Millertime!

John W. Vinson said:

On Fri, 25 Jan 2008 12:48:01 -0800, AlwaysFroosh!

Do you know if there is a maximum number of if statements a query can
have?

My understanding is that the Query Too Complex error arises when the
*compiled* query exceeds a maximum size - 64KBytes I recall. This would
include subqueries. Since Microsoft has not chosen to publish details
about
the compilation method or the structure of a compiled query, it's hard
to
predict when a query will hit this limit.

But your query is a humungous monster, and it's not surprising to me
that it
is popping a QTC.

All I can suggest is aliasing all the table and fieldnames that you can
to
one- and two-letter shortcuts so that you can have

IIF([K].[V1] =
0,NumberWithUnits([K].[M1],[K].[MFU]),NumberWithUnits([K].[F1],
[K.VFU])) AS PF1,

instead of

IIf([PRIM_IN_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([PRIM_IN_COMMONDATA].[MASS_FLOW_1],[PRIM_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([PRIM_IN_COMMONDATA].[VOL_FLOW_1],[PRIM_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [PRIMIN FLOW 1],

It makes your query harder to document but may be the only way around
your
problem (short of redesigning the datastructure which I suspect is not a
pleasant option).

John W. Vinson [MVP]

Click to expand...

Click to expand...

John W. Vinson [MVP]

AlwaysFroosh! · Jan 28, 2008

Hmm, that's an interesting approach. So you use VBA to initiate the queries
when the report loads, right? Is that what you mean? I don't know another way
you would accomplish it, but I think I could get it to work using VBA.

Graham

Knox said:
Another approach is use a temporary table... Say you have 10 chained
queries. Take the result of queries 1 through 5 and insert into a table.
Then have queries 6 through 10 work from that table. That cuts the size of
the complied queries roughly in half.

I've done things like this and used a macro to combine several steps into
one.

Good luck!

Knox

John W. Vinson said:

On Fri, 25 Jan 2008 15:16:04 -0800, AlwaysFroosh!

It's a stopgap to some extent, since the query is still huge, but it can
stave
off the dreaded day of restructureing for a while! Hope you're having a
good
weekend. Maybe you should go get a *real* beer though...

Hey, now there's a great idea! When I think of the size of the query, I
always think of the number of fields I'm referencing and the operations
being
performed, but I guess you're right, it would be the actual length of the
query. I'll have to try it out, I'll let you guys know if I have an luck.
But
right now I want to go home, for it's the weekend, and it's Millertime!

:

On Fri, 25 Jan 2008 12:48:01 -0800, AlwaysFroosh!

Do you know if there is a maximum number of if statements a query can
have?

My understanding is that the Query Too Complex error arises when the
*compiled* query exceeds a maximum size - 64KBytes I recall. This would
include subqueries. Since Microsoft has not chosen to publish details
about
the compilation method or the structure of a compiled query, it's hard
to
predict when a query will hit this limit.

But your query is a humungous monster, and it's not surprising to me
that it
is popping a QTC.

All I can suggest is aliasing all the table and fieldnames that you can
to
one- and two-letter shortcuts so that you can have

IIF([K].[V1] =
0,NumberWithUnits([K].[M1],[K].[MFU]),NumberWithUnits([K].[F1],
[K.VFU])) AS PF1,

instead of

IIf([PRIM_IN_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([PRIM_IN_COMMONDATA].[MASS_FLOW_1],[PRIM_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([PRIM_IN_COMMONDATA].[VOL_FLOW_1],[PRIM_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [PRIMIN FLOW 1],

It makes your query harder to document but may be the only way around
your
problem (short of redesigning the datastructure which I suspect is not a
pleasant option).

John W. Vinson [MVP]

Click to expand...

John W. Vinson [MVP]

Click to expand...

Knox · Jan 28, 2008

I don't generally use VBA for this approach. I use the Access Macros where
it's easy to enter the queries since it lets you have one query per line. I
typically have the first line in the macro turn on the hourglass, the second
line turns off warnings, the third line deletes the contents of the temp
table (which actually is permanently around, but could be deleted), the 4th
line does a insert query that loads the table, the 5th could run the report
(which uses a query running against the temp table) and the last lines turn
back on the warnings and turn off the hourglass. Of course, a possible
drawback of this approach is that the user must initiate a macro, rather
than a report. If it's a button on a form, the user won't know the
difference.

VBA could certainly do the same thing, but it's somewhat more complex than
the macros. In the macro editor, the queries and functions are in a list,
so it's just picking the one you want.

Knox

AlwaysFroosh! said:
Hmm, that's an interesting approach. So you use VBA to initiate the
queries
when the report loads, right? Is that what you mean? I don't know another
way
you would accomplish it, but I think I could get it to work using VBA.

Graham

Knox said:

Another approach is use a temporary table... Say you have 10 chained
queries. Take the result of queries 1 through 5 and insert into a table.
Then have queries 6 through 10 work from that table. That cuts the size
of
the complied queries roughly in half.

I've done things like this and used a macro to combine several steps into
one.

Good luck!

Knox

John W. Vinson said:

On Fri, 25 Jan 2008 15:16:04 -0800, AlwaysFroosh!

It's a stopgap to some extent, since the query is still huge, but it
can
stave
off the dreaded day of restructureing for a while! Hope you're having a
good
weekend. Maybe you should go get a *real* beer though...

Hey, now there's a great idea! When I think of the size of the query, I
always think of the number of fields I'm referencing and the operations
being
performed, but I guess you're right, it would be the actual length of
the
query. I'll have to try it out, I'll let you guys know if I have an
luck.
But
right now I want to go home, for it's the weekend, and it's Millertime!

:

On Fri, 25 Jan 2008 12:48:01 -0800, AlwaysFroosh!

Do you know if there is a maximum number of if statements a query
can
have?

My understanding is that the Query Too Complex error arises when the
*compiled* query exceeds a maximum size - 64KBytes I recall. This
would
include subqueries. Since Microsoft has not chosen to publish details
about
the compilation method or the structure of a compiled query, it's
hard
to
predict when a query will hit this limit.

But your query is a humungous monster, and it's not surprising to me
that it
is popping a QTC.

All I can suggest is aliasing all the table and fieldnames that you
can
to
one- and two-letter shortcuts so that you can have

IIF([K].[V1] =
0,NumberWithUnits([K].[M1],[K].[MFU]),NumberWithUnits([K].[F1],
[K.VFU])) AS PF1,

instead of

IIf([PRIM_IN_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([PRIM_IN_COMMONDATA].[MASS_FLOW_1],[PRIM_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([PRIM_IN_COMMONDATA].[VOL_FLOW_1],[PRIM_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [PRIMIN FLOW 1],

It makes your query harder to document but may be the only way around
your
problem (short of redesigning the datastructure which I suspect is
not a
pleasant option).

John W. Vinson [MVP]

John W. Vinson [MVP]

Click to expand...

Click to expand...

AlwaysFroosh! · Feb 4, 2008

It's been a while but I figured I owed at least a progress update!

I tried to get my query to work by shortening it using aliases. I understand
how this should work, but I can't seem to get it to make any difference.

Luckily I tried Knox's suggestion and it is working for me. I already rely
pretty heavily on VBA for all of my forms and for users to open the reports,
so it was actually pretty easy for me to implement Knox's suggestion. The
first 3 queries still 'piggyback' off of each other, but at this point I
write the data into a temp table. My final query then compares the old data
to the data in the temp table and flags revisions. This is working well for
me.

Thanks a lot for your suggestions and help.

Graham

John W. Vinson said:
On Fri, 25 Jan 2008 15:16:04 -0800, AlwaysFroosh!

It's a stopgap to some extent, since the query is still huge, but it can stave
off the dreaded day of restructureing for a while! Hope you're having a good
weekend. Maybe you should go get a *real* beer though...

Hey, now there's a great idea! When I think of the size of the query, I
always think of the number of fields I'm referencing and the operations being
performed, but I guess you're right, it would be the actual length of the
query. I'll have to try it out, I'll let you guys know if I have an luck. But
right now I want to go home, for it's the weekend, and it's Millertime!

John W. Vinson said:

On Fri, 25 Jan 2008 12:48:01 -0800, AlwaysFroosh!

Do you know if there is a maximum number of if statements a query can have?

My understanding is that the Query Too Complex error arises when the
*compiled* query exceeds a maximum size - 64KBytes I recall. This would
include subqueries. Since Microsoft has not chosen to publish details about
the compilation method or the structure of a compiled query, it's hard to
predict when a query will hit this limit.

But your query is a humungous monster, and it's not surprising to me that it
is popping a QTC.

All I can suggest is aliasing all the table and fieldnames that you can to
one- and two-letter shortcuts so that you can have

IIF([K].[V1] = 0,NumberWithUnits([K].[M1],[K].[MFU]),NumberWithUnits([K].[F1],
[K.VFU])) AS PF1,

instead of

IIf([PRIM_IN_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([PRIM_IN_COMMONDATA].[MASS_FLOW_1],[PRIM_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([PRIM_IN_COMMONDATA].[VOL_FLOW_1],[PRIM_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [PRIMIN FLOW 1],

It makes your query harder to document but may be the only way around your
problem (short of redesigning the datastructure which I suspect is not a
pleasant option).

John W. Vinson [MVP]

Click to expand...

Click to expand...

John W. Vinson [MVP]

AlwaysFroosh! · Feb 4, 2008

Great suggestion Knox, thanks. I've got this working quite nicely, so thank
you very much, my problem is fixed for another day!

Cheers!
Graham

Knox said:
Another approach is use a temporary table... Say you have 10 chained
queries. Take the result of queries 1 through 5 and insert into a table.
Then have queries 6 through 10 work from that table. That cuts the size of
the complied queries roughly in half.

I've done things like this and used a macro to combine several steps into
one.

Good luck!

Knox

John W. Vinson said:

On Fri, 25 Jan 2008 15:16:04 -0800, AlwaysFroosh!

It's a stopgap to some extent, since the query is still huge, but it can
stave
off the dreaded day of restructureing for a while! Hope you're having a
good
weekend. Maybe you should go get a *real* beer though...

Hey, now there's a great idea! When I think of the size of the query, I
always think of the number of fields I'm referencing and the operations
being
performed, but I guess you're right, it would be the actual length of the
query. I'll have to try it out, I'll let you guys know if I have an luck.
But
right now I want to go home, for it's the weekend, and it's Millertime!

:

On Fri, 25 Jan 2008 12:48:01 -0800, AlwaysFroosh!

Do you know if there is a maximum number of if statements a query can
have?

My understanding is that the Query Too Complex error arises when the
*compiled* query exceeds a maximum size - 64KBytes I recall. This would
include subqueries. Since Microsoft has not chosen to publish details
about
the compilation method or the structure of a compiled query, it's hard
to
predict when a query will hit this limit.

But your query is a humungous monster, and it's not surprising to me
that it
is popping a QTC.

All I can suggest is aliasing all the table and fieldnames that you can
to
one- and two-letter shortcuts so that you can have

IIF([K].[V1] =
0,NumberWithUnits([K].[M1],[K].[MFU]),NumberWithUnits([K].[F1],
[K.VFU])) AS PF1,

instead of

IIf([PRIM_IN_COMMONDATA].[VOL_FLOW_1]=0,NumberWithUnits([PRIM_IN_COMMONDATA].[MASS_FLOW_1],[PRIM_IN_COMMONDATA].[MASS_FLOW_UNITS]),NumberWithUnits([PRIM_IN_COMMONDATA].[VOL_FLOW_1],[PRIM_IN_COMMONDATA].[VOL_FLOW_UNITS]))
AS [PRIMIN FLOW 1],

It makes your query harder to document but may be the only way around
your
problem (short of redesigning the datastructure which I suspect is not a
pleasant option).

John W. Vinson [MVP]

Click to expand...

John W. Vinson [MVP]

Click to expand...

Code Check Request - Query to find reused values	2	Feb 9, 2010
System Resource Exceeded/Query Too Complex	4	Jan 20, 2010
Query is too Complex error message when trying to run query.	3	Oct 27, 2007
Query is too complex for Access 2007 ?	2	Jan 26, 2009
FSV318 VPN Client Software Prosafe	0	Feb 3, 2007
Vista Copy Speed over the network is slower that XP SP2 and OSX..much slower?	1	Jun 20, 2008
Windows XP Dell PC: c:\WINDOWS\system32\wavojami.dll not valid Windows image	2	Oct 4, 2011
Is it secure? WinXP 2-PC network via crossover cable, dial-up Internet firewalled, no ISC	3	Mar 11, 2004

Query is too complex

Guest

Guest

John Spencer

Brian Wilson

Guest

Brian Wilson

AlwaysFroosh!

AlwaysFroosh!

Ken Snell \(MVP\)

AlwaysFroosh!

John W. Vinson

AlwaysFroosh!

John W. Vinson

Knox

AlwaysFroosh!

Knox

AlwaysFroosh!

AlwaysFroosh!

Ask a Question

Similar Threads