UNION Query with Criteria

D

Dkline

Can you have a criteria in a UNION query? If so how it is done?

I've been able to get the original query working from VBA and it works
perfectly thanks to the assistance of this newsgroup.

Working query:

Function UNIONQuery()
strSQL = "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 Inv
entory] AS T3 INNER JOIN [Site Inventory] AS T4 ON T3.[SiteIDNumber] =
T4.[SiteIDNumber]) ON T1.[Account ID (to)] = T3.[AccountID]) " & _
"WHERE T1.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]);"
End Function


Modifed Working Query

Assume a string variable "strPN" to hold the [PolicyNumber] criteria e.g.
"PNVLA1234"

Now I need it to allow for a specifed [PolicyNumber]. The best I've been
able to do is not have it blow me out of the water.

Function UNIONQueryCase(PN As String)
strSQL = "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] = '" & PN & "') =
(T2.[PolicyNumber] = '" & PN & "'));"

End Function

I've chopped off the balance of the original figuring if I can get the
syntax right on the first piece of the puzzle I can adopt it to the rest.
 
D

Dkline

Got it working. Just needed to step outside to figure it out.

Dkline said:
Can you have a criteria in a UNION query? If so how it is done?

I've been able to get the original query working from VBA and it works
perfectly thanks to the assistance of this newsgroup.

Working query:

Function UNIONQuery()
strSQL = "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 Inv
entory] AS T3 INNER JOIN [Site Inventory] AS T4 ON T3.[SiteIDNumber] =
T4.[SiteIDNumber]) ON T1.[Account ID (to)] = T3.[AccountID]) " & _
"WHERE T1.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]);"
End Function


Modifed Working Query

Assume a string variable "strPN" to hold the [PolicyNumber] criteria e.g.
"PNVLA1234"

Now I need it to allow for a specifed [PolicyNumber]. The best I've been
able to do is not have it blow me out of the water.

Function UNIONQueryCase(PN As String)
strSQL = "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] = '" & PN & "') =
(T2.[PolicyNumber] = '" & PN & "'));"

End Function

I've chopped off the balance of the original figuring if I can get the
syntax right on the first piece of the puzzle I can adopt it to the rest.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top