1 to many relationship between columns

[Aman]

Hi,

Considering a table/dataset of structure below:

Name, Code, Other fields
ABC, 1, ...........
ABC, 1,............
ABC, 2,............
ABC, 3,............
DEF, 1, ............
DEF, 2,............
GHI, 4, ............
JKL, 5,............
......
......

I want to get a result set which would give me all instances where
multiple "Code" exist for each "Name". The result set should look like:

ABC, 1,............
ABC, 2,............
ABC, 3,............
DEF, 1,............
DEF, 2,............
(notice since there aren't multiple codes for "GHI" and "JKL", these
are not included in the resultset)

Can someone please suggest a SQL for this. I am trying this in MS
Access but would like to have an understanding of how this should be
generally handled.

Thanks.

[inkman04]

Perhaps what you can do is run a grouping query
from the table source and add only the Name field
to it, TWICE. The first field to be set as Group By
and the second set as Count. Then, set the
criteria for the Count column to >1. This will get
only the records that have 2 or more in your dataset.

Next, create a second query with the table as it's
source also and add your fields Name & Code. Also,
whatever fields you need.
Then, add the previous query and just link the two
Name fields together.

Add the fields from the table souce to the QBE grid
and set them both to ascending. Now run the query.

You should end up with the records you require.

Have a nice day.

Regards

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try:

SELECT [Name], [code]
FROM table_name As T
GROUP BY [Name], [code]
HAVING (SELECT COUNT(*) FROM table_name
WHERE [Name] = T.[Name]) > 1

This won't work if you include the other columns (fields). You'll have
to make it a derived table:

SELECT [Name], [code], <other columns>
FROM
( SELECT [Name], [code]
FROM table_name As T
GROUP BY [Name], [code]
HAVING (SELECT COUNT(*) FROM table_name
WHERE [Name] = T.[Name]) > 1
) AS A

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+ugpIechKqOuFEgEQILXwCdF+6eyHIZkTs0xd8Zg+OR9W QNL9EAoMcQ
YRnBenqshV/JIPEoY3X5r4hz
=P43Z
-----END PGP SIGNATURE-----
Aman wrote:

Hi,

Considering a table/dataset of structure below:

Name, Code, Other fields
ABC, 1, ...........
ABC, 1,............
ABC, 2,............
ABC, 3,............
DEF, 1, ............
DEF, 2,............
GHI, 4, ............
JKL, 5,............
.....
.....

I want to get a result set which would give me all instances where
multiple "Code" exist for each "Name". The result set should look like:

ABC, 1,............
ABC, 2,............
ABC, 3,............
DEF, 1,............
DEF, 2,............
(notice since there aren't multiple codes for "GHI" and "JKL", these
are not included in the resultset)

Can someone please suggest a SQL for this. I am trying this in MS
Access but would like to have an understanding of how this should be
generally handled.

[Aman]

Thanks for your replies guys. It did give me some ideas on how to
approach this. Here's what worked for me in MS Access:

SELECT DISTINCTROW tbl_nm.name, tbl_nm.code
FROM tbl_nm, [select distinct name, count(*)
from (
SELECT DISTINCTROW tbl_nm.Name,
tbl_nm.count, Count(*)
FROM tbl_nm
GROUP BY tbl_nm.Name, tbl_nm.code)
group by name
having count(*) > 1]. AS abc
WHERE tbl_nm.name = abc.name
GROUP BY tbl_nm.name, tbl_nm.code

The solution you had proposed Foster did not exclude the unique
combinations of Name and Code, and as I menntioned I did not want them.
I just wanted the names and codes for cases where multiple codes
existed for a name.

Thanks alot!!