query with Not-linked tables

[OTWarrior]

I am trying to do a query, where two tables are linked to each other via a unique id.

table 1 contains the data i need (5000 records)
table 2 is created by a query, to be used as a filter. (2000 records)

Now I know I can link the two in order to get the 2000 records based on table 2, but how do I do it the other way around (get the other 3000)?

Thanks in advance

[CreganTur]

Have you considered using Data Shaping via an ADO connection?

If you would like some help with this, then could you provide the table names you're working with, the name of the unique ID field(s), and individual SELECT statements from each table that pull the records you want?

[The Isaac]

Have you tried using a left join in your query with the large table being the table to show all records from, then in the PK of the small table set the criteria to is null. That will show you every record from the larger table where the linked data is not in the smaller one.

[OTWarrior]

My question was a rather simplified version of what I am trying to do, just in case someone was able to come up with the solution to this part of the problem.
Here is the basic example of the queries sql code (I have removed everything that isn't needed, as a lot of the queries is manipulating the fields into different data).
Hope this is clear CreganTur

SELECT [__ADMIN__ERRORS_NotActive_StillLive].Checker, tbl_CORE_Client.nClientID, tbl__Diaries.nBookingID, tbl__Diaries.anStatusID, tbl__Diaries.boolFlexibleUsage, tbl__Diaries.lkupCompanyName, tbl__lkup_productTypes.txtproductTypeDesc
FROM [__ADMIN__ERRORS_NotActive_StillLive] RIGHT JOIN (((tbl_CORE_Client LEFT JOIN tbl__Diaries ON tbl_CORE_Client.nClientID = tbl__Diaries.nClientID) LEFT JOIN tbl__lkup_productTypes ON tbl__Diaries.lkupTypeOfService = tbl__lkup_productTypes.anproductTypeID) LEFT JOIN (tbl__CompanyContacts LEFT JOIN qry__CompanyLookup_ByContract ON tbl__CompanyContacts.nlocationNumber = qry__CompanyLookup_ByContract.nlocationRef) ON tbl__Diaries.nCompanyContactID = tbl__CompanyContacts.anCompanyContactID) ON [__ADMIN__ERRORS_NotActive_StillLive].nClientID = tbl_CORE_Client.nClientID
WHERE ((([__ADMIN__ERRORS_NotActive_StillLive].Checker)<>1) AND ((tbl__Diaries.boolFlexibleUsage)<>True) AND ((tbl__Diaries.lkupCompanyName)=[qry__CompanyLookup_ByContract].[nCompanyID]) AND ((tbl__lkup_productTypes.txtproductTypeDesc)=[qry__CompanyLookup_ByContract].[txtproductTypeDesc]));


Issac, what do you mean by the PK of the table?

[CreganTur]

Have you considered using a Find Unmatched query to get the 3,000 records that are not a part of your 'filter' query?

[OTWarrior]

whoops, didn't even know about that option. I'll look into it and let you know if it does the trick :0

[stanl]

I probably missed something, but why not a third query

pseudo-code


SELECT * FROM Table1 WHERE NOT EXISTS (
SELECT [uniqueid] FROM Table2 WHERE Table1.uniqueid=Table2.uniqueid )


.02 Stan