Need someone to look at SQL statement

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

Dkline

SELECT T1.PolicyNumber, T1.[Net Investment Amount], T1.[Anticipated
Investment Fund Name], T1.[Contact Fund?], T1.[Confirm to Accounting?],
T1.[AccountID (from)], T1.[Account ID (to)], T1.[Sub Doc], T1.[Cover
Letter], T1.[Trade Date per Info Sheet (for new premium only)],
T1.[Effective Date], T1.[Wire Date], T1.[Sub Doc Date], T1.[Asset Allocation
Instructions], T1.[Client Allocation Instructions], T1.[Wire Instructions],
T1.Status, T1.Complete, T3.SiteIDNumber, T4.Custodian, T2.ID, T2.Requestor,
T2.[Request Type], T2.PolicyNumber, T2.AccountID, T2.[Date Redemption
Submitted], T2.[Requested Effective Date], T2.[Full or Partial], T2.[Partial
Amount], T2.[Date Expected], T2.[% or $ Expected], T2.[Date Residual
Expected], T2.[Residual % or $ Expected], T2.Comment
FROM ([Money Market Reserve] AS T1 INNER JOIN ([Account Inventory] AS T3
INNER JOIN [Site Inventory] AS T4 ON T3.SiteIDNumber = T4.SiteIDNumber) ON
T1.[Account ID (to)] = T3.AccountID) INNER JOIN [Pending Transactions] AS T2
ON T1.PolicyNumber = T2.PolicyNumber
UNION
SELECT T1.[PolicyNumber], T1.[Net Investment Amount], T1.[Anticipated
Investment Fund Name], T1.[Contact Fund?], T1.[Confirm to Accounting?],
T1.[AccountID (from)], T1.[Account ID (to)], T1.[Sub Doc], T1.[Cover
Letter], T1.[Trade Date per Info Sheet (for new premium only)],
T1.[Effective Date], T1.[Wire Date], T1.[Sub Doc Date], T1.[Asset Allocation
Instructions], T1.[Client Allocation Instructions], T1.[Wire Instructions],
T1.[Status], T1.[Complete], T3.[SiteIDNumber], T4.Custodian, Null, Null,
Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null
FROM ([Money Market Reserve] AS T1 INNER JOIN ([Account Inventory] AS T3
INNER JOIN [Site Inventory] AS T4 ON T3.SiteIDNumber = T4.SiteIDNumber) ON
T1.[Account ID (to)] = T3.AccountID)
WHERE [PolicyNumber] NOT IN (SELECT DISTINCT [PolicyNumber]
FROM [Pending Transactions])
UNION
SELECT T2.[PolicyNumber], Null, Null, Null, Null, Null, Null, Null, Null,
Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, T2.[ID],
T2.[Requestor], T2.[Request Type], T2.[PolicyNumber], T2.[AccountID],
T2.[Date Redemption Submitted], T2.[Requested Effective Date], T2.[Full or
Partial], T2.[Partial Amount], T2.[Date Expected], T2.[% or $ Expected],
T2.[Date Residual Expected], T2.[Residual % or $ Expected], T2.[Comment]
FROM [Pending Transactions] AS T2
WHERE [PolicyNumber] NOT IN (SELECT DISTINCT [PolicyNumber]
FROM [Money Market Reserve]);

I've got four tables in here.
T1 = [Money Market Reserve]
T2 = [Pending Transactions]
T3 = [Account Inventory]
T4 = [Site Inventory]

It is the "middle" one (between the two UNIONs) that is causing the
difficulty. I get an error message of "The specified filed '[PolicyNumber]'
could refer to more than one table used in the FROM clause of your SQL
statement."

If I copy just the portion above the first UNION into the SQL statement it
runs perfectly. If I copy just the bottom portion below the second UNION
into the SQL statement it runs perfectly.

I can't see straight anymore from looking at this. A second set of eyes
looking at this would be appreciated.
 
