Query Error Message

  • Thread starter Thread starter Dkline
  • Start date Start date
D

Dkline

When running a query of queries I am getting the error message "Not enough
space on termporary disk."

There are four queries - one from each four tables. The tables are linked
Excel files.

The query I'm trying to run uses one the queries as the key to pull the data
from the other three queries.

I have 12.9 GB of free space on this computer. All of the f iles are local
for now. Ultimately they'll end up on a server.

I cannot find anything in help or on Knowledge Base about this error. Does
this mean I need more RAM? I have 512 MB of RAM. I am running Access 2002
SP1.

I did notice that as the machine was trying to run this query it was
hammering at the hard disk.

Should I divide the three queries into smaller queries? Ultimately I still
have to pull the same information but will making smaller queries make this
feasible.

Should I consider a MakeTable? I have no experience with a MakeTable. Can a
MakeTable be created anytime someone opens the database?
 
You normally will get that message only when you're trying to run a huge
"Action" query - update, insert, delete, make table. It's hard to guess the
problem without knowing the general structure of your tables and the SQL
you're trying to run.

--
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 said:
When running a query of queries I am getting the error message "Not enough
space on termporary disk."

There are four queries - one from each four tables. The tables are linked
Excel files.

The query I'm trying to run uses one the queries as the key to pull the data
from the other three queries.

I have 12.9 GB of free space on this computer. All of the f iles are local
for now. Ultimately they'll end up on a server.

I cannot find anything in help or on Knowledge Base about this error. Does
this mean I need more RAM? I have 512 MB of RAM. I am running Access 2002
SP1.

I did notice that as the machine was trying to run this query it was
hammering at the hard disk.

Should I divide the three queries into smaller queries? Ultimately I still
have to pull the same information but will making smaller queries make this
feasible.

Should I consider a MakeTable? I have no experience with a MakeTable. Can a
MakeTable be created anytime someone opens the database?
Click to expand...

Dkline,

How big it the Windows swap file?


Sincerely,

Chris O.
 
John - I have your Access 2003 Inside Out book.

The structure I'm dealing with has 6 linked Excel files and a linked MDB. In
this specific problem, I'm using four of the Excel files. Each Excel file
has a number of worksheets with the needed information. I am only attempting
a Select query.

"Tables-Current" is the worksheet with the common data. One of its
worksheets has the lowest common denominator field for all the data - the
"Policy Number".

The other three files have similar information but are different types if
insurance policies. From each policy I will be pulling 57 values.
Single Life - "Current Policy Data - Single"
Joint Life - "Current Policy Data - Joint"
Annuities - "Current Policy Data - Annuity"

In each of these workbooks, I am pulling information from 7 worksheets. Who
is the insured, the beneficiary, the agent, etc. and their respective
information is on separate worksheets. (I have discussed normalization of
the data with senior management but it is not considered a priority.)

For each of the "Current Policy Data" linked tables, I created a query to
pull all the information based on "Policy Number". Those work fine.

Then I created a query of these three queries to get the information in one
query for all of the policies. It is this query that is blowing up. Further
I was testing it with only one field (in addition to Policy Number) being
requested - the last name of the insured. For each field I will be using an
expression. The expression here is:
Insured/Annuitant Last Name: qryByPN_Annuities.[Annuitant - Last Name] &
qryByPN_Joint.[Joint_Insured_1.Insured - Last Name] &
qryByPN_Joint.[Joint_Insured_2.Insured - Last Name] &
qryByPN_Single.[Insured - Last Name]

In total I will have 57 expressions similar to the above.

The intention of this whole thing is to give senior managment a snapshot of
the business. It is not to be used for data entry - just ways to look at the
data from various angles - such as by client, by agent, by Policy Number and
a few other views. Other Excel files and an Access file will also be
included in the total package.

Am I better off doing a Make-Table Query? Can I have the Make-Table query
automatically run whenever the MDB is opened?
 
Well, thanks for buying my book - I hope you find it useful.

But I still have no clue about your problem. I suspect you're exceeding the
capabilities of the Excel Isam that Access must use to "read" Excel
workbooks. You might have to import the workbooks to get your queries to
work.

However, it might have something to do with the queries you're trying to
run. Please open each query in Design view, choose SQL View from the View
menu, and paste the text into a reply.

--
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 said:
John - I have your Access 2003 Inside Out book.

The structure I'm dealing with has 6 linked Excel files and a linked MDB. In
this specific problem, I'm using four of the Excel files. Each Excel file
has a number of worksheets with the needed information. I am only attempting
a Select query.

"Tables-Current" is the worksheet with the common data. One of its
worksheets has the lowest common denominator field for all the data - the
"Policy Number".

The other three files have similar information but are different types if
insurance policies. From each policy I will be pulling 57 values.
Single Life - "Current Policy Data - Single"
Joint Life - "Current Policy Data - Joint"
Annuities - "Current Policy Data - Annuity"

In each of these workbooks, I am pulling information from 7 worksheets. Who
is the insured, the beneficiary, the agent, etc. and their respective
information is on separate worksheets. (I have discussed normalization of
the data with senior management but it is not considered a priority.)

For each of the "Current Policy Data" linked tables, I created a query to
pull all the information based on "Policy Number". Those work fine.

Then I created a query of these three queries to get the information in one
query for all of the policies. It is this query that is blowing up. Further
I was testing it with only one field (in addition to Policy Number) being
requested - the last name of the insured. For each field I will be using an
expression. The expression here is:
Insured/Annuitant Last Name: qryByPN_Annuities.[Annuitant - Last Name] &
qryByPN_Joint.[Joint_Insured_1.Insured - Last Name] &
qryByPN_Joint.[Joint_Insured_2.Insured - Last Name] &
qryByPN_Single.[Insured - Last Name]

In total I will have 57 expressions similar to the above.

The intention of this whole thing is to give senior managment a snapshot of
the business. It is not to be used for data entry - just ways to look at the
data from various angles - such as by client, by agent, by Policy Number and
a few other views. Other Excel files and an Access file will also be
included in the total package.

Am I better off doing a Make-Table Query? Can I have the Make-Table query
automatically run whenever the MDB is opened?

John Viescas said:
You normally will get that message only when you're trying to run a huge
"Action" query - update, insert, delete, make table. It's hard to guess the
problem without knowing the general structure of your tables and the SQL
you're trying to run.

--
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)
the
data Can
Click to expand...
Click to expand...
 
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];
 
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 said:
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];
Click to expand...
 
I'm trying to combine 3 Excel linked tables into one which I believe
prevents me from using a UNION.

I am going to try a Make-Table query.and build one table out of the three.
The three tables are Excel files as linked tables in Access.

I have writtten VBA code in Excel to create a unified Excel table. I could
link to that combined Excel file but it does require that a macro be run
whenever the database is started.
 
If you can "combine" them using Make Table / Append, then you can also UNION
them. The trick is to use UNION ALL. If some of the "tables" have columns
that don't exist in the other tables, you can generate the missing column(s)
with zero length string or Null constants. As in:

SELECT FirstName, LastName, Address
FROM Customers
UNION ALL
SELECT SupplierName, "" As LastName, Address
FROM Suppliers
 
Back
Top