"UNION" Query with different fields in the two tables

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?

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
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?


.

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" wrote:

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?

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" wrote:

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?

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" wrote:

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?

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" - wrote in message
...
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
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?


.