"UNION" Query with different fields in the two tables

D

Dkline

I have two tables. The tables have different fields and number of fields.
They each have one field in common "PolicyNumber".

What we want to do is to have a query which will pull the information into a
query from the other two tables - joining them into one. I assume that this
query must have ALL of the fields between the two different table designs.
The sole exception would be the PolicyNumber being the identical field
between the two tables.

A UNION query would be perfect except it won't work here since the tables
have the different fields and number of fields

Fields from first table:
[Money Market Reserve].PolicyNumber, [Money Market Reserve].[Net Investment
Amount], [Money Market Reserve].[Anticipated Investment Fund Name], [Money
Market Reserve].[Contact Fund?], [Money Market Reserve].[Confirm to
Accounting?], [Money Market Reserve].[AccountID (from)], [Money Market
Reserve].[Account ID (to)], [Money Market Reserve].[Sub Doc], [Money Market
Reserve].[Cover Letter], [Money Market Reserve].[Trade Date per Info Sheet
(for new premium only)], [Money Market Reserve].[Effective Date], [Money
Market Reserve].[Wire Date], [Money Market Reserve].[Sub Doc Date], [Money
Market Reserve].[Asset Allocation Instructions], [Money Market
Reserve].[Client Allocation Instructions], [Money Market Reserve].[Wire
Instructions], [Money Market Reserve].Status, [Money Market
Reserve].Complete

Fields from second table:
[Pending Transactions].ID, [Pending Transactions].Requestor, [Pending
Transactions].[Request Type], [Pending Transactions].PolicyNumber, [Pending
Transactions].AccountID, [Pending Transactions].[Date Redemption Submitted],
[Pending Transactions].[Requested Effective Date], [Pending
Transactions].[Full or Partial], [Pending Transactions].[Partial Amount],
[Pending Transactions].[Date Expected], [Pending Transactions].[% or $
Expected], [Pending Transactions].[Date Residual Expected], [Pending
Transactions].[Residual % or $ Expected], [Pending Transactions].Comment

Again the only common field is PolicyNumber:
[Money Market Reserve].PolicyNumber
[Pending Transactions].PolicyNumber


Should I do an Append query and suck both of them into the query which has
all their fields?
 
G

Gerald Stanley

Assuming that the policyNumber can be both or either
tables, you could use a UNION query along the lines

SELECT T1.column1, T1.column2 etc, T2.columnA, T2.columnB etc
FROM [Money Market Reserve] AS T1 INNER JOIN [Pending
Transactions] AS T2 ON T1.policyNumber = T2.policyNumber
UNION
SELECT T1.column1, T1.column2 etc, Null, Null, etc
FROM [Money Market Reserve] AS T1
WHERE policyNumber NOT IN (SELECT DISTINCT policyNumber
FROM [Pending Transactions])
UNION
SELECT Null, Null, etc, T2.columnA, T2.columnB etc
FROM [Pending Transactions] AS T2
WHERE policyNumber NOT IN (SELECT DISTINCT policyNumber
FROM [Money Market Reserve])

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have two tables. The tables have different fields and number of fields.
They each have one field in common "PolicyNumber".

What we want to do is to have a query which will pull the information into a
query from the other two tables - joining them into one. I assume that this
query must have ALL of the fields between the two different table designs.
The sole exception would be the PolicyNumber being the identical field
between the two tables.

A UNION query would be perfect except it won't work here since the tables
have the different fields and number of fields

Fields from first table:
[Money Market Reserve].PolicyNumber, [Money Market Reserve].[Net Investment
Amount], [Money Market Reserve].[Anticipated Investment Fund Name], [Money
Market Reserve].[Contact Fund?], [Money Market Reserve].[Confirm to
Accounting?], [Money Market Reserve].[AccountID (from)], [Money Market
Reserve].[Account ID (to)], [Money Market Reserve].[Sub Doc], [Money Market
Reserve].[Cover Letter], [Money Market Reserve].[Trade Date per Info Sheet
(for new premium only)], [Money Market Reserve].[Effective Date], [Money
Market Reserve].[Wire Date], [Money Market Reserve].[Sub Doc Date], [Money
Market Reserve].[Asset Allocation Instructions], [Money Market
Reserve].[Client Allocation Instructions], [Money Market Reserve].[Wire
Instructions], [Money Market Reserve].Status, [Money Market
Reserve].Complete

