?
Solved

ms access query help

Posted on 2011-02-16
4
Medium Priority
?
264 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

762 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