Filtering a database based on instr rules

[c.vaibhav]

Hi All,

I am absolutely new to MS access.

I have 2 database. The first database has 20 columns and 40000 rows. The first database looks like:

Region	Deals
US	8855
APAC	7988 7999
India	6855 6852 6853
India	7411

The other database has only 'Deals' column and each row of the second database has just 1 Deal Number unlike the first database where 'Deals' column can have multiple numbers.

I want to filter the first database where 'Deals' should have numbers that are present in the second database.
For Eg: If the second database contains 8855 & 6853, I would want the 1st and 3rd entry from first database.

FYI, the second database also has around 40000 rows.

Thanks for your help.



Regards,
Vaibhav

I have also posted same query on SQL Server Central
http://www.sqlservercentral.com/Foru...093-391-1.aspx

[orange]

Investigate Normalization. Get you tables Normalized and make life easier for yourself.

With a Normalized table structure it appears you want to do something along this pattern
select records in Table1 , Table2 (where there is a record in Table2 with a matching RecId)
where Table2.RecId=Table1.RecId

[June7]

To accomplish with present data structure will probably require VBA custom function. The function would:
1. receive the multi-part string from table1 as an argument.
2. parse string parts into array using Split function
3. loop through the array and compare each element with values in table2 - DLookup could be used for the search
4. if value found function returns a Yes value
5. the function could be called from a query or a textbox
6. if function called from query, apply filter criteria to field constructed with expression that calls the function

Consider:

Function CompareDeal(strDeals As String) As String
Dim ary As Variant
ary = Split(strDeals, " ")
For i = 0 To UBound(ary)
    If Not IsNull(DLookup("Deals", "Table2", "Deals='" & ary(i) & "'")) Then CompareDeal = "yes"
Next
End Function

SELECT Deals, Region
FROM Table1
WHERE CompareDeal([Deals])="yes";

[c.vaibhav]

Thanks Orange and June7 for your help

@June7 - I tried using your trick and it works absolutely fine on the dummy database I have. I need to try it on my main database which I will be able to do in another 18 hours max. I will close the thread once it works fine on the database I have, else I will bother you once again.

Thank you very much for this amazing piece of code.

Regards,
Vaibhav

[June7]

Sometimes it is easier for me to see a VBA solution than a query but took another look and maybe this will get you what you want:

SELECT Table1.Region, Table1.Deals
FROM Table1, Table2
WHERE (((Table1.Deals) Like "*" & [Table2].[Deals] & "*"));

[c.vaibhav]

Thanks June7

Both code works fine. I am going with the first solution (VBA) as it works faster with the database size that I have.

I believe the thread is classified as Solved. Do I need to take any other action now?

Regards,
Vaibhav

[June7]

Interesting that you found the VBA solution faster. Just glad something worked for you. No, you don't need to do anything else with the thread. I marked it solved after seeing your rep comment. Thank you.