Link to home
Start Free TrialLog in
Avatar of MSelect
MSelect

asked on

Retrieve unused characters

Hi,

From inside a VB6 program, I have 2 tables in a MDB database

The first table stores Characters : A  B  C  D  E  F  G  H  T  Y  Z ...

The 2nd tables uses theses characters in a text field, in any number and order : ADH  EFY  EAZYD  HCAY  HZ  BDA  ...

I want to retrieve the characters of the first table not used in any record of the 2nd table ...with a single querydef : In my example, G and T

Thank you very much,

Philippe
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Select CharLetter From tCharacter Where CharLetter = fnCharLetter([CharLetter])

This the query you need.  In Access, in a module, under module tab, paste:

Public Function fnCharLetter(strChar As String) As String
Dim rs 'As DOA.RecordSet
Dim bolMatchFoundYN As Boolean
Set rs=Currentdb.OpenRecordset("Select MultiChar From tblSecondTable")
If rs.recordcount>0 Then
bolMatchFoundYN =False
rs.movefirst
Do until rs.eof
if instr(rs!MultiChar,strChar)>0 then
   bolMatchFoundYN =False
  'exit do  'don't use for now
End if
rs.movenext
loop
Else   'when second table is empty
    fnCharLetter=strChar
    exit function
End If
if bolMatchFoundYN = flase then
   fnCharLetter=strChar
else
   fnCharLetter="NotFound"
end if
End Function

mike
Now, to avoid use of function call from vb, save following SQL as qQuery1 (save it in access).

Select CharLetter From tCharacter Where CharLetter = fnCharLetter([CharLetter])


And then use following from vb environment:

Select * From qQuery1

mike
ASKER CERTIFIED SOLUTION
Avatar of shanesuebsahakarn
shanesuebsahakarn
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Given a single char field strLetter in tblLetters and a multi-char field strCode in tblCodes:

SELECT strLetter FROM tblLetters
WHERE strLetter Not In(
    SELECT DISTINCTROW strLetter
    FROM tblLetters, tblCodes
    WHERE strCode Like '*' & strLetter & '*'
    )

Cheers :)
Shane, does that actually work in recent versions of Access? Up to A2k this isn't supported in a JOIN.
Yep, it does - just tested it on A97 and it works . I remembered doing something similar in an A97 app a while back so I was pretty sure that Jet 3 supports Like in join criteria as well. :)
Sorry Shane, I did test your query before asking this question. I must have done something wrong because now it works. I learned something :)
cheers.
Avatar of MSelect
MSelect

ASKER

Hello Arfang and al.
Thank you but … it doesn't work …

Actually, its a bit more complicated :

TableChars : 1 field named " Code" containing Characters  
MainTable1 : 1 field named "Code" and made of 1 to 10 chars taken from TableChars  BUT the first 2 characters must not be taken into account
MainTable2 : The "Code" field of this 2nd main table can contain
- either the same structure as the "Code" field of MainTable1
- either twice this structure separated with ";"

