How to retrieve records from one table based on other 
Author Message
 How to retrieve records from one table based on other

To retrieve all completed issues that HAVE been invoiced I use:
Select * From issues Where [resolved] = True AND
        id IN (Select id From invoices)

To retrieve all that HAVE NOT been invoiced I tried this:
Select * From issues Where [resolved] = True AND
        id NOT IN (Select id From invoices)

That query took approx: 8 minutes (vs 2 seconds for the first query)
and then returned 0 records.

Can anyone tell me why this doesn't work and what I should submit to
retrieve the records that HAVE NOT been invoiced.(ie: the records from
the issues table whose id is NOT found in the invoices table)

My 'workaround' is to create a recordset of all issues that are
resolved and then loop through it one by one doing a findfirst of the
ID in the invoices table. If no match I add the id from that record to
an array and then create a query string full of ORs:
Select * from issues Where id = 2145 OR id = 2231(...etc.)

Thanks,
[VB5]
Robb
Remove autospam deterrent from address before emailing



Mon, 06 Aug 2001 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Eliminate records of one table based on another table record count

2. Deleting records from one table based on data from another

3. need help please:joined tables,add new entries based on one table columns

4. need help please:joined tables,add new entries based on one table columns

5. Auto create record for one-to-one relationship tables

6. records transfer from one table to another table

7. changes to all records in one rec set based on position in another rec set

8. Help on Faxing one app's output based on an Access table

9. Retrieving a record in one go

10. populating multiple records based on another table

11. sorting records from 1 table into several based on a field value

12. Linking Access Tables in Different *.mdb Files, Populating an Access Table From Several Others

 

 
Powered by phpBB® Forum Software