Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2002
    Posts
    5

    Cool Unanswered: 'mismatches' results needed

    Hi!,

    This is a bit unusual (and I can't think of any other uses except for my own particular database)...

    I have an Access table with four columns:

    Choice1 : Choice2 : Choice3 : Choice4
    A : B : C : D
    A : B : C : E
    A : B : C : F
    A : B : C : G
    A : C : C : E

    If I search (via 4 drop-down lists) on:

    A : B : C : E

    I would like to get back as a result all '1 mismatches' i.e. where the results mismatch by one (either up or down).
    In the above case, it would be:

    A : B : C : D
    A : B : C : F and
    A : C : C : E


    My original thoughts for the SQL query were to convert each of the letters to numerical values and use SUM, then use plus or minus one each time to find the mismatches, but there would potentially be other combinations of table data that gave the same summed values (and thus wrong results)

    Any thoughts anyone...?

    Cheers

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    You were on the right track

    Chr(Asc(Forms!FormName!ComboBox1)+1)
    Or
    Chr(Asc(Forms!FormName!ComboBox1)-1)

    SELECT TableName.Choice1, TableName.Choice2, TableName.Choice3, TableName.Choice4
    FROM TableName
    WHERE (((TableName.Choice1)=Chr(Asc(Forms!FormName!Combo Box1)+1) Or (TableName.Choice1)=Chr(Asc(Forms!FormName!ComboBo x1)-1))) OR (((TableName.Choice2)=Chr(Asc(Forms!FormName!Combo Box2)+1) Or (TableName.Choice2)=Chr(Asc(Forms!FormName!ComboBo x2)-1))) OR (((TableName.Choice3)=Chr(Asc(Forms!FormName!Combo Box3)+1) Or (TableName.Choice3)=Chr(Asc(Forms!FormName!ComboBo x3)-1))) OR (((TableName.Choice4)=Chr(Asc(Forms!FormName!Combo Box4)+1) Or (TableName.Choice4)=Chr(Asc(Forms!FormName!ComboBo x4)-1)));

    Matt

  3. #3
    Join Date
    Oct 2002
    Posts
    5

    Re: 'mismatches' results needed

    Cheers Matt, I'll give it a whirl and post the code I used when I've got it down cold!!


    Originally posted by chickenlover
    Hi!,

    This is a bit unusual (and I can't think of any other uses except for my own particular database)...

    I have an Access table with four columns:

    Choice1 : Choice2 : Choice3 : Choice4
    A : B : C : D
    A : B : C : E
    A : B : C : F
    A : B : C : G
    A : C : C : E

    If I search (via 4 drop-down lists) on:

    A : B : C : E

    I would like to get back as a result all '1 mismatches' i.e. where the results mismatch by one (either up or down).
    In the above case, it would be:

    A : B : C : D
    A : B : C : F and
    A : C : C : E


    My original thoughts for the SQL query were to convert each of the letters to numerical values and use SUM, then use plus or minus one each time to find the mismatches, but there would potentially be other combinations of table data that gave the same summed values (and thus wrong results)

    Any thoughts anyone...?

    Cheers

  4. #4
    Join Date
    Nov 2001
    Posts
    336
    Hi chickenlover,

    Try SQL below. I hope, using function ABS() is a nice solution to your request.

    SELECT Choice1, Choice2, Choice3, Choice4 FROM TableName
    WHERE (Abs(Asc(Choice1)-Asc(Forms!FormName!ComboBox1)) +
    Abs(Asc(Choice2)-Asc(Forms!FormName!ComboBox2)) +
    Abs(Asc(Choice3)-Asc(Forms!FormName!ComboBox3)) +
    Abs(Asc(Choice4)-Asc(Forms!FormName!ComboBox4))) = 1;


    Igor

    P.S. Didn't have a chance to test it. If there is any problem, I'll fix it.

  5. #5
    Join Date
    Oct 2002
    Posts
    5
    Thanks Matt and Igor,
    I've tested both codes and they both work really well. The ABS() method does make it easier to change from 1 mismatch to 2 mismatches (i.e. just one digit is changed).

    Is there any way I can have numbers AND letters in the tables (and thus the comboboxes)? The code will work with integers from 1-9 but more than one digit e.g. 10, 11, 12 will be ignored (because it revolves around Ascii codes).

    Sorry to chuck a spanner into the works, I'd though I'd start simple and work up but I've got stuck again!!)

    E.g.
    The table now contains:

    Choice1 : Choice2 : Choice3 : Choice4
    A : B : C : A
    A : B : C : 09
    A : B : C : 10
    A : B : C : 11
    A : B : C : 12
    A : C : C : 11


    If I search on:

    A : B : C : 11

    I would like to get back as a result 1 mismatches:
    (note that Choice4 now contains letters AND numbers which can be anywhere between 05 and 95)

    A : B : C : 10
    A : B : C : 12
    A : C : C : 11 (currently it only returns this one)

    Would the Asc function change to AscB in Visual Basic? I need to eventually convert it so that my Access 2000 database can be interogated by Active Server Pages (VbScript) 3.0

    I had thought of using an If statement to check if a number is selected first, and using that to construct part of the query.


    Thanks again

  6. #6
    Join Date
    Aug 2002
    Location
    Singapore
    Posts
    91
    In fact, Asc only return the ascii code for the first letter. Asc("10") = Asc("11") = Asc("1")...

    How do you define the relationship between letter and number?
    Assumming letter and number is not to be compared, and your number is not zero, and your distance/difference between 2 compared items <= 7, you can use the following code:

    ... + Abs(Iif(Val(Choice4)=0 Or Val(Forms!FormName!ComboBox4)=0, Asc(Choice4)-Asc(Forms!FormName!ComboBox4), Val(Choice4)-Val(Forms!FormName!ComboBox4))) = 1

    If you change from 1 to 2 mismatches...,
    do you consider A B C 08 with A C C 07 as 2 mismatches?
    How about A B C 08 with A B C 06, or A D C 08?

  7. #7
    Join Date
    Oct 2002
    Posts
    5
    Hi Djoko,

    I've just tested this and it works just like a dream. Now that you've written it out for me, I can see how it breaks down too.

    You were right in your assumptions (letters not equal to numbers, distance less than 7.

    Works well with 2 mismatches as well!


    Thank you very much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •