Solved

ms access query help

Posted on 2011-02-16
4
258 Views
Last Modified: 2012-05-11
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
Comment
Question by:johnmadigan
  • 2
4 Comments
 
LVL 22

Expert Comment

by:Thomasian
ID: 34907811
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
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 34907971
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
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34907977
Here is the output of the final query:
Final-Query.jpg
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34910964
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now