Conditional Quiery Problems

[DreamAwake]

I have attached a screen shot of my database schema.

This is my scenario;

There are 3 different situations when it comes to searching a quote.

1. You have a job with 0 quotes.
2. You have a job with say 3 quotes, but none have been awarded.
3. You have a job with 3 Quotes, 1 or 2 have been awarded.

I want to have a query which lists all quotes but if the job has awarded 1 of the quotes I don't want to see the others. But I still need to see new jobs without quotes. I understand outer joins are needed but honestly i've spent hours on this and need some major assistance.

I have the following so far which is showing all quotes but is displaying un-awarded quotes when a quote has been awarded.

SELECT tblContract.MNW, tblContract.Location, tblContract.Status, tblCompany.CompanyName, tblQuote.awarded
FROM (tblContract LEFT JOIN tblQuote ON tblContract.ContractID = tblQuote.ContractID) LEFT JOIN tblCompany ON tblQuote.CompanyID = tblCompany.CompanyID
GROUP BY tblContract.MNW, tblContract.Location, tblContract.Status, tblCompany.CompanyName, tblQuote.awarded
HAVING (((tblContract.MNW)="mw000669") AND ((tblQuote.awarded)=1));

 

[namliam]

You will have to make a union query...

Make one query that only shows all quotes with nothing awarded, save it and call it i.e. qryQuoted.
Now make a second query that shows all awarded quotes without " non-awarded " quotes, save this and call it i.e. qryAwarded.
Make sure these 2 queries have the SAME fields in the SAME order, even if some of the fields are not really used for that version. This is manditory

Now make a third query:
Select * from qryQuoted
Union
Select * from qryAwarded

Alternatively maybe you need to use "Union all" instead of Union, but I doubt it.

 

[DreamAwake]

namliam, its possible in 1 query, i've posted this same question at Experts Exchange as this was an urgent matter;

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_23385218.html

I'd post SQL here but unsure on their TOS

 

[namliam]

I am aware of the fact it can be done in one query, but as you can see in the SQL it is not that easy to do it in 1 query.

Doing it this way is usually easier and more maintable as well as more understandable to 'less advanced' people.