Link to home
Start Free TrialLog in
Avatar of Davisro
DavisroFlag for United States of America

asked on

MS Access Query with IF Statement in a Subquery

Hello,

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.

MY REQUIREMENT
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?

Thanks,
Avatar of Member_2_5996043
Member_2_5996043

Combine the consultantID and the POnum into one field and group on that. :)
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Answer provided.