Solved

ms access query help

Posted on 2011-02-16
4
259 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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.

863 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

22 Experts available now in Live!

Get 1:1 Help Now