I have a table (attached) containing well license numbers, shareholder name, update date, and shareholder % interest (from left to right).
I need a query that will display the current shareholders for each well license and what % each one holds.
Problem is, these shareholders change quite frequently and it appears that rather than old data being overwritten when a change occurs, a new record was simply added with a more recent update date.
The approach I have in mind was a two step query with some sort of chronological running total for each license number, and a second query that only picks the most recent records where the running total is less than or equal to 100. Can't seem to accomplish this though, as Access does not support the "partition by" SQL command....and am at a huge standstill with my job! #jrdeveloperproblems
Thanks so much for any insight.
Cheers,
K
Plog: This is roughly the data I would like returned:
License_Number Work_Int_Partpt_Legal_Name Update_Date_Time Work_Int_Partpt_Interest
0000001--------------------Company A---------------------3/29/2016-------------------100
0000003--------------------Company B---------------------3/29/2016-------------------100
0000006--------------------Company C---------------------11/3/2014-------------------15
0000006--------------------Company D---------------------11/3/2014-------------------85
and etc
I need a query that will display the current shareholders for each well license and what % each one holds.
Problem is, these shareholders change quite frequently and it appears that rather than old data being overwritten when a change occurs, a new record was simply added with a more recent update date.
The approach I have in mind was a two step query with some sort of chronological running total for each license number, and a second query that only picks the most recent records where the running total is less than or equal to 100. Can't seem to accomplish this though, as Access does not support the "partition by" SQL command....and am at a huge standstill with my job! #jrdeveloperproblems
Thanks so much for any insight.
Cheers,
K
Plog: This is roughly the data I would like returned:
License_Number Work_Int_Partpt_Legal_Name Update_Date_Time Work_Int_Partpt_Interest
0000001--------------------Company A---------------------3/29/2016-------------------100
0000003--------------------Company B---------------------3/29/2016-------------------100
0000006--------------------Company C---------------------11/3/2014-------------------15
0000006--------------------Company D---------------------11/3/2014-------------------85
and etc
Attachments
Last edited: