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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Hamed NasrRetired IT ProfessionalCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.