Re: Query Error Message

From: John Viescas (JohnV_at_nomail.please)
Date: 04/19/04 

Date: Mon, 19 Apr 2004 09:51:55 -0500

Earlier in the thread, you said you're trying to "combine" these three
queries in another query. Are you building a UNION query? If so, Access is
trying to build a temporary index on *all* the fields to eliminate any
potential duplicates. However, with this many fields returned, you're
probably exceeding the 4K max size of an index entry for some rows, so
that's what is causing it to break. The error message should be clearer.

You can simplify it by removing the ORDER BY in each of the three queries
below and using UNION ALL instead of UNION in your final query. 

-- 
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"Dkline" <Dkline001@comcast.net> wrote in message
news:%23xTPgIeJEHA.3120@TK2MSFTNGP09.phx.gbl...
> Below are the SQL for each of the three tables. The other angle I'm
> contemplating is have Excel programatically combining these three
> spreadsheets into one and then using that combined spreadsheet for the
> query.
>
> Perhaps I prevail upon management to convert these Excel files permanently
> to Access. The difficulty there is the dictum from management that I am
not
> to change the way people work. This means that since the person maintaing
> these Excel files doesn's know Access, then they continue in Excel.
>
>
> Single Table
> SELECT Single_Producers.[Policy Number], Single_Commissions.[Premium
> Commission Table ID#], DistributionChargeDK.Status,
> DistributionChargeDK.[Rate ], DistributionChargeDK.[Initial Premium
Amount,
> if applicable], DistributionChargeDK.[Maximum Distribution Charge  $
Limit],
> Single_Insured.[Insured Prefix], Single_Insured.[Insured - First Name],
> Single_Insured.[Insured - Last Name], Single_Owners.[Policy Owner -
Prefix],
> Single_Owners.[Policy Owner - First Name], Single_Owners.[Policy Owner -
> Last Name], Single_Owners.[Policy Owner Code], Single_Owners.[Policy Owner
> Address - Line 1], Single_Owners.[Policy Owner Address - Line 2],
> Single_Owners.[Policy Owner Address - Line 3], Single_Owners.[Policy Owner
> Address - Line 4], Single_Owners.[Policy Owner Address - Line 5 - City],
> Single_Owners.[Policy Owner Address - Line 5 - State],
Single_Owners.[Policy
> Owner Address - Line 5 - Zip Code], Single_Owners.[Policy Owner Tax ID #],
> Single_Owners.[Trustee #1 - Prefix], Single_Owners.[Trustee #1 - First
> Name], Single_Owners.[Trustee #1 - Last Name], Single_Owners.[Trustee #2 -
> Prefix], Single_Owners.[Trustee #2 - First Name], Single_Owners.[Trustee
> #2 - Last Name], Single_Owners.[Trustee #3 - Prefix],
Single_Owners.[Trustee
> #3 - First Name], Single_Owners.[Trustee #3 - Last Name],
> Single_Owners.[Trustee #4- Prefix], Single_Owners.[Trustee #4 - First
Name],
> Single_Owners.[Trustee #4 - Last Name], Single_Policy.[Policy Date],
> Single_Policy.[Face Amount], Single_Policy.[Product IDCode],
> Single_Policy.[Definition of Life Insurance Test], Single_Policy.[Death
> Benefit Option], Single_Policy.[Jurisdiction State (Premium Tax State)],
> Single_Policy.[Insured underwriting class], Single_Policy.[Insured
> substandard rating], Single_Policy.[Insured smoker/non smoker indicator],
> Single_Policy.[Insured flat extra table], Single_Policy.[Policy Company ID
> Code], Single_Policy.[Policy Status], Single_Policy.[Underwriting Fee
Table
> ID Code], Single_Producers.[Producer ID Code]
> FROM (((Single_Producers INNER JOIN Single_Policy ON
> Single_Producers.[Policy Number] = Single_Policy.[Policy Number]) INNER
JOIN
> Single_Owners ON Single_Producers.[Policy Number] = Single_Owners.[Policy
> Number]) INNER JOIN Single_Insured ON Single_Producers.[Policy Number] =
> Single_Insured.[Policy Number]) INNER JOIN (Single_Commissions INNER JOIN
> DistributionChargeDK ON Single_Commissions.[Premium Commission Table ID#]
=
> DistributionChargeDK.[ID Number]) ON Single_Producers.[Policy Number] =
> Single_Commissions.[Policy Number]
> ORDER BY Single_Producers.[Policy Number];
>
> Joint Table
> SELECT Joint_Policy.[Policy Number], Joint_Commissions.[Premium Commission
> Table ID#], DistributionChargeDK.Status, DistributionChargeDK.[Rate ],
> DistributionChargeDK.[Initial Premium Amount, if applicable],
> DistributionChargeDK.[Maximum Distribution Charge  $ Limit],
> Joint_Beneficiary.[Beneficiary #1 - First Name],
> Joint_Beneficiary.[Beneficiary #1 - Last Name],
> Joint_Beneficiary.[Beneficiary#1 Percentage], Joint_Insured_1.[Insured
> Prefix], Joint_Insured_1.[Insured - First Name],
Joint_Insured_1.[Insured -
> Last Name], Joint_Insured_2.[Insured Prefix], Joint_Insured_2.[Insured -
> First Name], Joint_Insured_2.[Insured - Last Name], Joint_Owner.[Policy
> Owner - Prefix], Joint_Owner.[Policy Owner - First Name],
> Joint_Owner.[Policy Owner - Last Name], Joint_Owner.[Policy Owner Code],
> Joint_Owner.[Policy Owner Address - Line 1], Joint_Owner.[Policy Owner
> Address - Line 2], Joint_Owner.[Policy Owner Address - Line 3],
> Joint_Owner.[Policy Owner Address - Line 4], Joint_Owner.[Policy Owner
> Address - Line 5 - City], Joint_Owner.[Policy Owner Address - Line 5 -
> State], Joint_Owner.[Policy Owner Address - Line 5 - Zip Code],
> Joint_Owner.[Policy Owner Tax ID #], Joint_Owner.[Trustee #1 - Prefix],
> Joint_Owner.[Trustee #1 - First Name], Joint_Owner.[Trustee #1 - Last
Name],
> Joint_Owner.[Trustee #2 - Prefix], Joint_Owner.[Trustee #2 - First Name],
> Joint_Owner.[Trustee #2 - Last Name], Joint_Owner.[Trustee #3 - Prefix],
> Joint_Owner.[Trustee #3 - First Name], Joint_Owner.[Trustee #3 - Last
Name],
> Joint_Owner.[Trustee #4- Prefix], Joint_Owner.[Trustee #4 - First Name],
> Joint_Owner.[Trustee #4 - Last Name], Joint_Policy.[Policy Date],
> Joint_Policy.[Face Amount], Joint_Policy.[Product IDCode],
> Joint_Policy.[Definition of Life Insurance Test], Joint_Policy.[Death
> Benefit Option], Joint_Policy.[Jurisdiction State (Premium Tax State)],
> Joint_Policy.[Insured underwriting class 1], Joint_Policy.[Insured
> substandard rating 1], Joint_Policy.[Insured smoker/non smoker indicator
1],
> Joint_Policy.[Insured flat extra table 1], Joint_Policy.[Insured
> underwriting class 2], Joint_Policy.[Insured substandard rating 2],
> Joint_Policy.[Insured smoker/non smoker indicator 2],
Joint_Policy.[Insured
> flat extra table 2], Joint_Policy.[Policy Company ID Code],
> Joint_Policy.[Policy Status], Joint_Policy.[Underwriting Fee Table ID
Code],
> Joint_Producers.[Producer ID Code]
> FROM Joint_Insured_2, ((((Joint_Policy INNER JOIN Joint_Producers ON
> Joint_Policy.[Policy Number] = Joint_Producers.[Policy Number]) INNER JOIN
> Joint_Owner ON Joint_Policy.[Policy Number] = Joint_Owner.[Policy Number])
> INNER JOIN Joint_Insured_1 ON Joint_Policy.[Policy Number] =
> Joint_Insured_1.[Policy Number]) INNER JOIN Joint_Beneficiary ON
> Joint_Policy.[Policy Number] = Joint_Beneficiary.[Policy Number]) INNER
JOIN
> (Joint_Commissions INNER JOIN DistributionChargeDK ON
> Joint_Commissions.[Premium Commission Table ID#] =
DistributionChargeDK.[ID
> Number]) ON Joint_Policy.[Policy Number] = Joint_Commissions.[Policy
Number]
> ORDER BY Joint_Policy.[Policy Number];
>
> Annuity Table
> SELECT Annuity_Policy.[Policy Number], Annuity_Annuitant.[Annuitant
Prefix],
> Annuity_Annuitant.[Annuitant - First Name], Annuity_Annuitant.[Annuitant -
> Last Name], Annuity_Beneficiary.[Beneficiary #1 - First Name],
> Annuity_Beneficiary.[Beneficiary #1 - Last Name], Annuity_Owners.[Policy
> Owner - First Name], Annuity_Owners.[Policy Owner - Last Name],
> Annuity_Owners.[Policy Owner Address - Line 1], Annuity_Owners.[Policy
Owner
> Address - Line 2], Annuity_Owners.[Policy Owner Address - Line 3],
> Annuity_Owners.[Policy Owner Address - Line 4], Annuity_Owners.[Policy
Owner
> Address - Line 5 - City], Annuity_Owners.[Policy Owner Address - Line 5 -
> State], Annuity_Owners.[Policy Owner Address - Line 5 - Zip Code],
> Annuity_Owners.[Policy Owner Tax ID #], Annuity_Owners.[Trustee #1 -
> Prefix], Annuity_Owners.[Trustee #1 - First Name], Annuity_Owners.[Trustee
> #1 - Last Name], Annuity_Owners.[Trustee #2 - Prefix],
> Annuity_Owners.[Trustee #2 - First Name], Annuity_Owners.[Trustee #2 -
Last
> Name], Annuity_Owners.[Trustee #3 - Prefix], Annuity_Owners.[Trustee #3 -
> First Name], Annuity_Owners.[Trustee #3 - Last Name],
> Annuity_Owners.[Trustee #4- Prefix], Annuity_Owners.[Trustee #4 - First
> Name], Annuity_Owners.[Trustee #4 - Last Name]
> FROM (((((Annuity_Policy INNER JOIN Annuity_Allocations ON
> Annuity_Policy.[Policy Number] = Annuity_Allocations.[Policy Number])
INNER
> JOIN Annuity_Beneficiary ON Annuity_Policy.[Policy Number] =
> Annuity_Beneficiary.[Policy Number]) INNER JOIN Annuity_Commissions ON
> Annuity_Policy.[Policy Number] = Annuity_Commissions.[Policy Number])
INNER
> JOIN Annuity_Owners ON Annuity_Policy.[Policy Number] =
> Annuity_Owners.[Policy Number]) INNER JOIN Annuity_Producers ON
> Annuity_Policy.[Policy Number] = Annuity_Producers.[Policy Number]) INNER
> JOIN Annuity_Annuitant ON Annuity_Policy.[Policy Number] =
> Annuity_Annuitant.[Policy Number]
> ORDER BY Annuity_Policy.[Policy Number];
>
>