Complex query question-Grouped, Chronological Running Total (1 Viewer)

kmsmith

New member
Local time
Today, 12:26
Joined
Sep 19, 2017
Messages
8
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
 

Attachments

  • Capture.PNG
    Capture.PNG
    32 KB · Views: 135
Last edited:

plog

Banishment Pending
Local time
Today, 14:26
Joined
May 11, 2011
Messages
11,611
Using the data you supplied, can you now post the data you hope your query will return?
 

kmsmith

New member
Local time
Today, 12:26
Joined
Sep 19, 2017
Messages
8
Plog--posted my ultimate goal.

For the first step of my query however, this would be fine--I would then make another query to eliminate anything over 100:

License_Number Work_Int_Partpt_Legal_Name Update_Date_Time Work_Int_Partpt_Interest Running Total
0000001------------------Company A-----------------3/29/2016-------------------100-------------------100
0000003------------------Company B-----------------3/29/2016-------------------100-------------------100
0000006------------------Company C-----------------11/3/2014-------------------15--------------------15
0000006------------------Company D-----------------11/3/2014-------------------85--------------------100
0000006------------------Company D-----------------10/4/2013-------------------15--------------------115
0000006------------------Company D-----------------10/4/2013-------------------85--------------------200
 
Last edited:

plog

Banishment Pending
Local time
Today, 14:26
Joined
May 11, 2011
Messages
11,611
The starting data (the screenshot) doesn't have [Work_Int_Partpt_Legal_Name] values, so I cannot build a query to generate your desired result.

Can you post better sample data? Next post give me 2 sets of data:

A. Starting data from your table. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show me what you hope to end up with when you feed this new query the data from A.
 

kmsmith

New member
Local time
Today, 12:26
Joined
Sep 19, 2017
Messages
8
The starting data (the screenshot) doesn't have [Work_Int_Partpt_Legal_Name] values, so I cannot build a query to generate your desired result.

Can you post better sample data? Next post give me 2 sets of data:

A. Starting data from your table. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show me what you hope to end up with when you feed this new query the data from A.

I have created a simple set of sample data that illustrates the workflow I have in mind, see attached :) --I hope this makes more sense.
 

Attachments

  • Capture.PNG
    Capture.PNG
    98.3 KB · Views: 135

plog

Banishment Pending
Local time
Today, 14:26
Joined
May 11, 2011
Messages
11,611
Essentially what you want is a running total query. Then you want to apply criteria to that running total column. You can do this with a DSum (search this forum for 'Running Total' to find lots of examples).

I do this with a correlated sub-query. That means I write a sub-query inside a field of design view that is able to access the main query to use as criteria. I do it this way, because its pure SQL and works on any database, not just Access. DSum only works in Access.

Below is the SQL for your query:

Code:
SELECT A.License, A.ShareholderName, A.UpdateDate, A.PercentInterest
FROM YourTableNameHere AS A
WHERE ((((SELECT SUM(PercentInterest) FROM YourTableNameHere WHERE A.License = YourTableNameHere.License AND A.UpdateDate<=YourTableNameHere.UpdateDate))<=100));

Once you paste that into the SQL view of a query you will be able to then go back into Design View to see how it works.
 

kmsmith

New member
Local time
Today, 12:26
Joined
Sep 19, 2017
Messages
8
I cannot thank you enough plog! That worked beautifully. I clearly need to brush up on my SQL skills!!! :D
 

Users who are viewing this thread

Top Bottom