Help with Query - guidance needed! 1

[101287]

Need your guidance and/or help! Need query to rSead table and exclude records including wildcards from another table.
The query developed is below. However, the expected results are not correct. Could you please advise and/or share expertise
to fulfilled requirement. Thanks, Luis

Environment: Windows 7 OS/Ms Access 2007
SELECT *
FROM tblOne, tblTwo
WHERE (((tblOne.Name) In (SELECT tblTwo.Name
FROM tblTwo
WHERE (tblOne.Name Like Trim(tblTwo.Name) & "*" )) AND ((tblOne.Product)<>[tblTwo].[AuditId])));

Structure: Two tables.
1. tblOne - contained historical data related to customer Name Products
2. tblTwo - contained data to be stripped from tblOne.

Results desired: Query to extract all the data that is in tblOne that is not included in tblTwo.
Data in tblOne: Data in tblTwo
Name Product Name Auditid
Hebby Roman 50 Roman 50
Luis Roman 150 Matt 50
Johnny Roman 25 Roman 25
Matt 145 Matt 145

Expected Results:
Luis Roman 150

Actual Results from Query:
Matt 145

 

[randy700]

I'd start by using the Find Unmatched Query Wizard.


Randy

 

[Andrzejek]

query to rSead table and exclude records [(]including wildcards[)] from another table."

First, get all the records from OtherTable you want to exclude from rSead table:
[tt][blue]
SELECT Something From AnotherTable
WHERE Field LIKE '*ABC*'[/blue][/tt]

So you can run:

[tt]
Select * FROM rSead
WHERE SomeField [red]NOT IN[/red] ([blue]
SELECT Something From AnotherTable
WHERE Field LIKE '*ABC*'[/blue])
[/tt]

Where Something and SomeField are 'matching' fields in both tables.

Have fun.

---- Andy

 

[101287]

Thank you Andy for sharing your expertise. Your suggestion/recommendation work.