Fields from second table:
[Pending Transactions].ID, [Pending
Click to expand...
Transactions].Requestor, [Pending
Transactions].[Request Type], [Pending
Click to expand...
Transactions].PolicyNumber, [Pending
Transactions].AccountID, [Pending Transactions].[Date Redemption Submitted],
[Pending Transactions].[Requested Effective Date], [Pending
Transactions].[Full or Partial], [Pending
Click to expand...
Transactions].[Partial Amount],
[Pending Transactions].[Date Expected], [Pending Transactions].[% or $
Expected], [Pending Transactions].[Date Residual Expected], [Pending
Transactions].[Residual % or $ Expected], [Pending Transactions].Comment

Again the only common field is PolicyNumber:
[Money Market Reserve].PolicyNumber
[Pending Transactions].PolicyNumber


Should I do an Append query and suck both of them into the query which has
all their fields?


.
Click to expand...
 
G

Guest

I can't figure out what you're trying to do. Unioning two completely different recordsets doesn't make any sense. It's like trying to append your address book to the end of your checkbook register. They aren't the same data and so don't belong in the same list.

Are you sure that you don't want to join the two tables? Perhaps you need a full outer join and that is why you are looking at a union. To do a full outer join:
1. Create a left join query
2. Create a right join query
3. Union the two queries.
--
Bridge Players know all the Tricks


Dkline said:
I have two tables. The tables have different fields and number of fields.
They each have one field in common "PolicyNumber".

What we want to do is to have a query which will pull the information into a
query from the other two tables - joining them into one. I assume that this
query must have ALL of the fields between the two different table designs.
The sole exception would be the PolicyNumber being the identical field
between the two tables.

A UNION query would be perfect except it won't work here since the tables
have the different fields and number of fields

Fields from first table:
[Money Market Reserve].PolicyNumber, [Money Market Reserve].[Net Investment
Amount], [Money Market Reserve].[Anticipated Investment Fund Name], [Money
Market Reserve].[Contact Fund?], [Money Market Reserve].[Confirm to
Accounting?], [Money Market Reserve].[AccountID (from)], [Money Market
Reserve].[Account ID (to)], [Money Market Reserve].[Sub Doc], [Money Market
Reserve].[Cover Letter], [Money Market Reserve].[Trade Date per Info Sheet
(for new premium only)], [Money Market Reserve].[Effective Date], [Money
Market Reserve].[Wire Date], [Money Market Reserve].[Sub Doc Date], [Money
Market Reserve].[Asset Allocation Instructions], [Money Market
Reserve].[Client Allocation Instructions], [Money Market Reserve].[Wire
Instructions], [Money Market Reserve].Status, [Money Market
Reserve].Complete

Fields from second table:
[Pending Transactions].ID, [Pending Transactions].Requestor, [Pending
Transactions].[Request Type], [Pending Transactions].PolicyNumber, [Pending
Transactions].AccountID, [Pending Transactions].[Date Redemption Submitted],
[Pending Transactions].[Requested Effective Date], [Pending
Transactions].[Full or Partial], [Pending Transactions].[Partial Amount],
[Pending Transactions].[Date Expected], [Pending Transactions].[% or $
Expected], [Pending Transactions].[Date Residual Expected], [Pending
Transactions].[Residual % or $ Expected], [Pending Transactions].Comment

Again the only common field is PolicyNumber:
[Money Market Reserve].PolicyNumber
[Pending Transactions].PolicyNumber


