Find unmatch data from another table with two critreria

[gaurav2310]

Hi All,

Need help on one of the queries i am trying to make. I want to extract unmatched data based on two criteria.

I have two tables:-

TblCoDetails having fields - RegnNumber CoName
TblDetails having fields - RecordNo CoName FinancialYear Product CostPrice SalesPrice

I want the name of company whose data is not yet entered into TblDetails based on FinancialYear criteria.

For e.g. if in TblCoDetails i have 5 companies A,B,C,D and E.
And in TblDetails I have 3 records for Co A,D and E for the FinancialYear 2013-2014.
My tables have one to many relationship and are connected with join properties 2 (Left Join).
I want a query which will give me a result of B and C as an answer, because data of these two companies are not yet entered for that particular FinancialYear.

Thanking you in anticipation.

Regards,
Gaurav

[hapm]

Simply add the year as a calculated field using a subquery:
SELECT RegnNumber, TblCoDetails.CoName FROM (SELECT *.TblCoDetails, [YearToCheck] AS SelectedYear) AS TblCoDetails LEFT JOIN TblDetails ON TblCoDetails.RegnNumber = TblDetails.RecordNo AND TblCoDetails.CoName = TblDetails.CoName AND TblCoDetails.SelectedYear = TblDetails.FinancialYear WHERE TblDetails.RecordNo IS NULL

[rpeare]

You should not be linking these two tables on the company name. your table tblDetails should have the RegnNumber (assuming that is unique and does not change over time). Linking any two tables via a text string that the users have the ability to change is asking for trouble in a big way. If RegnNumber is a company assigned value (and therefore also changable) you should add an autonumber field to that table and use the autonumber field as your link.

Secondly do you have another table that tells you what the relationship between the companies and the parent are? If you don't then you would likely be doing this comparison from the previous year to the current year.

[gaurav2310]

Thanks for the reply hapm.

I want to select CoName and not RegnNumber. And also RegnNumber <> RecordNo.

RecordNo is an Autonumber field and is not related to RegnNumber.

TblCoDetails primary key is CoName which is connected to CoName of TblDetails.

Thanks & Regards,
Gaurav