3 Step Query

[ZPBJ]

I need to show in a query one list of vendors who haven't bid on a project. In order to do this, the query must be able to determine whether the vendor bids on this type of project and whether we have already received a bid

tblvalidProjects
ProjectID(auto#)
Project Number
Project Requestor
Project Request Date
Project Type

tblBidTracker
ProjectID
VendorID
Bid

tblvalidprojecttypesbyvendor
VendorID
Project Type


Thanks for your help

__________________________________
Remember that time when I took the box? - Peter

 

[lespaul]

select * from vendor where vendorID not in (select vendorId from tblBidTracker)

Will give you a list of all vendors who have not bid on ANY project.

Do you want to be able to enter a project type or project number in order to get a list of all vendors who COULD have bid on a specific project, but haven't yet?

Leslie

 

[ZPBJ]

I would like to return who could and who hasn't. The first example you wrote, if I wrote it right - looked like this

select * from validprojecttypesbyvendor where vendornumber not in (select vendor from tblBidTracker)

It did not return any records

__________________________________
Remember that time when I took the box? - Peter

 

[PHV]

Create a saved query named, say, qryProjectVendor:
SELECT ProjectID,VendorID
FROM tblvalidProjects P INNER JOIN tblvalidprojecttypesbyvendor V ON P.[Project Type]=V.[Project Type];

Now your query:
SELECT Q.ProjectID,Q.VendorID
FROM qryProjectVendor Q LEFT JOIN tblBidTracker B ON Q.ProjectID=B.ProjectID AND Q.VendorID=B.VendorID
WHERE B.VendorID Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ZPBJ]

I don't understand why, but my database is having a helluva time returning anything.

The first query returns data, but the second query doesn't. Do I need to do something on the table that creates a record for each project for each valid vendor? Because a bid doesn't get keyed unless it is received, and it's not returning a null value if it doesn't exist.

__________________________________
Remember that time when I took the box? - Peter

 

[lespaul]

Do you have a table that lists all the vendors and their names? You're probably going to want to use that table as well (or is VendorID the "name" of the company?)

So what you want is for each project find all the vendors who are eligible to bid on each project and if they haven't bid, list them?





Leslie

 

[ZPBJ]

I have a table, validVendors, that had vendorID and vendorname

__________________________________
Remember that time when I took the box? - Peter

 

[PHV]

and it's not returning a null value if it doesn't exist
This is the behavior for LEFT OUTER JOINs...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ZPBJ]

Would it make sense then to have the form auto create records for all valid vendors of a project type when a project is entered?

and if so, how does that work?

__________________________________
Remember that time when I took the box? - Peter

 

[PHV]

Actually, how is populated tblvalidprojecttypesbyvendor ?
Does qryProjectVendor returns all the vendor qualified for each project ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ZPBJ]

tblvalidprojecttypes is maintained by me. It is entered separately.

qryProjectVendor is working

__________________________________
Remember that time when I took the box? - Peter

 

[PHV]

So the second query should works too.
It is an unmatched query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244