Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access query to pull maximum percentage, with a couple of catches...

Posted on 2011-03-07
3
Medium Priority
?
366 Views
Last Modified: 2012-05-11
Hello - I'm working with a spreadsheet containing data for corporate construction projects

Each project can have up to 7 business units that will be billed back for the project costs, at a particular percentage, which might be equal splits, or not

These are entered just like you would expect in the spreadsheet:
   Project ID, (...various project info fields...), BU Number1, BU Percent1, BU Number2, BU Percent2, BU Number3, BU Percent3, etc...

The project managers just enter them (in no meaningful, reliable order) in 7 sets of 2 columns on the spreadsheet

When I import this spreadsheet into Access, I load the project header info into a parent table, and then make a series of 7 passes to load the the bill-back entries into a child table

The project bill-back table looks like this:
Project Header ID - parent key back to the projects headers table
Bill Back entry # - the ordinal number of the bill-back entry field pairs (BU Number1 and BU Percent1 are loaded in with an entry # of "1", etc...)
Business Unit ID - ID number of the BU
Business Unit Pct - pct of costs this BU will be billed back for

They want one report that will display, by project, the business unit with the highest bill-back percentage

If 2 or more BU's have the same bill-back percentage, display the BU with the lowest entry #

I have racked my brains, but can't come up with the SQL to get me there in one pass
or even 2... or 3... :-(

Anybody done something like this?

Thanks

0
Comment
Question by:mlagrange
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 3

Expert Comment

by:JAMcDo
ID: 35059405
Please post some sample tables - parent and child - so that I can better understand your table structure.

J.
0
 

Accepted Solution

by:
mlagrange earned 0 total points
ID: 35111428
Ok, I figured it out.

I did a Make Table query off the bill-back table that sorts the records by Project header, BU pct, and BU entry #.

Then I do a summary query off THAT table that pulls the "First" values by Project ID.

Thanks anyway

Mark
0
 

Author Closing Comment

by:mlagrange
ID: 35304216
I came up with a solution myself.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question