[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

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.
0
johnmadigan
Asked:
johnmadigan
  • 2
1 Solution
 
ThomasianCommented:
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

0
 
Helen FeddemaCommented:
I did it with nested queries -- rather klutzy, but it will work so long as there aren't huge numbers of bids for the same project on the same day (which seems a reasonable assumption):
Nested-Queries.jpg
0
 
Helen FeddemaCommented:
Here is the output of the final query:
Final-Query.jpg
0
 
GRayLCommented:
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;
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now