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
GROUP BY tbl_Master.wPurchaseOrderNbr
ORDER BY tbl_Master.wPurchaseOrderNbr;
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?