Hiya,

wonder if anyone can help me get a pay rise

got a database say:

name code
geff a1
geff a2
fred a1


in query i used Or for "a1" Or "a2" and it brings up codes a1 or a2, if i
use And it brings back nothing. i want to query multiple codes and reult only
all codes contained. so if query is a1 and a2 i would not want fred to be
displayed. there are thousands of codes and hundreds of names, names are on a
seperate table for reference and multiple names are used in the main matrix.

please help, ive tried for three days and got nuthin!
please email me, i need some experienced contacts.

be well

Just to clarify, it sounds like you have multiple rows in your table, with
data like the example.

So if you look for a code of "a1" AND "a2", you'll never find it! That's
because you'll only ever have ONE code in the code field (as it should be).

What is it about "geff" that makes excluding "fred" important. Is it that
"geff" has more than one record? If that's the case, run a query that
returns all the names of folks with more than one record. Then join that
query back to the table and find all the folks who are NOT on the list of
"more than one record".

By the way, if your database has more than one "fred", are they the same
person more than once, or two (or more) different people?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"doublezer00" <(E-Mail Removed)> wrote in message
news:3869782D-17BB-4E82-A67E-(E-Mail Removed)...
> Hiya,
>
> wonder if anyone can help me get a pay rise
>
> got a database say:
>
> name code
> geff a1
> geff a2
> fred a1
>
>
> in query i used Or for "a1" Or "a2" and it brings up codes a1 or a2, if i
> use And it brings back nothing. i want to query multiple codes and reult
> only
> all codes contained. so if query is a1 and a2 i would not want fred to be
> displayed. there are thousands of codes and hundreds of names, names are
> on a
> seperate table for reference and multiple names are used in the main
> matrix.
>
> please help, ive tried for three days and got nuthin!
> please email me, i need some experienced contacts.
>
> be well

Try this --
SELECT DoubleZer00.Name, DoubleZer00.Code, DoubleZer00_1.Code
FROM DoubleZer00 INNER JOIN DoubleZer00 AS DoubleZer00_1 ON DoubleZer00.Name
= DoubleZer00_1.Name
WHERE (((DoubleZer00.Code)="a1") AND ((DoubleZer00_1.Code)="a2"));


"doublezer00" wrote:

> Hiya,
>
> wonder if anyone can help me get a pay rise
>
> got a database say:
>
> name code
> geff a1
> geff a2
> fred a1
>
>
> in query i used Or for "a1" Or "a2" and it brings up codes a1 or a2, if i
> use And it brings back nothing. i want to query multiple codes and reult only
> all codes contained. so if query is a1 and a2 i would not want fred to be
> displayed. there are thousands of codes and hundreds of names, names are on a
> seperate table for reference and multiple names are used in the main matrix.
>
> please help, ive tried for three days and got nuthin!
> please email me, i need some experienced contacts.
>
> be well

You have hundred of code? Hum, not sure that you will find an easy way out
of this but for your particular exemple, you might try something like:

Select * From T as T1
Where (code = 'a1' and Exists (select * from T as T2 where T2.Code = 'a2'
and T2.name = T1.name)) Or
(code = 'a2' and Exists (select * from T as T2 where T2.Code = 'a1' and
T2.name = T1.name))

or maybe:

Select * From T as T1
Where name in (Select name from T as T2 Group By Name Having Count(*) >= 2)


However, as you have hundred of code, I think that you should learn how to
use temporary tables and the Group By and Having clauses.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"doublezer00" <(E-Mail Removed)> wrote in message
news:3869782D-17BB-4E82-A67E-(E-Mail Removed)...
> Hiya,
>
> wonder if anyone can help me get a pay rise
>
> got a database say:
>
> name code
> geff a1
> geff a2
> fred a1
>
>
> in query i used Or for "a1" Or "a2" and it brings up codes a1 or a2, if i
> use And it brings back nothing. i want to query multiple codes and reult
> only
> all codes contained. so if query is a1 and a2 i would not want fred to be
> displayed. there are thousands of codes and hundreds of names, names are
> on a
> seperate table for reference and multiple names are used in the main
> matrix.
>
> please help, ive tried for three days and got nuthin!
> please email me, i need some experienced contacts.
>
> be well

Of course, there is an error in my previous post about the second exemple
and where you have to add the tests for 'a1' and 'a2' code in the subquery
(the one with the Group By clause). This exemple was more about retrieving
all records with more than a single code. Here's the correct one:

Select * From T as T1
Where name in (Select name from T as T2 Where code = 'a1' or code = 'a2'
Group By Name Having Count(*) >= 2

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:(E-Mail Removed)...
> You have hundred of code? Hum, not sure that you will find an easy way
> out of this but for your particular exemple, you might try something like:
>
> Select * From T as T1
> Where (code = 'a1' and Exists (select * from T as T2 where T2.Code = 'a2'
> and T2.name = T1.name)) Or
> (code = 'a2' and Exists (select * from T as T2 where T2.Code = 'a1' and
> T2.name = T1.name))
>
> or maybe:
>
> Select * From T as T1
> Where name in (Select name from T as T2 Group By Name Having Count(*) >=
> 2)
>
>
> However, as you have hundred of code, I think that you should learn how to
> use temporary tables and the Group By and Having clauses.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
>
> "doublezer00" <(E-Mail Removed)> wrote in message
> news:3869782D-17BB-4E82-A67E-(E-Mail Removed)...
>> Hiya,
>>
>> wonder if anyone can help me get a pay rise
>>
>> got a database say:
>>
>> name code
>> geff a1
>> geff a2
>> fred a1
>>
>>
>> in query i used Or for "a1" Or "a2" and it brings up codes a1 or a2, if i
>> use And it brings back nothing. i want to query multiple codes and reult
>> only
>> all codes contained. so if query is a1 and a2 i would not want fred to be
>> displayed. there are thousands of codes and hundreds of names, names are
>> on a
>> seperate table for reference and multiple names are used in the main
>> matrix.
>>
>> please help, ive tried for three days and got nuthin!
>> please email me, i need some experienced contacts.
>>
>> be well
>
>