And Or Queries 2

G

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:)
 
G

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,
 
G

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"));
 
G

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 said:
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"));


[QUOTE="KARL DEWEY"]
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,
[/QUOTE][/QUOTE]
Click to expand...
 
G

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 said:
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 said:
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"));


[QUOTE="KARL DEWEY"]
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,

:

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:)
[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
 
G

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 said:
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 said:
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 said:
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:)

:


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"));


:

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,

:

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:)
[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
G

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 said:
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 said:
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 said:
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"));


:

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,

:

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:)
[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

And and Or Queries 4
Count cells containing text, multiple criteria 2
how do i get 1 table to autofill info from another 3
Excel ALERT a duplicate of the match of two cells in two different columns... please 0
Excel If a Cell Contains a Certain Text (From Another Table) 0
Naming non-contigent cells with vba 5
Help to create a macro 2
Vlookup and Indirect 0

Top