Phereklos
asked on
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!
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!
ASKER
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.
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.
So can you post an example of the *exact* output you are seeking
ASKER
I have attached an the exact output I am seeking. Thanks for looking.
query-example.xlsx
query-example.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What if they are all 33.33%?
What if one fund owns 100%