Access query--select higest and second higest value in one row

Looking at Access after a long time away.

I have three tables that I need to use for a query
tblProjects              ( list of Projects:  ProjectID (PK), ProjectName)
tblFunds                 (list of Funds that own the projects: Fund ID (PK), FundName)
tblProjects_Funds  (linking table for the many-to-many relationship between the two tables: ProjectID,  FundID, OwnedPercent)

I want a query that produces a row for each property and the two funds that own the most of the project:
ProjectID
ProjectName
Fund1: (FundName--of the fund that owns the highest percentage)
Fund1OwnedPercent:
Fund2: (FundName--of the fund that owns the second highest percentage)
Fund2OwnedPercent

How should I get started on this?  Thanks!
PhereklosAsked:
Who is Participating?
 
hnasrCommented:
Try this. One function is used to sequence funds.
Table: p_f_percent
project      fund      perc
p1      f1      0.1
p2      f1      0.1
p2      f2      0.2
p3      f3      0.4
p3      f4      0.3
p3      f5      0.5
few queries used to get the final result.
Final query is just to hide second fund for one fund record.

Result of query: p_f_percent_from_xtab
project      firstFund      secondFund2      fund1      fund2
p1      f1                           0.1      
p2      f2      f1                     0.2      0.1
p3      f5      f3                    0.5      0.4

Check this sample database.
Hope that helps!
p-f-percent.mdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
what if two %'s are the same (fund1=40 Fund 2=40 Fund 3=20)
What if they are all 33.33%?
What if one fund owns 100%
0
 
PhereklosAuthor Commented:
Good points, I should have been more clear.

In most instances there is only one fund that owns the project (that does not mean, however, that the percentage is 100% as there is also a general partner that might own 1%, .1%, or .01%.)  If there is only one fund, then I want the Fund2 cells to be blank.

I don't have any funds that exactly share a project along the same ownership percentages...it is highly unlikely, but certainly theoretically possible, so I should probably design my query for that eventually.  In this case, I really don't care which fund comes first, as long as the results are the same the next time I run the query.  So in the event of a tie, I could select the fund that has the lowest FundID (which is an autonumber PK) and have that be the listing in Fund1?

I don't have any projects that are owned by three funds, but that is a more likely possibility.  In this case, I will just ignore the smallest fund.  That is probably another question!


When I have done this for reports, I have embedded a subreport in the main report to show all the funds.  But in this case I have been provided an excel template to fill out, and I can only have one row per project.  I am planing on directly exporting the query to Excel.
0
 
Jeffrey CoachmanMIS LiasonCommented:
So can you post an example of the *exact* output you are seeking
0
 
PhereklosAuthor Commented:
I have attached an the exact output I am seeking.   Thanks for looking.
query-example.xlsx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.