example : (so that it's easier to explain, only the uppercase chars must be taken into account in these examples) :

MainTable1 : acDEF   zeR   sxKSTY
MainTable2 : dfRT  dxMVX;hjGT   bnKP;swO   ntR   bh;heYCG

According to your suggestion, here is what I have written :

Dim DB as database, QD as Querydef

'Exploring Chars 3 to 10 of MainTable1
Set QD = DB.CreateQueryDef("Unused1", "SELECT TableChars.Code
FROM TableChars LEFT JOIN MainTable1
ON MID(MainTable1.Code, 3, 10) LIKE '*' & TableChars.Code & '*'
WHERE MID(MainTable1.Code, 3, 10) Is Null")
 
'Exploring Chars 3 to 10 of MainTable2 when it is made of a unique group of chars (i.e. not containing ";")
Set QD = DB.CreateQueryDef("Unused2", "SELECT Unused1.Code
FROM Unused1 LEFT JOIN MainTable2
ON MID(MainTable2.Code, 3, 10) LIKE '*' & Unused1.Code & '*'
WHERE MainTable2.Code NOT LIKE '*;*' AND MID(MainTable2.Code, 3, 10) Is Null")
 
'Exploring Chars 3 to the position of ";" of MainTable2
Set QD = DB.CreateQueryDef("Unused3", "SELECT Unused2.Code
FROM Unused2 LEFT JOIN MainTable2
ON MID(MainTable2.Code, 3, INSTR(MainTable2.Code, ';') - 3) LIKE '*' & Unused2.Code & '*'
WHERE MainTable2.Code LIKE '*;*' AND MID(MainTable2.Code, 3, INSTR(MainTable2.Code, ';') -3) Is Null")
 
'Exploring Chars 3 to … of the 2nd group of chars
Set QD = DB.CreateQueryDef("Unused4", "SELECT Unused3.Code
FROM Unused3 LEFT JOIN MainTable2
ON MID(MainTable2.Code, 3, INSTR(MainTable2.Code, ';') + 3, 10) LIKE '*' & Unused3.Code & '*'
WHERE MainTable2.Code LIKE '*;*' AND MID(MainTable2.Code, INSTR(MainTable2.Code, ';') + 3, 10) Is Null")

DB.Execute "SELECT UnusedC4.* INTO TableUnused FROM UnusedC4"

???????????????????????????????????????
Well - your Execute line is selecting from a query called UnusedC4, but the queres you have created are called Unused4 etc. Is that correct?
If you have upper case, you may consider:    (please comment on this solution)

Select Code From TableChars Where Asc(Code) = fnCharLetter(Asc([Code]))

This the query you need.  In Access, in a module, under module tab, paste:

Public Function fnCharLetter(intChar As Integer) As integer
Dim rs 'As DOA.RecordSet
Dim bolMatchFoundYN As Boolean
Set rs=Currentdb.OpenRecordset("Select Code From MainTable1")
If rs.recordcount>0 Then
bolMatchFoundYN =False
rs.movefirst
Do until rs.eof
if fnMatchFoundYN(rs![Code],intChar)=True then
   bolMatchFoundYN =True
   exit do  
End if
rs.movenext
loop
Else   'when second table is empty
    fnCharLetter=intChar
    exit function
End If
if bolMatchFoundYN = flase then
   fnCharLetter=intChar
else
   fnCharLetter=123456789
end if
End Function

Function fnMatchFoundYN(strCode as string, intCode as integer)As boolean
If Len(strCode)>2 Then
   strCode = mid(strCode,3)
Else
   fnMatchFoundYN=False
   Exit Function
End If

Dim i as integer
For i=1 to Len(strCode)
If Asci(mid(strCode,i,1))=intCode Then
    fnMatchFoundYN=True
    Exit For
End If
Next i
End Function

mike
btw, I have focused on MainTable1 only for time being.
Avatar of MSelect

ASKER

YES, my last Querydef is well "Unused4" and the Execute line should have been written
DB.Execute "SELECT Unused4.* INTO TableUnused FROM Unused4"
Typing error !

NO, there are only Uppercase characters used in the "Code" fields. In my example, I have written upper- and lowercase letters to only make this example more readable and understandable.
Sorry ... it would have been easier !

I do not really like the structure of the database tables I'm working with but ... the people I'm working for don't want their current structure to be changed ...................
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Lookin at your queries, there is essentially two problems:

1) Mid() will not return Null if the source string is not null. It will return vbNullString ("")
2) You are confusing the ON and the WHERE. In Shane's query, the ON is used to *link* two tables and the WHERE is used *only* to select those records where the link always fails. You cannot change anything in the WHERE clause, you need to place all link information after ON (provided JetEngine SQL accepts it).

I haven't tested your queries, but I have tested mine, and it works. The essential difference between Shane's solution and mine is that he uses " WHERE tblLetterCombos.LetterCombo Is Null" to reverse the search (i.e. find thost *not* matching), while I use "WHERE strLetter Not In(SELECT <matching records>)" In both cases you cannot change that part of the query. In Shane's you can adjust the "ON ...", in mine you can adjust the second "WHERE ...", however.

Good Luck!
Thanks for helping with the clarification harfang - I have a bad cold at the moment and I can't really think straight enough to post long explanations :)
Avatar of MSelect

ASKER

Youpee ……….It's done, thanks to the help of all your ideas that have lighted my candle to find a solution (almost) by myself :

'First, create a set of all the regions to be searched in MainTable1 and Maintable2

Set QD = DB.CreateQueryDef("Unused1",
"SELECT MID(MainTable1.Code, 3, 10) AS A FROM MainTable1
UNION
(SELECT MID(MainTable2.Code, 3, 10) AS A FROM MainTable2
WHERE MainTable2.Code NOT LIKE ';'
UNION
(SELECT MID(MainTable2.Code, 3, INSTR(MainTable2.Code, ';') - 3) AS A FROM Maintable2
WHERE MainTable2.Code LIKE ';'
UNION
SELECT MID(MainTable2.Code, INSTR(MainTable2.Code, ';') + 3, 10) AS A FROM MainTable2
WHERE MainTable2.Code LIKE ';'))")

'Then search this recordset that contain no double since "UNION" remove duplicate items if I well remember :

Set QD = .CreateQueryDef("Unused2", "SELECT Tablechars.Code
FROM TableChars LEFT JOIN Unused1
ON Unused1.A LIKE '*' & Chartable.Code & '*'
WHERE Unused1.A Is Null")

'Finally, create the "physical" table named "Unused" :

DB.Execute "SELECT Unused2.* INTO Unused FROM Unused2"

I should want to thank Soluch and Harfang especially, giving 200 pts to everyone. Is it possible, Mr Moderator ?
FYI:  There is 500 max points.  You split it amongst experts who have contributed and helped you to the solution.  This of course doesn't include me.

Regards,

Mike
more info, There is 500 max points for each question.  One doesn't have the option to increase it according EE member agreement.

mike
Avatar of MSelect

ASKER

OK, split the points to the people who helped me .... very much.
Thank's to all.