Comparison of two table to look for un mached records

[Ihussein]

I have two tables that I want to check is there any record in Table (A) but not in Table (B) the comparison column is number field, my problem is that the numbers are in serial in the first column while scattered into two columns in table (B). I don't what to go for the solution of generating in between serials of Table (B).
hope the attached image is self explanatory. Regards

[ItsMe]

Have you tried using the Unmatched Query Wizard? I would use the wizard and have it join on one of the two options. Afterwards, I would go into design view and add a second join, duplicate the existing join but use the second column from table B. Then, be sure to add the second column to the grid and the Is Null criteria.

[Ihussein]

Thanks for the suggestion, but how to do the second join?

[CJ_London]

just to clarify what you want.

I don't what to go for the solution of generating in between serials of Table (B).

for Seliny do you want to bring back records with 4,5 and 6 in the qnt field? and 9 for Tarke? because they are not 3,7, 8 or 10

Or with this dataset there would be nothing to report because 4,5 and 6 are between 3 and 7 and 9 is between 8 and 10

[Ihussein]

In Table(B) I have a date column, what I want is to generate and compare all the records which are having date, against the record of table(A), yes I want to bring back records with 4,5 and 6 in the qnt field, then do the comparison, the above table is just and example but the table has more that 1000 records at some point I might have abouth 150 records without date that I do n't need to compare them with Table(A).
hope this clarifies. Thanks

[CJ_London]

still not clear - your table b has no dates so by what you are saying you would also want 3 and 7 as well - since there is no date

[Ihussein]

Sorry for that, but dates are suppose to be their, mistakenly I did n't update the table before attaching, here is the update table. apology for the confusion made

[CJ_London]

SELECT TableA.* FROM
(tableA AS A LEFT JOIN TableB as B1 ON A.HdgeName=B1.HdgeName AND A.Date=B1.Date AND A.Qnt=B1.QntFrom) LEFT JOIN TableB as B2 ON A.HdgeName=B2.HdgeName AND A.Date=B2.Date AND A.Qnt=B2.QntTo)
WHERE B1.HdgeName is null AND B2.HdgeName is null

In your example data this will back all records in tableA because there are no dates in tableB

[CJ_London]

posted at the same time! in your revised sample data you will still get all the records from TableA except Serah 1 - because dates don't match or are not in tableB

[Ihussein]

I tried the above sql code but it's giving and syntax error in from clause, I uploaded the database to my drop box folder for your easy reference. please see below link
https://www.dropbox.com/s/ob7tvlcqi3...hed.accdb?dl=0
Regards

[CJ_London]

given your ability to not post what you actually have, I suspect that the table and field names you have provided do not match with what you have in your tables - and Date, if that is what you are using is a reserved word, even in square brackets it can still cause unexpected errors

[ItsMe]

Not sure exactly what you are after or if Ajax has provided you with a solution. If I want to check for values that do not exist in multiple columns I create multiple joins. Only to answer your question ...

Thanks for the suggestion, but how to do the second join?

Drag the field name from the one table to the other, choosing the second option (field name) within the other table. Double click the line that is the join to change the properties of the JOIN so it matches the first. Using the second option (field name), add it to the grid. Add it to the grid by double clicking it. Then, add the criteria to the second option (field name) so it matches the first option (field name). The first option (field name) should have Is Null in its criteria, from the Wizard.

[Ihussein]

Thanks for your efforts, what ever my abilities r. I was just trying to give more clarifications. Any how, now the Idea is clear, I will further explore for Join Query in YouTube.