Access Help

[MarkyMark]

I'm trying to pull only the names that has more than one channel from a
table has the below data with the other information about that name.

Acct Zip Name Channel Address
1 222 A1 B2 124A Dr
2 323 A1 B3 124A Dr
3 324 A2 B4 253 B Dr
4 998 A4 B5 356 C Dr
5 387 A2 B8 253 B Dr
6 656 A6 B8 2356 CDr
8 696 A9 B5 235 J Dr

I tried distinct but it didn't work

 

[David F Cox]

from my head, untested, something along the lines of(using your field
names)::

SELECT [MyName], COUNT(Channel) FROM [Mytable] GROUP BY [MyName] HAVING
COUNT(Channel]) > 1

 

[S.Clark]

Select Name, Count(Channel) FROM Tablename GROUP BY Name WHERE
Count(Channel) > 1


--
Steve Clark, Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[Guest]

Try this --
SELECT MarkyMark.Name, Count(MarkyMark.channel) AS CountOfchannel
FROM MarkyMark
GROUP BY MarkyMark.Name
HAVING (((Count(MarkyMark.channel))>1));

 

[Granny Spitz via AccessMonster.com]

I'm trying to pull only the names that has more than one channel from a
table has the below data with the other information about that name.

Because you specifically mentioned "with the other information about that
name," I'm going to interpret your question as you want the names that have 2
or more channels *and* you want the other information (columns) too. Since
Name is a reserved word, I changed it to ChName in the following example:

SELECT N.Acct, N.Zip, N.ChName, N.Channel, N.Address
FROM tblNameChannels AS NC INNER JOIN
tblNameChannels AS N ON NC.ChName = N.ChName
WHERE (NC.Channel <> N.Channel);

 

[MarkyMark]

ok i did the below. i know i didn't do it right could you please review
it and let me know how am I supposed to fix it

SELECT TestyTest.Name, TestyTest.Channel, TestyTest.Loan,
TestyTest.SSN, TestyTest.Field5
FROM TestyTest as Name INNER JOIN
Channel AS N ON Name = ChName
Where (TestyTest.Channel<>Channel);

 

[Granny Spitz via AccessMonster.com]

could you please review
it and let me know how am I supposed to fix it

Don't use "name" as a table name, column name or alias because "name" is a
reserved word. If you use reserved words as names you'll be fighting with
bugs. Don't use the same name for a column as you used for the table because
you'll be fighting with bugs some more.

If your table is named TestyTest and it contains ChName, Channel, Loan, SSN,
and Field5 as column names, you can use this:

SELECT TT.ChName, TT.Channel, TT.Loan,
TT.SSN, TT.Field5
FROM TestyTest as TT INNER JOIN
TestyTest AS N ON TT.ChName = N.ChName
Where (TT.Channel<>N.Channel);

BTW for security reasons you should never store anyone's SSN in an Access
database. Anyone can read it and use your database to commit fraud and you
(or your company) are legally liable.

 

[MarkyMark]

It kept requesting marameter value for TT.ChName?

SELECT TT.ChName, TT.Channel, TT.Loan,
TT.SSN, TT.Field5
FROM TestyTest as TT INNER JOIN
TestyTest AS N ON TT.ChName = N.ChName
Where (TT.Channel<>N.Channel);

 

[David F Cox]

Didy ou change the field name in TestyTest from Name to ChName as
suggested?

It kept requesting marameter value for TT.ChName?

SELECT TT.ChName, TT.Channel, TT.Loan,
TT.SSN, TT.Field5
FROM TestyTest as TT INNER JOIN
TestyTest AS N ON TT.ChName = N.ChName
Where (TT.Channel<>N.Channel);

 

[Granny Spitz via AccessMonster.com]

It kept requesting marameter value for TT.ChName?

Open your table in design view. If you find a column named "Name" say 10
times out loud "I hate fighting this bug and I will never use reserved words
as names again," then change Name to ChName and save the table.

 

[MarkyMark]

awesomeeeeeeeeeeeeeeeeeeeeeeeeeeee and yes it's working perfect now. I
feel kind slow my bad

Didy ou change the field name in TestyTest from Name to ChName as
suggested?