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
|
|||
|
|||
SQL help needed please
greetings gurus
I am having trouble with the syntax of a query I'm trying to write. Here is the situation, simpified to its essence: C:/DB/A.mdb has tblNames with relevant fields CustID, CustName, CustEMail. C:/DB/B.mdb has the same table and fields. C:/DB/C.mdb also has the same table and fields. Many of the names appear in 2 or 3 of the databases, but most names appear in only one of the three databases. Here's what I am trying to do (so far without success)... I want the values of CustID, CustName, CustEMail that are in A.mdb UNION the values of CustID, CustName, CustEMail that are in B.mdb. Then I need to remove any doubles (same PK CustID) from the resulting recordset. Finally, I need to EXCLUDE any of these remaining records that ALSO appear in C.mdb. I would be SO greatful for help. - cinnie |
#2
|
|||
|
|||
SQL help needed please
On Fri, 12 Feb 2010 15:49:02 -0800, cinnie
wrote: You should link the tables from B and C to your A database. Give them different names e.g. tblNamesB, tblNamesC. Now the union query is trivial, and it will automatically exclude duplicate records. However, your requirement is to exclude duplicate PKs, which is a subtly different question. How about: select * from tblNames union select * from tblNamesB where tblNamesB.CustID not in (select CustID from tblNames) I think you can figure out the rest. -Tom. Microsoft Access MVP greetings gurus I am having trouble with the syntax of a query I'm trying to write. Here is the situation, simpified to its essence: C:/DB/A.mdb has tblNames with relevant fields CustID, CustName, CustEMail. C:/DB/B.mdb has the same table and fields. C:/DB/C.mdb also has the same table and fields. Many of the names appear in 2 or 3 of the databases, but most names appear in only one of the three databases. Here's what I am trying to do (so far without success)... I want the values of CustID, CustName, CustEMail that are in A.mdb UNION the values of CustID, CustName, CustEMail that are in B.mdb. Then I need to remove any doubles (same PK CustID) from the resulting recordset. Finally, I need to EXCLUDE any of these remaining records that ALSO appear in C.mdb. I would be SO greatful for help. - cinnie |
#3
|
|||
|
|||
SQL help needed please
If you have links in the A.mdb to the relevant table in B.Mdb and C.mdb then
the following should work to give you a unique list of CustID, CustName, and CustEmail that are in either A or B and not in C. SELECT A.CustID, A.CustName, A.CustEmail FROM TableA LEFT JOIN TableC ON TableA.CustID = TableC.CustID WHERE TableC.CustID is Not Null UNION SELECT B.CustID, B.CustName, B.CustEmail FROM TableB LEFT JOIN TableC ON TableB.CustID = TableC.CustID WHERE TableC.CustID is Not Null If you want just one record per CustID and there are possible duplicates records because CustName or CustEmail differs then you can use the UNION query as the source for another query where you aggregate the data SELECT CustID, First(CustName), First(CustEmail) FROM qUnionQuery GROUP BY CustID IF you want the latter, you might change UNION to UNION ALL and see if that returns the results faster or slower. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County cinnie wrote: greetings gurus I am having trouble with the syntax of a query I'm trying to write. Here is the situation, simpified to its essence: C:/DB/A.mdb has tblNames with relevant fields CustID, CustName, CustEMail. C:/DB/B.mdb has the same table and fields. C:/DB/C.mdb also has the same table and fields. Many of the names appear in 2 or 3 of the databases, but most names appear in only one of the three databases. Here's what I am trying to do (so far without success)... I want the values of CustID, CustName, CustEMail that are in A.mdb UNION the values of CustID, CustName, CustEMail that are in B.mdb. Then I need to remove any doubles (same PK CustID) from the resulting recordset. Finally, I need to EXCLUDE any of these remaining records that ALSO appear in C.mdb. I would be SO greatful for help. - cinnie |
#4
|
|||
|
|||
SQL help needed please
That does it! Thanks.
-- cinnie "John Spencer" wrote: If you have links in the A.mdb to the relevant table in B.Mdb and C.mdb then the following should work to give you a unique list of CustID, CustName, and CustEmail that are in either A or B and not in C. SELECT A.CustID, A.CustName, A.CustEmail FROM TableA LEFT JOIN TableC ON TableA.CustID = TableC.CustID WHERE TableC.CustID is Not Null UNION SELECT B.CustID, B.CustName, B.CustEmail FROM TableB LEFT JOIN TableC ON TableB.CustID = TableC.CustID WHERE TableC.CustID is Not Null If you want just one record per CustID and there are possible duplicates records because CustName or CustEmail differs then you can use the UNION query as the source for another query where you aggregate the data SELECT CustID, First(CustName), First(CustEmail) FROM qUnionQuery GROUP BY CustID IF you want the latter, you might change UNION to UNION ALL and see if that returns the results faster or slower. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County cinnie wrote: greetings gurus I am having trouble with the syntax of a query I'm trying to write. Here is the situation, simpified to its essence: C:/DB/A.mdb has tblNames with relevant fields CustID, CustName, CustEMail. C:/DB/B.mdb has the same table and fields. C:/DB/C.mdb also has the same table and fields. Many of the names appear in 2 or 3 of the databases, but most names appear in only one of the three databases. Here's what I am trying to do (so far without success)... I want the values of CustID, CustName, CustEMail that are in A.mdb UNION the values of CustID, CustName, CustEMail that are in B.mdb. Then I need to remove any doubles (same PK CustID) from the resulting recordset. Finally, I need to EXCLUDE any of these remaining records that ALSO appear in C.mdb. I would be SO greatful for help. - cinnie . |
#5
|
|||
|
|||
SQL help needed please
In both cases, I changed "WHERE TableC.CustID is Not Null"
to "WHERE TableC.CustID is Null". -- cinnie "John Spencer" wrote: If you have links in the A.mdb to the relevant table in B.Mdb and C.mdb then the following should work to give you a unique list of CustID, CustName, and CustEmail that are in either A or B and not in C. SELECT A.CustID, A.CustName, A.CustEmail FROM TableA LEFT JOIN TableC ON TableA.CustID = TableC.CustID WHERE TableC.CustID is Not Null UNION SELECT B.CustID, B.CustName, B.CustEmail FROM TableB LEFT JOIN TableC ON TableB.CustID = TableC.CustID WHERE TableC.CustID is Not Null If you want just one record per CustID and there are possible duplicates records because CustName or CustEmail differs then you can use the UNION query as the source for another query where you aggregate the data SELECT CustID, First(CustName), First(CustEmail) FROM qUnionQuery GROUP BY CustID IF you want the latter, you might change UNION to UNION ALL and see if that returns the results faster or slower. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County cinnie wrote: greetings gurus I am having trouble with the syntax of a query I'm trying to write. Here is the situation, simpified to its essence: C:/DB/A.mdb has tblNames with relevant fields CustID, CustName, CustEMail. C:/DB/B.mdb has the same table and fields. C:/DB/C.mdb also has the same table and fields. Many of the names appear in 2 or 3 of the databases, but most names appear in only one of the three databases. Here's what I am trying to do (so far without success)... I want the values of CustID, CustName, CustEMail that are in A.mdb UNION the values of CustID, CustName, CustEMail that are in B.mdb. Then I need to remove any doubles (same PK CustID) from the resulting recordset. Finally, I need to EXCLUDE any of these remaining records that ALSO appear in C.mdb. I would be SO greatful for help. - cinnie . |
#6
|
|||
|
|||
SQL help needed please
Of course. My error.
John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County cinnie wrote: In both cases, I changed "WHERE TableC.CustID is Not Null" to "WHERE TableC.CustID is Null". |
Thread Tools | |
Display Modes | |
|
|