I am trying to Join a parent and child table to create One table in order to export to Excel. I realize it will only pick the first child to match - My issue is that I want a specific record and if that doesn't exist then the first record.
The parent table is A Land database, and the child is all the owners - which could be one or several. They are linked by rec_no, The owners may have a lease associated with them. I have a field in the Owner table called Lease_rec_no - If I do a filter and only pick those childs with Lease_rec_no>0, then if there is no lease, I don't get any of the Child info, although I still get the parent(which I want). What I want to do is pick either the first Owner with a Lease_rec_no>0 or if none of the owners have a lease_rec_no, pick one of owners(doesn't matter to me which one - just need one- could be first or last) After this join is done, I have a set that this table is then matched to (the lease info)Prior to my export so that I only end up with one record, some of which have lease info and some that dont, but all my land and at least one owner record is there. Unless someone can think of a better way to get all this info in my export.
Example: Table Fields Link
Property Rec_No Rec_No to one-many Owner
property Id
Owner Rec_No Rec_No to Property
Owner_rec
Lease_Rec_No Lease_Rec_no to Lease_Info
Names
Perc Owned
Lease_Info Lease_Rec_No Lease_Rec_no to one-Many Owner
Lease_No
Lease_Status
So there is ALWAYS only One Property Record, and One Lease_Info Record, but there could be Many Owners per piece of Property, or Many Owners per Lease(that could possible be different pieces of Property)
i.e.
Property Rec_No 1 Property ID 123
Rec_no 2 Property Id 456
Owner Rec_No 1 Owner_rec 1 Lease_rec_no Null()
Rec_no 1 Owner_rec 2 Lease_rec_no 1
Rec_no 1 Owner_rec 3 Lease_rec_no 2
Rec_No 2 Owner_rec 4 Lease_rec_no Null()
Currently my join would give me (As I did filter for Lease_rec_no >0)
New Table Rec_No 1 Property_ID 123 Owner_rec 2
Rec_No 2 Property_Id 456 No owner Rec
I need the info on Rec_No 2 from Owner Rec even though there is no Lease_rec_no.
This is run on the entire table, not just one Parent record.
I have looked at first(),last(),tablemax() and none of those seem to work.
Any suggestions??
The parent table is A Land database, and the child is all the owners - which could be one or several. They are linked by rec_no, The owners may have a lease associated with them. I have a field in the Owner table called Lease_rec_no - If I do a filter and only pick those childs with Lease_rec_no>0, then if there is no lease, I don't get any of the Child info, although I still get the parent(which I want). What I want to do is pick either the first Owner with a Lease_rec_no>0 or if none of the owners have a lease_rec_no, pick one of owners(doesn't matter to me which one - just need one- could be first or last) After this join is done, I have a set that this table is then matched to (the lease info)Prior to my export so that I only end up with one record, some of which have lease info and some that dont, but all my land and at least one owner record is there. Unless someone can think of a better way to get all this info in my export.
Example: Table Fields Link
Property Rec_No Rec_No to one-many Owner
property Id
Owner Rec_No Rec_No to Property
Owner_rec
Lease_Rec_No Lease_Rec_no to Lease_Info
Names
Perc Owned
Lease_Info Lease_Rec_No Lease_Rec_no to one-Many Owner
Lease_No
Lease_Status
So there is ALWAYS only One Property Record, and One Lease_Info Record, but there could be Many Owners per piece of Property, or Many Owners per Lease(that could possible be different pieces of Property)
i.e.
Property Rec_No 1 Property ID 123
Rec_no 2 Property Id 456
Owner Rec_No 1 Owner_rec 1 Lease_rec_no Null()
Rec_no 1 Owner_rec 2 Lease_rec_no 1
Rec_no 1 Owner_rec 3 Lease_rec_no 2
Rec_No 2 Owner_rec 4 Lease_rec_no Null()
Currently my join would give me (As I did filter for Lease_rec_no >0)
New Table Rec_No 1 Property_ID 123 Owner_rec 2
Rec_No 2 Property_Id 456 No owner Rec
I need the info on Rec_No 2 from Owner Rec even though there is no Lease_rec_no.
This is run on the entire table, not just one Parent record.
I have looked at first(),last(),tablemax() and none of those seem to work.
Any suggestions??
Comment