Display rows where column values are the same

[tomseattle]

Hello, I am a complete newbie when it comes to access so I really don't know how to do this. I would like to take a database that has the following fields and get the results I have shown here.

name	age	weight	type	sample_num
tom	30	200	1	1
tom	30		2	1
jen	25	120	1	2
jen	25		2	2
mary	21	115	1	3
beth	20	110	1	4

Result

name	age	weight	Sample_num
tom	30	200	1
jen	25	120	2

I want to search the sample_num and find equal numbers. There has to be both a type 1 and a type 2. Can I even do this with a query?

Thanks for the help

[Dal Jeanis]

Assuming your names are unique, you could use either this -

SELECT T1.name, T1.age, T1.weight, T1.sample_num
FROM MyTable as T1, MyTable as T2
WHERE T1.Name = T2.Name
AND T1.Type = '1' 
AND T2.Type = '2';

or this -

SELECT T1.name, T1.age, T1.weight, T1.sample_num
FROM MyTable as T1
WHERE T1.Type = '1' 
AND T1.Name IN 
   (SELECT T2.Name
    FROM MyTable as T2
    WHERE T2.Type = '2' 
   );

or this -

SELECT T1.name, T1.age, T1.weight, T1.sample_num
FROM MyTable as T1 
     INNER JOIN 
     MyTable as T2
     ON T1.Name = T2.Name
WHERE T1.Type = '1' 
AND T2.Type = '2';

You should take the quotes off the '1' and '2' if they're numeric types.