Discovered that the T3 table [Account Inventory] also had [PolicyNumber] as
a field. Specifiying the table in the WHERE as "WHERE [T1.PolicyNumber] NOT
IN (SELECT DISTINCT [PolicyNumber]" solved the problem.

Sorry for the false alarm.

Dkline said:
SELECT T1.PolicyNumber, T1.[Net Investment Amount], T1.[Anticipated
Investment Fund Name], T1.[Contact Fund?], T1.[Confirm to Accounting?],
T1.[AccountID (from)], T1.[Account ID (to)], T1.[Sub Doc], T1.[Cover
Letter], T1.[Trade Date per Info Sheet (for new premium only)],
T1.[Effective Date], T1.[Wire Date], T1.[Sub Doc Date], T1.[Asset Allocation
Instructions], T1.[Client Allocation Instructions], T1.[Wire Instructions],
T1.Status, T1.Complete, T3.SiteIDNumber, T4.Custodian, T2.ID, T2.Requestor,
T2.[Request Type], T2.PolicyNumber, T2.AccountID, T2.[Date Redemption
Submitted], T2.[Requested Effective Date], T2.[Full or Partial], T2.[Partial
Amount], T2.[Date Expected], T2.[% or $ Expected], T2.[Date Residual
Expected], T2.[Residual % or $ Expected], T2.Comment
FROM ([Money Market Reserve] AS T1 INNER JOIN ([Account Inventory] AS T3
INNER JOIN [Site Inventory] AS T4 ON T3.SiteIDNumber = T4.SiteIDNumber) ON
T1.[Account ID (to)] = T3.AccountID) INNER JOIN [Pending Transactions] AS T2
ON T1.PolicyNumber = T2.PolicyNumber
UNION
SELECT T1.[PolicyNumber], T1.[Net Investment Amount], T1.[Anticipated
Investment Fund Name], T1.[Contact Fund?], T1.[Confirm to Accounting?],
T1.[AccountID (from)], T1.[Account ID (to)], T1.[Sub Doc], T1.[Cover
Letter], T1.[Trade Date per Info Sheet (for new premium only)],
T1.[Effective Date], T1.[Wire Date], T1.[Sub Doc Date], T1.[Asset Allocation
Instructions], T1.[Client Allocation Instructions], T1.[Wire Instructions],
T1.[Status], T1.[Complete], T3.[SiteIDNumber], T4.Custodian, Null, Null,
Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null
FROM ([Money Market Reserve] AS T1 INNER JOIN ([Account Inventory] AS T3
INNER JOIN [Site Inventory] AS T4 ON T3.SiteIDNumber = T4.SiteIDNumber) ON
T1.[Account ID (to)] = T3.AccountID)
WHERE [PolicyNumber] NOT IN (SELECT DISTINCT [PolicyNumber]
FROM [Pending Transactions])
UNION
SELECT T2.[PolicyNumber], Null, Null, Null, Null, Null, Null, Null, Null,
Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, T2.[ID],
T2.[Requestor], T2.[Request Type], T2.[PolicyNumber], T2.[AccountID],
T2.[Date Redemption Submitted], T2.[Requested Effective Date], T2.[Full or
Partial], T2.[Partial Amount], T2.[Date Expected], T2.[% or $ Expected],
T2.[Date Residual Expected], T2.[Residual % or $ Expected], T2.[Comment]
FROM [Pending Transactions] AS T2
WHERE [PolicyNumber] NOT IN (SELECT DISTINCT [PolicyNumber]
FROM [Money Market Reserve]);

I've got four tables in here.
T1 = [Money Market Reserve]
T2 = [Pending Transactions]
T3 = [Account Inventory]
T4 = [Site Inventory]

It is the "middle" one (between the two UNIONs) that is causing the
difficulty. I get an error message of "The specified filed '[PolicyNumber]'
could refer to more than one table used in the FROM clause of your SQL
statement."

If I copy just the portion above the first UNION into the SQL statement it
runs perfectly. If I copy just the bottom portion below the second UNION
into the SQL statement it runs perfectly.

I can't see straight anymore from looking at this. A second set of eyes
looking at this would be appreciated.
Click to expand...
 
Dear D:

Simplify! Simplify! Simplify!

I think you would have this error with just the middle SELECT query by
itself. If so, then the rest of this SQL is unnecessary to fixing the
problem. Look at just that query:

SELECT T1.[PolicyNumber], T1.[Net Investment Amount],
T1.[Anticipated Investment Fund Name], T1.[Contact Fund?],
T1.[Confirm to Accounting?], T1.[AccountID (from)],
T1.[Account ID (to)], T1.[Sub Doc], T1.[Cover Letter],
T1.[Trade Date per Info Sheet (for new premium only)],
T1.[Effective Date], T1.[Wire Date], T1.[Sub Doc Date],
T1.[Asset Allocation Instructions],
T1.[Client Allocation Instructions], T1.[Wire Instructions],
T1.[Status], T1.[Complete], T3.[SiteIDNumber], T4.Custodian,
Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null,
Null, Null, Null
FROM ([Money Market Reserve] AS T1
INNER JOIN ([Account Inventory] AS T3
INNER JOIN [Site Inventory] AS T4
ON T3.SiteIDNumber = T4.SiteIDNumber)
ON T1.[Account ID (to)] = T3.AccountID)
WHERE [PolicyNumber] NOT IN
(SELECT DISTINCT [PolicyNumber] FROM [Pending Transactions])

I have formatted this to my personal liking to help me study it.

Now, in the WHERE clause you reference [PolicyNumber] without
specifying in which table it is found. The error message is about
this. The query references 3 tables:

[Money Market Reserve]
[Account Inventory]
[Site Inventory]

Apparently, the column named PolicyNumber appears in more than one of
these 3 tables. So, the query cannot resolve this ambiguity and gives
you that error message.

So, change that part of the query to read:

WHERE XX.[PolicyNumber] NOT IN

replacing XX with T1, T3, or T4, depending on which of those 3 tables
contains the PolicyNumber you want to test. It may well be that the
different PolicyNumber columns will always contain the same value, but
that doesn't matter. This error is detected in the query syntax
before the query even starts looking at the data. So, it may not be
ambiguous in reality, but it is ambiguous syntactically.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


SELECT T1.PolicyNumber, T1.[Net Investment Amount], T1.[Anticipated
Investment Fund Name], T1.[Contact Fund?], T1.[Confirm to Accounting?],
T1.[AccountID (from)], T1.[Account ID (to)], T1.[Sub Doc], T1.[Cover
Letter], T1.[Trade Date per Info Sheet (for new premium only)],
T1.[Effective Date], T1.[Wire Date], T1.[Sub Doc Date], T1.[Asset Allocation
Instructions], T1.[Client Allocation Instructions], T1.[Wire Instructions],
T1.Status, T1.Complete, T3.SiteIDNumber, T4.Custodian, T2.ID, T2.Requestor,
T2.[Request Type], T2.PolicyNumber, T2.AccountID, T2.[Date Redemption
Submitted], T2.[Requested Effective Date], T2.[Full or Partial], T2.[Partial
Amount], T2.[Date Expected], T2.[% or $ Expected], T2.[Date Residual
Expected], T2.[Residual % or $ Expected], T2.Comment
FROM ([Money Market Reserve] AS T1 INNER JOIN ([Account Inventory] AS T3
INNER JOIN [Site Inventory] AS T4 ON T3.SiteIDNumber = T4.SiteIDNumber) ON
T1.[Account ID (to)] = T3.AccountID) INNER JOIN [Pending Transactions] AS T2
ON T1.PolicyNumber = T2.PolicyNumber
UNION
SELECT T1.[PolicyNumber], T1.[Net Investment Amount], T1.[Anticipated
Investment Fund Name], T1.[Contact Fund?], T1.[Confirm to Accounting?],
T1.[AccountID (from)], T1.[Account ID (to)], T1.[Sub Doc], T1.[Cover
Letter], T1.[Trade Date per Info Sheet (for new premium only)],
T1.[Effective Date], T1.[Wire Date], T1.[Sub Doc Date], T1.[Asset Allocation
Instructions], T1.[Client Allocation Instructions], T1.[Wire Instructions],
T1.[Status], T1.[Complete], T3.[SiteIDNumber], T4.Custodian, Null, Null,
Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null
FROM ([Money Market Reserve] AS T1 INNER JOIN ([Account Inventory] AS T3
INNER JOIN [Site Inventory] AS T4 ON T3.SiteIDNumber = T4.SiteIDNumber) ON
T1.[Account ID (to)] = T3.AccountID)
WHERE [PolicyNumber] NOT IN (SELECT DISTINCT [PolicyNumber]
FROM [Pending Transactions])
UNION
SELECT T2.[PolicyNumber], Null, Null, Null, Null, Null, Null, Null, Null,
Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, T2.[ID],
T2.[Requestor], T2.[Request Type], T2.[PolicyNumber], T2.[AccountID],
T2.[Date Redemption Submitted], T2.[Requested Effective Date], T2.[Full or
Partial], T2.[Partial Amount], T2.[Date Expected], T2.[% or $ Expected],
T2.[Date Residual Expected], T2.[Residual % or $ Expected], T2.[Comment]
FROM [Pending Transactions] AS T2
WHERE [PolicyNumber] NOT IN (SELECT DISTINCT [PolicyNumber]
FROM [Money Market Reserve]);

I've got four tables in here.
T1 = [Money Market Reserve]
T2 = [Pending Transactions]
T3 = [Account Inventory]
T4 = [Site Inventory]

It is the "middle" one (between the two UNIONs) that is causing the
difficulty. I get an error message of "The specified filed '[PolicyNumber]'
could refer to more than one table used in the FROM clause of your SQL
statement."

If I copy just the portion above the first UNION into the SQL statement it
runs perfectly. If I copy just the bottom portion below the second UNION
into the SQL statement it runs perfectly.

I can't see straight anymore from looking at this. A second set of eyes
looking at this would be appreciated.
Click to expand...
 
Dear D:

Yah!

The syntax should look like this, however:

WHERE T1.[PolicyNumber] NOT IN

with the T1 outside the brackets. I'm a bit surprised it would work
the way you stated. [T1.PolicyNumber] should reference a column with
the name "T1.PolicyNumber" rather than a column named PolicyNumber in
the table aliased as T1.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Discovered that the T3 table [Account Inventory] also had [PolicyNumber] as
a field. Specifiying the table in the WHERE as "WHERE [T1.PolicyNumber] NOT
IN (SELECT DISTINCT [PolicyNumber]" solved the problem.

Sorry for the false alarm.

Dkline said:
SELECT T1.PolicyNumber, T1.[Net Investment Amount], T1.[Anticipated
Investment Fund Name], T1.[Contact Fund?], T1.[Confirm to Accounting?],
T1.[AccountID (from)], T1.[Account ID (to)], T1.[Sub Doc], T1.[Cover
Letter], T1.[Trade Date per Info Sheet (for new premium only)],
T1.[Effective Date], T1.[Wire Date], T1.[Sub Doc Date], T1.[Asset Allocation
Instructions], T1.[Client Allocation Instructions], T1.[Wire Instructions],
T1.Status, T1.Complete, T3.SiteIDNumber, T4.Custodian, T2.ID, T2.Requestor,
T2.[Request Type], T2.PolicyNumber, T2.AccountID, T2.[Date Redemption
Submitted], T2.[Requested Effective Date], T2.[Full or Partial], T2.[Partial
Amount], T2.[Date Expected], T2.[% or $ Expected], T2.[Date Residual
Expected], T2.[Residual % or $ Expected], T2.Comment
FROM ([Money Market Reserve] AS T1 INNER JOIN ([Account Inventory] AS T3
INNER JOIN [Site Inventory] AS T4 ON T3.SiteIDNumber = T4.SiteIDNumber) ON
T1.[Account ID (to)] = T3.AccountID) INNER JOIN [Pending Transactions] AS T2
ON T1.PolicyNumber = T2.PolicyNumber
UNION
SELECT T1.[PolicyNumber], T1.[Net Investment Amount], T1.[Anticipated
Investment Fund Name], T1.[Contact Fund?], T1.[Confirm to Accounting?],
T1.[AccountID (from)], T1.[Account ID (to)], T1.[Sub Doc], T1.[Cover
Letter], T1.[Trade Date per Info Sheet (for new premium only)],
T1.[Effective Date], T1.[Wire Date], T1.[Sub Doc Date], T1.[Asset Allocation
Instructions], T1.[Client Allocation Instructions], T1.[Wire Instructions],
T1.[Status], T1.[Complete], T3.[SiteIDNumber], T4.Custodian, Null, Null,
Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null
FROM ([Money Market Reserve] AS T1 INNER JOIN ([Account Inventory] AS T3
INNER JOIN [Site Inventory] AS T4 ON T3.SiteIDNumber = T4.SiteIDNumber) ON
T1.[Account ID (to)] = T3.AccountID)
WHERE [PolicyNumber] NOT IN (SELECT DISTINCT [PolicyNumber]
FROM [Pending Transactions])
UNION
SELECT T2.[PolicyNumber], Null, Null, Null, Null, Null, Null, Null, Null,
Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, T2.[ID],
T2.[Requestor], T2.[Request Type], T2.[PolicyNumber], T2.[AccountID],
T2.[Date Redemption Submitted], T2.[Requested Effective Date], T2.[Full or
Partial], T2.[Partial Amount], T2.[Date Expected], T2.[% or $ Expected],
T2.[Date Residual Expected], T2.[Residual % or $ Expected], T2.[Comment]
FROM [Pending Transactions] AS T2
WHERE [PolicyNumber] NOT IN (SELECT DISTINCT [PolicyNumber]
FROM [Money Market Reserve]);

I've got four tables in here.
T1 = [Money Market Reserve]
T2 = [Pending Transactions]
T3 = [Account Inventory]
T4 = [Site Inventory]

It is the "middle" one (between the two UNIONs) that is causing the
difficulty. I get an error message of "The specified filed '[PolicyNumber]'
could refer to more than one table used in the FROM clause of your SQL
statement."

If I copy just the portion above the first UNION into the SQL statement it
runs perfectly. If I copy just the bottom portion below the second UNION
into the SQL statement it runs perfectly.

I can't see straight anymore from looking at this. A second set of eyes
looking at this would be appreciated.
Click to expand...
Click to expand...
 
It did work with the T1 inside the brackets but I have now corrected per
your observation.

Thanks for the help.

Tom Ellison said:
Dear D:

Yah!

The syntax should look like this, however:

WHERE T1.[PolicyNumber] NOT IN

with the T1 outside the brackets. I'm a bit surprised it would work
the way you stated. [T1.PolicyNumber] should reference a column with
the name "T1.PolicyNumber" rather than a column named PolicyNumber in
the table aliased as T1.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Discovered that the T3 table [Account Inventory] also had [PolicyNumber] as
a field. Specifiying the table in the WHERE as "WHERE [T1.PolicyNumber] NOT
IN (SELECT DISTINCT [PolicyNumber]" solved the problem.

Sorry for the false alarm.

Dkline said:
SELECT T1.PolicyNumber, T1.[Net Investment Amount], T1.[Anticipated
Investment Fund Name], T1.[Contact Fund?], T1.[Confirm to Accounting?],
T1.[AccountID (from)], T1.[Account ID (to)], T1.[Sub Doc], T1.[Cover
Letter], T1.[Trade Date per Info Sheet (for new premium only)],
T1.[Effective Date], T1.[Wire Date], T1.[Sub Doc Date], T1.[Asset Allocation
Instructions], T1.[Client Allocation Instructions], T1.[Wire Instructions],
T1.Status, T1.Complete, T3.SiteIDNumber, T4.Custodian, T2.ID, T2.Requestor,
T2.[Request Type], T2.PolicyNumber, T2.AccountID, T2.[Date Redemption
Submitted], T2.[Requested Effective Date], T2.[Full or Partial], T2.[Partial
Amount], T2.[Date Expected], T2.[% or $ Expected], T2.[Date Residual
Expected], T2.[Residual % or $ Expected], T2.Comment
FROM ([Money Market Reserve] AS T1 INNER JOIN ([Account Inventory] AS T3
INNER JOIN [Site Inventory] AS T4 ON T3.SiteIDNumber = T4.SiteIDNumber) ON
T1.[Account ID (to)] = T3.AccountID) INNER JOIN [Pending Transactions]
Click to expand...
AS
T2
ON T1.PolicyNumber = T2.PolicyNumber
UNION
SELECT T1.[PolicyNumber], T1.[Net Investment Amount], T1.[Anticipated
Investment Fund Name], T1.[Contact Fund?], T1.[Confirm to Accounting?],
T1.[AccountID (from)], T1.[Account ID (to)], T1.[Sub Doc], T1.[Cover
Letter], T1.[Trade Date per Info Sheet (for new premium only)],
T1.[Effective Date], T1.[Wire Date], T1.[Sub Doc Date], T1.[Asset Allocation
Instructions], T1.[Client Allocation Instructions], T1.[Wire Instructions],
T1.[Status], T1.[Complete], T3.[SiteIDNumber], T4.Custodian, Null, Null,
Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null
FROM ([Money Market Reserve] AS T1 INNER JOIN ([Account Inventory] AS T3
INNER JOIN [Site Inventory] AS T4 ON T3.SiteIDNumber = T4.SiteIDNumber) ON
T1.[Account ID (to)] = T3.AccountID)
WHERE [PolicyNumber] NOT IN (SELECT DISTINCT [PolicyNumber]
FROM [Pending Transactions])
UNION
SELECT T2.[PolicyNumber], Null, Null, Null, Null, Null, Null, Null, Null,
Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, T2.[ID],
T2.[Requestor], T2.[Request Type], T2.[PolicyNumber], T2.[AccountID],
T2.[Date Redemption Submitted], T2.[Requested Effective Date], T2.[Full or
Partial], T2.[Partial Amount], T2.[Date Expected], T2.[% or $ Expected],
T2.[Date Residual Expected], T2.[Residual % or $ Expected], T2.[Comment]
FROM [Pending Transactions] AS T2
WHERE [PolicyNumber] NOT IN (SELECT DISTINCT [PolicyNumber]
FROM [Money Market Reserve]);

I've got four tables in here.
T1 = [Money Market Reserve]
T2 = [Pending Transactions]
T3 = [Account Inventory]
T4 = [Site Inventory]

It is the "middle" one (between the two UNIONs) that is causing the
difficulty. I get an error message of "The specified filed '[PolicyNumber]'
could refer to more than one table used in the FROM clause of your SQL
statement."

If I copy just the portion above the first UNION into the SQL statement it
runs perfectly. If I copy just the bottom portion below the second UNION
into the SQL statement it runs perfectly.

I can't see straight anymore from looking at this. A second set of eyes
looking at this would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top