As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

writing checks my (MS)Access can't cash

So i have a list of accounts in access, who is responsible for them, and their levels. when two (or more) people are responsible for an account, that means the higher level is the second approver. my boss wants a report based on this, and i think she wants it presented like below:

Account -> Approver1->Approver2->Etc

right now the list of approvers i have is arranged thusly

Level/Account/Approver
1 /X /Steve
1 /Y /Steve
2 /X /Joe

So i would want the report to read:
Account X/Steve/Joe
Account Y/Steve

i may have bitten of more than Access can chew... i guess i could dump the data into excel, and use a macro to shunt the rows over a column as they go up in precedence as an option, but i was hoping to keep this self contained, so i wouldn't have to run and format it each time she wants to know.

Any access gurus out there?

Posts

  • DaenrisDaenris Registered User regular
    edited November 2013
    Hmm, I'm not immediately sure offhand the best way to generalize this to the more than 2 case, but a modification of this StackOverflow answer will get you close. I made your test table quickly in Access with columsn Account, Level, and Approver and then did an SQL query
    SELECT Account,MAX(IIF(`Level`=1,Approver,NULL)) as App1,MAX(IIF(`Level`=2,Approver,NULL)) as App2 FROM Approvers GROUP BY Account ORDER BY Account;
    And the result is a table in the form you want. If there are only ever like 3 or 4 approvers at most, just extending with a few more MAX(IIF.... statements would work, but doesn't generalize to arbitrary numbers of approvers.
    Account     App1     App2
    X           Steve    Joe
    Y           Steve
    

    Daenris on
Sign In or Register to comment.