CODEWITHSUNDEEP
ms-access
bd528
bd528

Reputation: 886

Returning one row query where table has multiple rows

I have 2 tables :-

qryCML
QuoteID
544
289

tblCommissionNew
Quote_ID / Stage / Cost
544 / 1 / 23.99
544 / 2 / 15.50
289 / 1 / 87.99

Is it possible to have a query return the following results based on the above :-

QuoteID / Stage 1 Paid / Stage 2 Paid
544 / Yes / Yes
289 / Yes / No

The query I've put together so far returns 2 rows for quote 544, which isn't what I want

Upvotes: 0

Views: 19

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521804

You can try the following pivot query:

SELECT
    Quote_ID,
    MAX(IIF(Stage = 1, 'Yes', 'No')) AS [Stage 1 Paid],
    MAX(IIF(Stage = 2, 'Yes', 'No')) AS [Stage 2 Paid]
FROM tblCommissionNew
GROUP BY Quote_ID

This employes a trick, namely that the string 'Yes' is lexicographically greater than 'No'. So if a given quote is missing one of the two stages, then no would be the greatest value, otherwise if the stage be present then yes would be the result.

Upvotes: 1

Related Questions