Deduction Query

[ZPBJ]

I have a source table that has Proj#, Vendor, BidAmt, FinalCost

I have used a query to make another table that aligns BidAmt and FinalCost on one row

How can I compare table1 to table2 and return the items which have a BidAmt but no FinalCost?

The BidAmt and the FinalCost are entered separately because of separate users

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

 

[PHV]

Retrieve all records in table1 not in table2:
SELECT * FROM table1 LEFT JOIN table2
ON table1.[Proj#]=table2.[Proj#] AND table1.Vendor=table2.Vendor
WHERE table2.FinalCost 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

 

[OhioSteve]

Is this accurate? Both bid amount and final cost exist in table1. For reasons beyond your control, you have two "amount" fields in the first table. In each record in the first table, one of those two fields will have data.

If all of that is true, you don't need the second table to answer this particular question. If you want "items with a bid amount but no final cost" then bid amount should be "not null" and final cost should be "null".

SELECT yourTable.field1, yourTable.field2, yourTable.field3, yourTable.[bid amount], yourTable.[final cost]
FROM yourTable
WHERE (((yourTable.[bid amount]) Is Not Null) AND ((yourTable.[final cost]) Is Null));

 

[ZPBJ]

I may have done some stupid during the creation of the form. Because I have to have a form for the first data entry person and one for the engineer, each form usage creates a new row in the table. Is there a simplified way of linking the 2nd form to the initial data set thus rendering this thread moot?

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

 

[OhioSteve]

Its a bit difficult for me to follow your last post. You say that each "use" of the form creates a new "row". The user SHOULD create a row when they enter a record.

Let me make a guess about your situation. You started out with a table that had two money fields. Now you realize that you only need one money field. But you want to move the data from the old table to the new table. Is that accurate?