ms access query help
Posted on 2011-02-16
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.