Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

And Or Queries 2

Discussion in 'Information Technology' started by doublezer00, Jul 28, 2006.

Tags:
  1. doublezer00

    doublezer00
    Expand Collapse
    Guest

    Hiya, and thanks to those who replied my post.
    Unfortuantly i was not detailed enough and the responces were not suited.
    okay here goes:

    table eg.

    name code
    geff a1
    geff a2
    fred a1

    there are 120 names and thousands of codes, i need to parameter query the
    code that shows eg. a1 And a2, which would not display fred. Eg. dialogue
    what code you want to search, user specs a1, then another dialogue for the
    second: a2 (works sort of with Or expr). i cant do this by name as the name
    may appear hundreds of times, eg, lets say the codes go to b1 b2 and so on. i
    need to specify the code and find out who owns it, its to do with transport.
    geff may appear 1000s of times with extra codes b1, b2 c1000, c1001.
    selecting multiple names would result in far to many results. The Or command
    almost works but i get either names with the codes stated in the parameter.

    i have used multiple names as the only other way would be to have field
    names with all the names or codes, too big...

    please help. i need to show IT it can be done;)
    (if it cant i'll just say i didnt want that to happen anyway:)
     
  2. Loading...

    Similar Threads Forum Date
    Importance of Hukamnama - Some Queries Sikh Sikhi Sikhism Jun 14, 2009
    Queries History of Sikhism Oct 26, 2006

  3. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    A couple of questions.
    How will you input the codes you are looking for or do you want it to
    automatically detect your code pairs?
    Is your code pairs always the code and code incremented by one?
    b1, b2 c1000, c1001 Zed1, Zed2,

    "doublezer00" wrote:

    > Hiya, and thanks to those who replied my post.
    > Unfortuantly i was not detailed enough and the responces were not suited.
    > okay here goes:
    >
    > table eg.
    >
    > name code
    > geff a1
    > geff a2
    > fred a1
    >
    > there are 120 names and thousands of codes, i need to parameter query the
    > code that shows eg. a1 And a2, which would not display fred. Eg. dialogue
    > what code you want to search, user specs a1, then another dialogue for the
    > second: a2 (works sort of with Or expr). i cant do this by name as the name
    > may appear hundreds of times, eg, lets say the codes go to b1 b2 and so on. i
    > need to specify the code and find out who owns it, its to do with transport.
    > geff may appear 1000s of times with extra codes b1, b2 c1000, c1001.
    > selecting multiple names would result in far to many results. The Or command
    > almost works but i get either names with the codes stated in the parameter.
    >
    > i have used multiple names as the only other way would be to have field
    > names with all the names or codes, too big...
    >
    > please help. i need to show IT it can be done;)
    > (if it cant i'll just say i didnt want that to happen anyway:)
    >
    >
     
  4. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    SELECT DoubleZer00.Name, DoubleZer00.Code, DoubleZer00_1.Code
    FROM DoubleZer00 INNER JOIN DoubleZer00 AS DoubleZer00_1 ON DoubleZer00.Name
    = DoubleZer00_1.Name
    WHERE
    (((DoubleZer00_1.Code)=Left([DoubleZer00].
    Code:
    ,Len([DoubleZer00].[Code])-1)
    & Val(Right([DoubleZer00].[Code],1))+"1"));
    
    
    "KARL DEWEY" wrote:
    [color=blue]
    > A couple of questions.
    > How will you input the codes you are looking for or do you want it to
    > automatically detect your code pairs?
    > Is your code pairs always the code and code incremented by one?
    >             b1, b2          c1000, c1001          Zed1, Zed2,
    >
    > "doublezer00" wrote:
    >[color=green]
    > > Hiya, and thanks to those who replied my post.
    > > Unfortuantly i was not detailed enough and the responces were not suited.
    > > okay here goes:
    > >
    > > table eg.
    > >
    > > name   code
    > > geff      a1
    > > geff      a2
    > > fred      a1
    > >
    > > there are 120 names and thousands of codes, i need to parameter query the
    > > code that shows eg. a1 And a2, which would not display fred. Eg. dialogue
    > > what code you want to search, user specs a1, then another dialogue for the
    > > second: a2 (works sort of with Or expr). i cant do this by name as the name
    > > may appear hundreds of times, eg, lets say the codes go to b1 b2 and so on. i
    > > need to specify the code and find out who owns it, its to do with transport.
    > > geff may appear 1000s of times with extra codes b1, b2 c1000, c1001.
    > > selecting multiple names would result in far to many results. The Or command
    > > almost works but i get either names with the codes stated in the parameter.
    > >
    > > i have used multiple names as the only other way would be to have field
    > > names with all the names or codes, too big...
    > >
    > > please help. i need to show IT it can be done;)
    > > (if it cant i'll just say i didnt want that to happen anyway:)
    > >
    > >[/color][/color]
     
  5. doublezer00

    doublezer00
    Expand Collapse
    Guest

    its random codes. some names might have many different ones.
    will this work? and will it give an input dialogue? need upto 5 inputs
    matching all the codes, and showing who owns which codes,

    thanks:)

    "KARL DEWEY" wrote:

    >
    > SELECT DoubleZer00.Name, DoubleZer00.Code, DoubleZer00_1.Code
    > FROM DoubleZer00 INNER JOIN DoubleZer00 AS DoubleZer00_1 ON DoubleZer00.Name
    > = DoubleZer00_1.Name
    > WHERE
    > (((DoubleZer00_1.Code)=Left([DoubleZer00].
    Code:
    ,Len([DoubleZer00].[Code])-1)
    > & Val(Right([DoubleZer00].[Code],1))+"1"));
    >
    >
    > "KARL DEWEY" wrote:
    >[color=green]
    > > A couple of questions.
    > > How will you input the codes you are looking for or do you want it to
    > > automatically detect your code pairs?
    > > Is your code pairs always the code and code incremented by one?
    > >             b1, b2          c1000, c1001          Zed1, Zed2,
    > >
    > > "doublezer00" wrote:
    > >[color=darkred]
    > > > Hiya, and thanks to those who replied my post.
    > > > Unfortuantly i was not detailed enough and the responces were not suited.
    > > > okay here goes:
    > > >
    > > > table eg.
    > > >
    > > > name   code
    > > > geff      a1
    > > > geff      a2
    > > > fred      a1
    > > >
    > > > there are 120 names and thousands of codes, i need to parameter query the
    > > > code that shows eg. a1 And a2, which would not display fred. Eg. dialogue
    > > > what code you want to search, user specs a1, then another dialogue for the
    > > > second: a2 (works sort of with Or expr). i cant do this by name as the name
    > > > may appear hundreds of times, eg, lets say the codes go to b1 b2 and so on. i
    > > > need to specify the code and find out who owns it, its to do with transport.
    > > > geff may appear 1000s of times with extra codes b1, b2 c1000, c1001.
    > > > selecting multiple names would result in far to many results. The Or command
    > > > almost works but i get either names with the codes stated in the parameter.
    > > >
    > > > i have used multiple names as the only other way would be to have field
    > > > names with all the names or codes, too big...
    > > >
    > > > please help. i need to show IT it can be done;)
    > > > (if it cant i'll just say i didnt want that to happen anyway:)
    > > >
    > > >[/color][/color][/color]
     
  6. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    Can you give a better example of data?

    How will you input the codes you are looking for?

    Will you want it to automatically detect your code pairs?

    Are the codes always in pairs (two in a combination) or a sequence of codes?


    "doublezer00" wrote:

    > its random codes. some names might have many different ones.
    > will this work? and will it give an input dialogue? need upto 5 inputs
    > matching all the codes, and showing who owns which codes,
    >
    > thanks:)
    >
    > "KARL DEWEY" wrote:
    >
    > >
    > > SELECT DoubleZer00.Name, DoubleZer00.Code, DoubleZer00_1.Code
    > > FROM DoubleZer00 INNER JOIN DoubleZer00 AS DoubleZer00_1 ON DoubleZer00.Name
    > > = DoubleZer00_1.Name
    > > WHERE
    > > (((DoubleZer00_1.Code)=Left([DoubleZer00].
    Code:
    ,Len([DoubleZer00].[Code])-1)
    > > & Val(Right([DoubleZer00].[Code],1))+"1"));
    > >
    > >
    > > "KARL DEWEY" wrote:
    > >[color=darkred]
    > > > A couple of questions.
    > > > How will you input the codes you are looking for or do you want it to
    > > > automatically detect your code pairs?
    > > > Is your code pairs always the code and code incremented by one?
    > > >             b1, b2          c1000, c1001          Zed1, Zed2,
    > > >
    > > > "doublezer00" wrote:
    > > >
    > > > > Hiya, and thanks to those who replied my post.
    > > > > Unfortuantly i was not detailed enough and the responces were not suited.
    > > > > okay here goes:
    > > > >
    > > > > table eg.
    > > > >
    > > > > name   code
    > > > > geff      a1
    > > > > geff      a2
    > > > > fred      a1
    > > > >
    > > > > there are 120 names and thousands of codes, i need to parameter query the
    > > > > code that shows eg. a1 And a2, which would not display fred. Eg. dialogue
    > > > > what code you want to search, user specs a1, then another dialogue for the
    > > > > second: a2 (works sort of with Or expr). i cant do this by name as the name
    > > > > may appear hundreds of times, eg, lets say the codes go to b1 b2 and so on. i
    > > > > need to specify the code and find out who owns it, its to do with transport.
    > > > > geff may appear 1000s of times with extra codes b1, b2 c1000, c1001.
    > > > > selecting multiple names would result in far to many results. The Or command
    > > > > almost works but i get either names with the codes stated in the parameter.
    > > > >
    > > > > i have used multiple names as the only other way would be to have field
    > > > > names with all the names or codes, too big...
    > > > >
    > > > > please help. i need to show IT it can be done;)
    > > > > (if it cant i'll just say i didnt want that to happen anyway:)
    > > > >
    > > > >[/color][/color][/color]
     
  7. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    I think this might do it. When prompted for the third entry you can enter
    data or just press ENTER if you only have two map codes.

    SELECT DoubleZer00.intial, DoubleZer00.surname, DoubleZer00.[map code],
    DoubleZer00_1.[map code], DoubleZer00_2.[map code]
    FROM (DoubleZer00 INNER JOIN DoubleZer00 AS DoubleZer00_1 ON
    (DoubleZer00.surname = DoubleZer00_1.surname) AND (DoubleZer00.intial =
    DoubleZer00_1.intial)) INNER JOIN DoubleZer00 AS DoubleZer00_2 ON
    (DoubleZer00.surname = DoubleZer00_2.surname) AND (DoubleZer00.intial =
    DoubleZer00_2.intial)
    WHERE (((DoubleZer00.[map code])=[enter route 1]) AND ((DoubleZer00_1.[map
    code])=[enter route 2]) AND ((DoubleZer00_2.[map code]) Like IIf([enter route
    3] Is Null,"*",[enter route 3])));


    "doublezer00" wrote:

    > okay the tables are like so:
    >
    > intial surname map code
    > g davis mmo1
    > g davis mmo2
    > g davis gyh14
    > g davis poiu11
    > a smith mmo1
    > a smith mmo2
    > a smith adwe23
    >
    > query criteria: [enter route 1]Or[enter route 2] Or [enter route 3]
    >
    > parameters: mmo1 mmo2 gyh14
    >
    > results are:
    >
    > intial surname map code
    > g davis mmo1
    > g davis mmo2
    > g davis gyh14
    > a smith mmo1
    > a smith mmo2
    >
    > i need just g davis as he only knows all three parameters, And does not work
    > cos the way the data is laid out, only one column for map codes, reason: cant
    > turn fields into map codes, ie. enter yes to drivers who know route on each
    > field as there are to many and you would still not be able to query with one
    > field for all routes?
    >
    > its a knightmare, im not all that good at VB so could you let me know what
    > some of the expression are, also for sql. whether its select table or field
    > etc..
    >
    > cheers matey
    >
    >
    > "KARL DEWEY" wrote:
    >
    > > Can you give a better example of data?
    > >
    > > How will you input the codes you are looking for?
    > >
    > > Will you want it to automatically detect your code pairs?
    > >
    > > Are the codes always in pairs (two in a combination) or a sequence of codes?
    > >
    > >
    > > "doublezer00" wrote:
    > >
    > > > its random codes. some names might have many different ones.
    > > > will this work? and will it give an input dialogue? need upto 5 inputs
    > > > matching all the codes, and showing who owns which codes,
    > > >
    > > > thanks:)
    > > >
    > > > "KARL DEWEY" wrote:
    > > >
    > > > >
    > > > > SELECT DoubleZer00.Name, DoubleZer00.Code, DoubleZer00_1.Code
    > > > > FROM DoubleZer00 INNER JOIN DoubleZer00 AS DoubleZer00_1 ON DoubleZer00.Name
    > > > > = DoubleZer00_1.Name
    > > > > WHERE
    > > > > (((DoubleZer00_1.Code)=Left([DoubleZer00].
    Code:
    ,Len([DoubleZer00].[Code])-1)
    > > > > & Val(Right([DoubleZer00].[Code],1))+"1"));
    > > > >
    > > > >
    > > > > "KARL DEWEY" wrote:
    > > > >
    > > > > > A couple of questions.
    > > > > > How will you input the codes you are looking for or do you want it to
    > > > > > automatically detect your code pairs?
    > > > > > Is your code pairs always the code and code incremented by one?
    > > > > >             b1, b2          c1000, c1001          Zed1, Zed2,
    > > > > >
    > > > > > "doublezer00" wrote:
    > > > > >
    > > > > > > Hiya, and thanks to those who replied my post.
    > > > > > > Unfortuantly i was not detailed enough and the responces were not suited.
    > > > > > > okay here goes:
    > > > > > >
    > > > > > > table eg.
    > > > > > >
    > > > > > > name   code
    > > > > > > geff      a1
    > > > > > > geff      a2
    > > > > > > fred      a1
    > > > > > >
    > > > > > > there are 120 names and thousands of codes, i need to parameter query the
    > > > > > > code that shows eg. a1 And a2, which would not display fred. Eg. dialogue
    > > > > > > what code you want to search, user specs a1, then another dialogue for the
    > > > > > > second: a2 (works sort of with Or expr). i cant do this by name as the name
    > > > > > > may appear hundreds of times, eg, lets say the codes go to b1 b2 and so on. i
    > > > > > > need to specify the code and find out who owns it, its to do with transport.
    > > > > > > geff may appear 1000s of times with extra codes b1, b2 c1000, c1001.
    > > > > > > selecting multiple names would result in far to many results. The Or command
    > > > > > > almost works but i get either names with the codes stated in the parameter.
    > > > > > >
    > > > > > > i have used multiple names as the only other way would be to have field
    > > > > > > names with all the names or codes, too big...
    > > > > > >
    > > > > > > please help. i need to show IT it can be done;)
    > > > > > > (if it cant i'll just say i didnt want that to happen anyway:)
    > > > > > >
    > > > > > >[/color][/color][/color]
     
  8. doublezer00

    doublezer00
    Expand Collapse
    Guest

    okay the tables are like so:

    intial surname map code
    g davis mmo1
    g davis mmo2
    g davis gyh14
    g davis poiu11
    a smith mmo1
    a smith mmo2
    a smith adwe23

    query criteria: [enter route 1]Or[enter route 2] Or [enter route 3]

    parameters: mmo1 mmo2 gyh14

    results are:

    intial surname map code
    g davis mmo1
    g davis mmo2
    g davis gyh14
    a smith mmo1
    a smith mmo2

    i need just g davis as he only knows all three parameters, And does not work
    cos the way the data is laid out, only one column for map codes, reason: cant
    turn fields into map codes, ie. enter yes to drivers who know route on each
    field as there are to many and you would still not be able to query with one
    field for all routes?

    its a knightmare, im not all that good at VB so could you let me know what
    some of the expression are, also for sql. whether its select table or field
    etc..

    cheers matey


    "KARL DEWEY" wrote:

    > Can you give a better example of data?
    >
    > How will you input the codes you are looking for?
    >
    > Will you want it to automatically detect your code pairs?
    >
    > Are the codes always in pairs (two in a combination) or a sequence of codes?
    >
    >
    > "doublezer00" wrote:
    >
    > > its random codes. some names might have many different ones.
    > > will this work? and will it give an input dialogue? need upto 5 inputs
    > > matching all the codes, and showing who owns which codes,
    > >
    > > thanks:)
    > >
    > > "KARL DEWEY" wrote:
    > >
    > > >
    > > > SELECT DoubleZer00.Name, DoubleZer00.Code, DoubleZer00_1.Code
    > > > FROM DoubleZer00 INNER JOIN DoubleZer00 AS DoubleZer00_1 ON DoubleZer00.Name
    > > > = DoubleZer00_1.Name
    > > > WHERE
    > > > (((DoubleZer00_1.Code)=Left([DoubleZer00].
    Code:
    ,Len([DoubleZer00].[Code])-1)
    > > > & Val(Right([DoubleZer00].[Code],1))+"1"));
    > > >
    > > >
    > > > "KARL DEWEY" wrote:
    > > >
    > > > > A couple of questions.
    > > > > How will you input the codes you are looking for or do you want it to
    > > > > automatically detect your code pairs?
    > > > > Is your code pairs always the code and code incremented by one?
    > > > >             b1, b2          c1000, c1001          Zed1, Zed2,
    > > > >
    > > > > "doublezer00" wrote:
    > > > >
    > > > > > Hiya, and thanks to those who replied my post.
    > > > > > Unfortuantly i was not detailed enough and the responces were not suited.
    > > > > > okay here goes:
    > > > > >
    > > > > > table eg.
    > > > > >
    > > > > > name   code
    > > > > > geff      a1
    > > > > > geff      a2
    > > > > > fred      a1
    > > > > >
    > > > > > there are 120 names and thousands of codes, i need to parameter query the
    > > > > > code that shows eg. a1 And a2, which would not display fred. Eg. dialogue
    > > > > > what code you want to search, user specs a1, then another dialogue for the
    > > > > > second: a2 (works sort of with Or expr). i cant do this by name as the name
    > > > > > may appear hundreds of times, eg, lets say the codes go to b1 b2 and so on. i
    > > > > > need to specify the code and find out who owns it, its to do with transport.
    > > > > > geff may appear 1000s of times with extra codes b1, b2 c1000, c1001.
    > > > > > selecting multiple names would result in far to many results. The Or command
    > > > > > almost works but i get either names with the codes stated in the parameter.
    > > > > >
    > > > > > i have used multiple names as the only other way would be to have field
    > > > > > names with all the names or codes, too big...
    > > > > >
    > > > > > please help. i need to show IT it can be done;)
    > > > > > (if it cant i'll just say i didnt want that to happen anyway:)
    > > > > >
    > > > > >[/color][/color][/color]
     

Share This Page