?
Solved

Retrieve unused characters

Posted on 2004-11-23
22
Medium Priority
?
376 Views
Last Modified: 2013-12-03
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
0
Comment
Question by:MSelect
  • 6
  • 5
  • 4
  • +2
20 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 12660762
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
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 12660777
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
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 336 total points
ID: 12662250
SELECT tblCharacters.* FROM tblCharacters LEFT JOIN tblLetterCombos ON tblLetterCombos.LetterCombo Like "*" & tblCharacters.Character & "*" WHERE tblLetterCombos.LetterCombo Is Null
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 58

Expert Comment

by:harfang
ID: 12662286
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 :)
0
 
LVL 58

Expert Comment

by:harfang
ID: 12662304
Shane, does that actually work in recent versions of Access? Up to A2k this isn't supported in a JOIN.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12662319
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. :)
0
 
LVL 58

Expert Comment

by:harfang
ID: 12662367
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.
0
 

Author Comment

by:MSelect
ID: 12662693
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"

???????????????????????????????????????
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12662906
Well - your Execute line is selecting from a query called UnusedC4, but the queres you have created are called Unused4 etc. Is that correct?
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 12662991
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
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 12662998
btw, I have focused on MainTable1 only for time being.
0
 

Author Comment

by:MSelect
ID: 12663421
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 ...................
0
 
LVL 2

Assisted Solution

by:Soluch
Soluch earned 332 total points
ID: 12664723
Hi Philippe,

I've had a look at your requirements and come up with a partial solution.  Let me explain.

This is the scenario:

You have a table called "TableChars" with a single field named "Code", with the following contents (as example only):

Code
A
B
C
D
E
F
G
H
T
Y
Z

You have a table called "MainTable1" with a single field, also called "Code", with the following data (example):

Code
AAADH
BBEFY
CCEAZYD
GGHCAY
TTHZ
EEBDA

I created a single query that will return all records from TableChars that do not appear after the first two characters in any of the records in MainTable1, i.e. it will check "ADH", "EFY", "EAZYD", "HCAY", "HZ", "BDA".  I presumed this is what you were after from reading your comments.  When run the query returns "G", and "T", as per your original example and ignoring the first two characters as per your subsequent comment.

The query was tested using Microsoft Access 2000, so should run under VB6 with the proper references, I hope.  Of course this would be relatively easy to do with a sub, but I beleive you were after a "single querydef".  I did say this was a partial solution because I did not even consider MainTable2, as you mentioned in one of your comments.  However, you could muck around and insert a union select, or perhaps a bunch of OR statements to include both tables.  Let me know if it doesn't work, because I tested it here and it worked quite fine, with several other tests.

The query is:

SELECT TempTable.Code
FROM [SELECT TableChars.Code
FROM MainTable1, TableChars
GROUP BY TableChars.Code, Right([Maintable1].[Code], Len([MainTable1].[Code])-2)
HAVING (((Right([Maintable1].[Code],Len([maintable1].[Code])-2)) Not Like "*" & [tablechars].[code] & "*"))]. AS TempTable
GROUP BY TempTable.Code
HAVING (((Count(TempTable.Code))=DCount("code","MainTable1")));

Cheers,
Soluch.

PS: Nice question, really got the old brain thinking with the "single querydef" :)
0
 
LVL 58

Assisted Solution

by:harfang
harfang earned 332 total points
ID: 12665740
> "BUT the first 2 characters must not be taken into account"

That is easy: simply change the Like comparison in shane's suggestion, or in mine, to something like:
     Like '??*' & strLetter & '*'

The two question marks will skip the first two characters.

For the second table, with the semicolon separator, you can write a similar query, with:
    WHERE strCode Like '??*' & strLetter & '*' And strCode Not Like '*;*'
       OR strCode Like '??*' & strLetter & '*;*'
       OR strCode Like '*;??*' & strLetter & '*'

This would accept: "twice this structure separated with ';' ", not not more. I.e. it will grow even more complex with up to three times the code. In the above, the first line tests for single codes only, the second for the first code in two-code syntax and the third for the second code in two-code syntax.

You might still get that "single query" solution yet!

Good Luck
0
 
LVL 58

Expert Comment

by:harfang
ID: 12666129
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!
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12666255
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 :)
0
 

Author Comment

by:MSelect
ID: 12670354
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 ?
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 12670760
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
0
 
LVL 34

Expert Comment

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

mike
0
 

Author Comment

by:MSelect
ID: 14183918
OK, split the points to the people who helped me .... very much.
Thank's to all.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question