Should I do an Append query and suck both of them into the query which has
all their fields?
Click to expand...
 
D

Dkline

Thank you. Your solution was brilliant.

For the record the SQL ended up being:

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], 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 [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], Null, Null, Null, Null, Null, Null, Null, Null,
Null, Null, Null, Null, Null, Null
FROM [Money Market Reserve] AS T1
WHERE [PolicyNumber] NOT IN (SELECT DISTINCT [PolicyNumber]
FROM [Pending Transactions])
UNION
SELECT 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])


Thanks again.


Gerald Stanley said:
Assuming that the policyNumber can be both or either
tables, you could use a UNION query along the lines

SELECT T1.column1, T1.column2 etc, T2.columnA, T2.columnB etc
FROM [Money Market Reserve] AS T1 INNER JOIN [Pending
Transactions] AS T2 ON T1.policyNumber = T2.policyNumber
UNION
SELECT T1.column1, T1.column2 etc, Null, Null, etc
FROM [Money Market Reserve] AS T1
WHERE policyNumber NOT IN (SELECT DISTINCT policyNumber
FROM [Pending Transactions])
UNION
SELECT Null, Null, etc, T2.columnA, T2.columnB etc
FROM [Pending Transactions] AS T2
WHERE policyNumber NOT IN (SELECT DISTINCT policyNumber
FROM [Money Market Reserve])

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have two tables. The tables have different fields and number of fields.
They each have one field in common "PolicyNumber".

What we want to do is to have a query which will pull the information into a
query from the other two tables - joining them into one. I assume that this
query must have ALL of the fields between the two different table designs.
The sole exception would be the PolicyNumber being the identical field
between the two tables.

A UNION query would be perfect except it won't work here since the tables
have the different fields and number of fields

Fields from first table:
[Money Market Reserve].PolicyNumber, [Money Market Reserve].[Net Investment
Amount], [Money Market Reserve].[Anticipated Investment Fund Name], [Money
Market Reserve].[Contact Fund?], [Money Market Reserve].[Confirm to
Accounting?], [Money Market Reserve].[AccountID (from)], [Money Market
Reserve].[Account ID (to)], [Money Market Reserve].[Sub Doc], [Money Market
Reserve].[Cover Letter], [Money Market Reserve].[Trade Date per Info Sheet
(for new premium only)], [Money Market Reserve].[Effective Date], [Money
Market Reserve].[Wire Date], [Money Market Reserve].[Sub Doc Date], [Money
Market Reserve].[Asset Allocation Instructions], [Money Market
Reserve].[Client Allocation Instructions], [Money Market Reserve].[Wire
Instructions], [Money Market Reserve].Status, [Money Market
Reserve].Complete

Fields from second table:
[Pending Transactions].ID, [Pending
Click to expand...
Transactions].Requestor, [Pending
Transactions].[Request Type], [Pending
Click to expand...
Transactions].PolicyNumber, [Pending
Transactions].AccountID, [Pending Transactions].[Date Redemption Submitted],
[Pending Transactions].[Requested Effective Date], [Pending
Transactions].[Full or Partial], [Pending
Click to expand...
Transactions].[Partial Amount],
[Pending Transactions].[Date Expected], [Pending Transactions].[% or $
Expected], [Pending Transactions].[Date Residual Expected], [Pending
Transactions].[Residual % or $ Expected], [Pending Transactions].Comment

Again the only common field is PolicyNumber:
[Money Market Reserve].PolicyNumber
[Pending Transactions].PolicyNumber


Should I do an Append query and suck both of them into the query which has
all their fields?


.
Click to expand...
Click to expand...
 

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

Similar Threads

UNION Query with Criteria 1
Need someone to look at SQL statement 4
Retrieve date from different tables 2
Joined two tables 2
Filter Dates in a union query 2
How can point me out the best way on a query 7
Query to show latest review only 4
sum query regardless of different transaction dates 3

Top