OK, let's find some more constituent referenses. First run "select * from information_schema.columns where column_name = 'constituentid' ", that will show all tables that has a column named "constituentid" in it. We can then build a union (excluding some tables like constituents....) in our "not in" criteria. So, run this: select distinct constituentid, displayname from constituents where constituentid not in ( select distinct constituentid from ExhVenuesXrefs union select distinct constituentid from RefConXrefs union select distinct constituentid from Makers union select distinct constituentid from LoanConXrefs union select distinct constituentid from HistConXrefs union select distinct constituentid from ExhConXrefs union select distinct constituentid from ExColConXrefs union select distinct constituentid from ConAddrShipping union select distinct constituentid from ConAddrMailing union select distinct constituentid from ConDates union select distinct constituentid from ConAttributeXrefs union select distinct constituentid from ObjConXrefs union select distinct constituentid from Loans union select distinct constituentid from AcqConXrefs union select distinct constituentid from ConXrefs union select distinct constituentid from TransDistributions union select distinct constituentid from TransPurchase union select distinct constituentid from Transactions union select distinct constituentid from ObjArtist union select distinct constituentid from SiteConXrefs union select distinct constituentid from ShipConXrefs ) order by constituentid Now you have narrowed it down a bit. Always check in TMS before deleting anything. I have checked some of constituents in my result and they did not have any hits i the relations meny in TMS. But why delete them? Jonas. Jonas Karlsson Moderna Museet Stockholm Sweden [log in to unmask] [Jonas Karlsson] -----Ursprungligt meddelande----- Från: Brian Jennings [mailto:[log in to unmask]] Skickat: den 27 november 2002 22:30 Till: Jonas Karlsson Ämne: RE: searching for unlinked constituents Hi Jonas. I saw other replies that Ella got directly, and they all were similar you your clear step by step one (i.e. use Crystal and run a select query), and most mentioned that the example was incomplete. The best approach is a union query, of course. One area that everyone forgot (me included the first time I looked at the problem): Venues! That's a biggie. BJ -----Original Message----- From: Jonas Karlsson [mailto:[log in to unmask]] Sent: Wednesday, November 27, 2002 11:24 AM To: [log in to unmask] Subject: SV: searching for unlinked constituents Hello Ella and all TMSUSERS ! I'm not sure this will help you but it might do a interesting job for you, in SQL. It is not possible to execute it from within TMS but use MS SQL Query Analyser if you have it or any other similar tool, like the one in Crystal Reports. (if installed on windows it's : start>>program>> CrystalReportsTools>>CrystalSQLDesigner, click the blank with paper and then choose your linking info (ODBC - TMS?)and then choose to "enter sql statement directly") Run this query: "select distinct constituentid, displayname from constituents where constituentid not in (select distinct constituentid from conxrefs) order by constituentid". They can be linked to something elsewhere in TMS but this might point you in the right direction. The following query will give you the number of objects related to each constituent: "SELECT constituentid, count(id) number_of_obj FROM conxrefs group by constituentid". I hope this was helpful for you. Jonas. Moderna Museet, Stockholm, Sweden. [log in to unmask] -----Ursprungligt meddelande----- Från: Ella Rothgangel [mailto:[log in to unmask]] Skickat: den 26 november 2002 14:30 Till: [log in to unmask] Ämne: searching for unlinked constituents I'm trying to figure out a way to search for unlinked constituents (i.e. constituents who are not attached to anything anywhere and need to be deleted). Has anyone tried this or been successful, either with an Advanced Query or a Crystal Report? Thanks, Ella Rothgangel Collections Database Administrator Saint Louis Art Museum [log in to unmask]