Link to home
Start Free TrialLog in
Avatar of johnmadigan
johnmadiganFlag for United States of America

asked on

ms access query help

I have a table with the following fields:  Proposal#, BidDate, ProjectName, Status, Contractor.  I need to make a query to determine Proposals that were Won vs. Lost.

Here is some sample data:
Proposal#   BidDate   ProjectName   Status    Contractor
1001            2-15-11   ABC                Won       bob
1002            2-15-11   ABC                Lost       joe
1003            2-15-11   ABC                Lost       mike
1004            2-16-11   DEF                 Open      jake
1005            2-16-11   DEF                 Lost        bob
1006            2-20-11   GHI                  Lost        joe
1007            2-20-11   GHI                  Lost        bob
1008            2-20-11   GHI                  Lost        jake

I would like to make a query that will put a value of Won, Lost or Open in a WL column so the output would look like:

Proposal#   BidDate   ProjectName   Status    Contractor  WL
1001            2-15-11   ABC                Won       bob            Won
1002            2-15-11   ABC                Lost       joe              Won
1003            2-15-11   ABC                Lost       mike            Won
1004            2-16-11   DEF                 Open      jake            Open
1005            2-16-11   DEF                 Open      bob             Open
1006            2-20-11   GHI                  Lost        joe              Lost
1007            2-20-11   GHI                  Lost        bob             Lost
1008            2-20-11   GHI                  Lost        jake             Lost

There are times we have multiple proposals for a project - we will send a modified bid to different contractors bidding on the same job.  So I need to look at the group of proposals for a ProjectName and Bid date.

If one of the proposals in the group got accepted (status = Won) then the WL = Won for all the proposals in the group.  

If a group has a statue of all Lost then WL = Lost for the group.

If one proposal in a group has a status = open then the WL = Open for the group.

Any suggestions on the best way to set up this query?

Thanks for your help.
Avatar of Thomasian
Thomasian
Flag of Philippines image

Change "Proposals" to your table name
SELECT P1.*
      ,(SELECT MAX(Status) FROM Proposals P2 WHERE P1.ProjectName=P2.ProjectName) AS WL
FROM Proposals P1

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Helen Feddema
Helen Feddema
Flag of United States of America 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
Here is the output of the final query:
Final-Query.jpg
From what you described, it looks like WL is just a copy of Status, so try this:

SELECT [Proposal#], BidDate, ProjectName, Status, Contractor,  Status as WL FROM Proposals;