writing checks my (MS)Access can't cash

Dr. Frenchenstein

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?

Daenris

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