Frequent Data in Tables

[amejr999]

Hi everyone,

I'm working on a project, and I'm a bit confused about how to make a query.
I'm new to Access, so please forgive me if this is obvious. Basically, I
have a table containing a list of ID numbers and a list of names. Most of
the ID numbers of more than one name associated with them. What I'm trying
to find is how to create a list of the movies that are most highly
correlated. In other words, I'm looking for the names that are most often
listed together under the same ID numbers.

I'd very much appreciate any help that you could provide (but please make it
as simple as possible for my sake )

Thanks!

 

[MGFoster]

Hi everyone,

I'm working on a project, and I'm a bit confused about how to make a query.
I'm new to Access, so please forgive me if this is obvious. Basically, I
have a table containing a list of ID numbers and a list of names. Most of
the ID numbers of more than one name associated with them. What I'm trying
to find is how to create a list of the movies that are most highly
correlated. In other words, I'm looking for the names that are most often
listed together under the same ID numbers.

I'd very much appreciate any help that you could provide (but please make it
as simple as possible for my sake )

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

Use GROUP BY and ORDER BY:

SELECT ID, [name], COUNT(*) As CountOf
FROM table_name
GROUP BY ID, [name]
ORDER BY 3 DESC

The ORDER BY 3 DESC means sort by the 3rd column in descending order.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSasaPYechKqOuFEgEQJuSACgsWJg+LFiusyfmnsOWQ49URF+YS4AoOY3
3y3TNF1IZsZKfwsmXwcUpbyB
=WRJt
-----END PGP SIGNATURE-----

 

[amejr999]

Sorry, I'm completely confused (told you I was new at this)! Just to
clarify, the field IDnumber is the first column of the table, and the field
favorite is the 2nd column (favorite is text, not numerical). The table is
named favorites.

I'm trying to run the following:

SELECT ID, favorite, COUNT(*) As CountOf
FROM favorites
GROUP BY ID, IDnumber
ORDER BY 3 DESC

but it's returning a message that "You have tried to execute a query that
does not include the specified expression "favorite" as part of an aggregate
function." I've played around with the query a bit, but it keeps returning a
similar message. Maybe part of the issue is that there is no 3rd column?

Any ideas? Thanks in advance.

 

[MGFoster]

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

You'll notice that the GROUP BY clause in my query has the same names as
the SELECT clause, excluding the aggregate COUNT(*) function. This
means that your query should have the same names in the GROUP BY clause
as are in the SELECT clause, excluding any aggregate functions (COUNT(),
MAX(), MIN(), etc.). Using the column and table names you have
supplied, the query would look like this:

SELECT IDnumber, favorite, COUNT(*) As CountOf
FROM favorites
GROUP BY IDnumber, favorite
ORDER BY 3 DESC

If you get an error on the ORDER BY clause you can change it to:

ORDER BY COUNT(*) DESC

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSauqAoechKqOuFEgEQLyfQCeJK3Lxu42VYAq8aGfrLXgMl3YdboAoOC1
IoE2LF4i107eoa5e+H8yDypu
=c4fC
-----END PGP SIGNATURE-----

Sorry, I'm completely confused (told you I was new at this)! Just to
clarify, the field IDnumber is the first column of the table, and the field
favorite is the 2nd column (favorite is text, not numerical). The table is
named favorites.

I'm trying to run the following:

SELECT ID, favorite, COUNT(*) As CountOf
FROM favorites
GROUP BY ID, IDnumber
ORDER BY 3 DESC

but it's returning a message that "You have tried to execute a query that
does not include the specified expression "favorite" as part of an aggregate
function." I've played around with the query a bit, but it keeps returning a
similar message. Maybe part of the issue is that there is no 3rd column?

Any ideas? Thanks in advance.

Use GROUP BY and ORDER BY:

SELECT ID, [name], COUNT(*) As CountOf
FROM table_name
GROUP BY ID, [name]
ORDER BY 3 DESC

The ORDER BY 3 DESC means sort by the 3rd column in descending order.