RTINSC Posted January 26, 2006 Share Posted January 26, 2006 I am working with a database of 100,000+ property records. The file contains numerous fields of data and each record is anchored by the parcel number. Previous grantees and grantors are in the file so when I sort the file by parcel number, I get anywhere from 2 to 6 of the same parcel numbers, each with current and previous owner name. Each record also has a date field with the dates of previous transactions. I want to be able to do a "find" with this file that will give me only the records with the most current date (current owner) for each parcel. Looks like this: 105-20-33-090 Smith John 02/09/1984 105-20-33-090 Davis Sandra 11/10/1989 105-20-33-090 Stuck Ima 02/23/2004 109-45-00-101 Jones James 10/12/1978 109-45-00-101 Rogers Matty 04/21/2002 The most recent records (underlined) are the ones I want to export to another file.. Any suggestions will be appreciated. RT Link to comment Share on other sites More sharing options...
Lee Smith Posted January 26, 2006 Share Posted January 26, 2006 Why don't you just do a find for Duplicates, and then sort? Lee Link to comment Share on other sites More sharing options...
RTINSC Posted January 26, 2006 Author Share Posted January 26, 2006 Thanks for your help. I have tried that with my text editor. It removes exact duplicates when all fields are identical. In this case the parcel number is the same for several records and names and dates are different. I need a way to find the records with the most current date from those with same oarcel numbers. Link to comment Share on other sites More sharing options...
Lee Smith Posted January 26, 2006 Share Posted January 26, 2006 Okay, I think I'm confused as to what you are trying to say. Is this Data, 105-20-33-090 Smith John 02/09/1984 in three fields, or all in one? If different fields, then just do a [color:blue]Find in the field containing this part 105-20-33-090 using the exclamation point [color:blue]! in that field. This will pull all of the duplicates out of the mix. Then do a sort my that same field, and it should put the duplicates together. If this data is all in one field, then you will need to parse it out. Lee Link to comment Share on other sites More sharing options...
RTINSC Posted January 26, 2006 Author Share Posted January 26, 2006 Hi Lee.. Parcel numbers, names and dates are in separate fields in each record. The only common field is the parcel number of which there may be anywhere from 2 to 6 records with the same parcel number. I want to retrieve a list of records that containing only those records with the most current date. Using ! gives me a file of over 40000 dupe records. I'm thinking the date field has to come into play somehow, giving me only the record with the most current date of the records with the same parcel number. A list view looks like this: 105-20-33-090, Smith John, 02/09/1984 105-20-33-090, Davis Sandra, 11/10/1989 105-20-33-090, Stuck Ima, 02/23/2004 109-45-00-101, Jones James, 10/12/1978 109-45-00-101, Rogers Matty, 04/21/2002 Your help is appreciated. Roger Link to comment Share on other sites More sharing options...
Lee Smith Posted January 26, 2006 Share Posted January 26, 2006 Okay, Yes the date field would be of help to you, first be sure that it is formatted as a DATE. If so, then do a search using the ! and 1/1/2006...1/31/2006 as an example for the date range of your choice. Then sort by the parcel number Lee Link to comment Share on other sites More sharing options...
RTINSC Posted January 26, 2006 Author Share Posted January 26, 2006 Hi again.. Another way to look at this is: Where Field 1 has duplicate numbers, "find" only the records that have the most current date based on the date in Field 3. Thanks, Roger Link to comment Share on other sites More sharing options...
RTINSC Posted January 26, 2006 Author Share Posted January 26, 2006 Hi.. With respect to the date, I can't set a definite period as the dates are unknown in each record. The records may show several dates anywhere from this month to 10 years ago. Roger Link to comment Share on other sites More sharing options...
Lee Smith Posted January 26, 2006 Share Posted January 26, 2006 Then set up a self relationship based on the number, sort by the date, and view by portal. Lee Link to comment Share on other sites More sharing options...
RTINSC Posted January 26, 2006 Author Share Posted January 26, 2006 Hi Lee., My objective is to export only the records with the most current date (of the records that have the same parcel number). There may not be a way to do this other than going through the 40000+ dupe records and manually removing the records with previous dates, leaving the ones with most current date. This is very frustrating and may not have an FM solution. Thanks, Roger Link to comment Share on other sites More sharing options...
Lee Smith Posted January 26, 2006 Share Posted January 26, 2006 What you need is becoming a moving target. Link to comment Share on other sites More sharing options...
RTINSC Posted January 26, 2006 Author Share Posted January 26, 2006 From my first post: "The most recent records (underlined) are the ones I want to export to another file.. Any suggestions will be appreciated. RT" Hi Lee.. Sorry if I have difficulty explaining this problem. Identifying the dupes is no problem. I am looking for a way to select only the records with the most recent date from the records that have the same parcel numbers so I can export only those records to another file. Your help is appreciated. Roger Link to comment Share on other sites More sharing options...
RTINSC Posted January 26, 2006 Author Share Posted January 26, 2006 To better illustrate, this is how file appears in Excel or FM table. These entries are in separate fields, Parcel #, Name, Date. I want to identify and export only the duped records with the most recent date. Thanks for any help. Roger 001-16-01-004 GUSTAV SCHICKEDANZ TRUSTEE 4/5/2005 001-16-01-004 DANNY L GREENE & JULIE S GREENE 1/1/2003 001-16-01-002 CHASE MANHATTAN MTG CORP 9/1/2005 001-16-01-002 CLARENCE HARRIS & JOHN E GAMBRELL 12/4/1997 001-15-01-007 ANGELINA L RUTLAND & LARRY GRANT NEWMAN 9/1/2005 001-15-01-004 JENNIFER L RUVO 12/3/2003 001-15-01-004 OLIVER S BURRELL & TINA M BURRELL 6/19/2003 001-15-01-004 ALFRED C WARD 5/4/1998 001-15-01-004 MARY FAYE BUTLER & WILLIAM P BUTLER 11/30/1995 Link to comment Share on other sites More sharing options...
comment Posted January 26, 2006 Share Posted January 26, 2006 Define a self-join relationship (to the same file), named "SameParcel", based on: ParcelNumber = ParcelNumber Sort the related records by Date, descending. Define a calculation field (result is number) = SerialID = SameParcel::SerialID (Note: you must have a serial number field in the file - if you don't, create one and populate it by finding all records and replacing the field's contents.) Go to Find mode and search for "1" (without the quotes) in the new calculation field. Link to comment Share on other sites More sharing options...
RTINSC Posted January 26, 2006 Author Share Posted January 26, 2006 Hi...Thanks I have created and populated a field (SerialID) with serial numbers and set relationship. Created a field "SameParcel" and sorted the file by date descending. In calculation box I set SerialID =SameParcel::SerialID When I click OK, I get "Field Not found." I can see that this will lead to a solution. Link to comment Share on other sites More sharing options...
Lee Smith Posted January 26, 2006 Share Posted January 26, 2006 Hi, I was in the process of making a reply when comment solved your problem. I have made a small demo of the file, perhaps you will have some use for it. [color:blue] BTW, ignore the portal as it has not value. Lee FindRecentParcel.fp7.zip Link to comment Share on other sites More sharing options...
RTINSC Posted January 26, 2006 Author Share Posted January 26, 2006 Thanks Lee..Unfortunately, I get "Need Version 7.0 to view". I have V.6 Nothing is simple, is it? Roger Link to comment Share on other sites More sharing options...
Lee Smith Posted January 26, 2006 Share Posted January 26, 2006 hold on, I'll change it for you Link to comment Share on other sites More sharing options...
Lee Smith Posted January 26, 2006 Share Posted January 26, 2006 (edited) here you go [color:red]oops In my haste to change this to v5, I forgot the sort in the Relationship. Open the Relationship box, and Edit the Sort Records by Date Ascending, as comment had instructed. Lee FindRecentParcel.fp5.zip Edited January 26, 2006 by Guest Link to comment Share on other sites More sharing options...
RTINSC Posted January 26, 2006 Author Share Posted January 26, 2006 Lee.. When I open the file, I see the data but the Relationship/Define menu items are greyed out.. Roger Link to comment Share on other sites More sharing options...
Lee Smith Posted January 26, 2006 Share Posted January 26, 2006 There are no passwords attached to this file. You might have to Get Info at the finder level and change the access from read only to read and write. Since I'm on a Mac, I don't know the process. Lee Link to comment Share on other sites More sharing options...
comment Posted January 26, 2006 Share Posted January 26, 2006 (edited) 1. Make sure the sorting is defined in the relationship itself. There's no need to sort the file. 2. If you want the latest record, sort the relationship by Date, descending, so that the latest record is the first related record. 3. In the calculation definition, clear all, then double click your SerialID field name, type the equal sign, switch the popup to related fields from "SameParcel" and double click the SerialID again. This should give you: SerialID = SameParcel::SerialID but the field/relationship names have to be as they are in your file. EDIT: With that many records, expect the find to take a while. There are faster methods, but they are too complex to go into now. If you need to repeat the process periodically, it's something to think about. Edited January 26, 2006 by Guest Link to comment Share on other sites More sharing options...
RTINSC Posted January 26, 2006 Author Share Posted January 26, 2006 Can't open it. However, I see the way to solution. In your file, I set up a field "SerialID" and "CComment". I populate SerialID with numbers ad sort by descending date. I set relationship "Same Parcel" to "Same Parcel". Can you take me through Field set up for SerialID and "CComment". Calc as numbers? What should calc look like? Do I need to set up any additional field? Thanks - Cup of Peets Java on the way to you..via email.. Link to comment Share on other sites More sharing options...
RTINSC Posted January 26, 2006 Author Share Posted January 26, 2006 Thanks..You and Lee have my thanks. A cup of Peets Java will be on the way to you if you can send me an email address :. Thanks Roger T Link to comment Share on other sites More sharing options...
RTINSC Posted January 26, 2006 Author Share Posted January 26, 2006 I'm not quite there. I have set up relationship Same Parcel Same Parcel. Created fields SerialID and SameParcel. Have populated SerialID with numbers. Set SamePaecel field as calc with number. Double clicked Serial ID (Main relationship), entered = sign, switched to SameParcelSameParcel relationship and double clicked :SerialID, Result is SerialID=SameParcel::SerialID I click OK and get "-circular relationship error." Are these only fields I need to set? Thanks... Link to comment Share on other sites More sharing options...
RTINSC Posted January 26, 2006 Author Share Posted January 26, 2006 Hi Comment.. I just can't seem to make this work. I am unable to see serial numbers when I set a field to Autonumber. Is this right? Or am I missing something? I see your solution will solve the problem, but I am simply missing something. Link to comment Share on other sites More sharing options...
Lee Smith Posted January 27, 2006 Share Posted January 27, 2006 You need to "Replace" the field value in [color:red]SerialID, by First placing your cursor in the SerialID field. Next, go to the Main Menu, Select the [color:blue] Records >> Replace Contents you will then get a Dialog Box, choose the [color:blue] Replace with Serial Numbers and then click [color:red]Replace HTH Lee Link to comment Share on other sites More sharing options...
comment Posted January 27, 2006 Share Posted January 27, 2006 (edited) Ok, from the top, step by step: 1. Define a new Number field named SerialID. In Options, click auto-enter serial number. Goto Layout mode and put SerialID on the body of the layout. Go to Browse mode, click into SerialID, and select Replace.. from the menu (I'm not sure where the command is in ver.6). Choose replace with serial numbers, starting at 1, update serial number in Entry Options. 2. Define a new relationship (to the same file) named "SameParcel", based on: ParcelNumber = ParcelNumber In the relationship definition, click "Sort the related records" and select by Date, descending. 3. Define a new Calculation field named cUnique. In Options, paste this: SerialID = SameParcel::SerialID Select a result of Number for the calculation. 4. Goto Layout mode and put cUnique on the body of the layout. Goto Find mode, enter 1 into cUnique and wait for the result. Edited January 27, 2006 by Guest Link to comment Share on other sites More sharing options...
RTINSC Posted January 27, 2006 Author Share Posted January 27, 2006 It helped a lot when I finaly figured out that I wasn't seeing the serial numbers! You and Comment deserve Bronze Stars, at the very least. Thanks. I have it figured out. Roger T Enjoy the Java.. Link to comment Share on other sites More sharing options...
RTINSC Posted January 27, 2006 Author Share Posted January 27, 2006 It helped a lot when I finaly figured out that I wasn't seeing the serial numbers! You and Lee have solved a "tough" problem..I owe ya! Thanks, I appreciate you taking the time to help me. Email me for that Java! Roger T Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6665 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now