Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

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

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
mlagrange
Asked:
mlagrange
  • 2
1 Solution
 
JAMcDoCommented:
Please post some sample tables - parent and child - so that I can better understand your table structure.

J.
0
 
mlagrangeAuthor Commented:
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
 
mlagrangeAuthor Commented:
I came up with a solution myself.
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.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now