If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"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? |
#2
|
|||
|
|||
"UNION" Query with different fields in the two tables
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? . |
#3
|
|||
|
|||
"UNION" Query with different fields in the two tables
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? |
#4
|
|||
|
|||
"UNION" Query with different fields in the two tables
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? |
#5
|
|||
|
|||
"UNION" Query with different fields in the two tables
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? |
#6
|
|||
|
|||
"UNION" Query with different fields in the two tables
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? . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
UNION query question | Dale Peart | Running & Setting Up Queries | 6 | July 14th, 2004 12:26 AM |
Union Query Issue | Larry | Running & Setting Up Queries | 4 | July 12th, 2004 08:53 AM |
Use a make-table query to combine two existing tables | Matt | Running & Setting Up Queries | 1 | June 16th, 2004 01:53 AM |
surely a form with a ListBox can be used in a query? | 1.156 | Running & Setting Up Queries | 14 | June 2nd, 2004 04:54 PM |
Need help with Tables Design and Relationships | Tom | Database Design | 24 | May 19th, 2004 06:51 PM |