MS Access Query with IF Statement in a Subquery


I have an Access database of Consultants and associated information such as Position ID, Consultant ID, Position Status, Consultant Status, etc, including Purchase Order Number.

I understand the table is not normalized (i inherited it) and in reality looks like an excel spreadsheet wrapped in Access. Be that as it may...on this table, some consultants on this table have the same Purchase Order number. So the table structure looks like this:
PositionID   ConsultantID   PONum      LastName   FirstName   Additional Fields-->
101512        C00125             62510478   John             Reddy
101513        C00126             62510478   Paula           Jones
101514        C00127             97851244   Jeff               Jackson

The first two consultants are on the same PO.

Is to take a Budget query from our system which has Purchase Orders and Budget Amounts, and add in the Consultants from the table above. I see that since there are multiple consultants per PO, the query will create duplicate lines in the budget for each PO with more than one consultant. One solution suggested to me by a manager is to create a query of these two tables with a Consultant Name field with an expression that says,
    "If there's one Consultant per PO, then return the consultant name, otherwise if there are multiple consultants per PO, return the word 'Multiple'"

I tried to do this with an aggregate query
SELECT tbl_Master.wPurchaseOrderNbr, Count(tbl_Master.wPosition) AS CountOfwPosition
FROM tbl_Master
GROUP BY tbl_Master.wPurchaseOrderNbr
HAVING (((tbl_Master.wPurchaseOrderNbr)<>""))
ORDER BY tbl_Master.wPurchaseOrderNbr;

Open in new window

Which counts how many Positions (there's only one consultant per position) exist for each PO. But if I could write this Sql to include my expression that decides if the Count of Positions per PO is > 1, then return the word "multiple", otherwise return the [LastName] & ", " & [FirstName] of the Consultant

I suspect this is accomplished with a subquery in my Sql. Anyone know how to accomplish this?

DavisroBudget AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Combine the consultantID and the POnum into one field and group on that. :)
Gustav BrockCIOCommented:
Try with:

    Count(*) AS CountOfwPosition,
    IIf(Count(*)=1, 'Multiple', First([LastName]) & ", " & First([FirstName]) As Consultant
    tbl_Master.wPurchaseOrderNbr Is Not Null

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
Answer provided.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.