Return records where count is > 1 (?)

[John]

I've got a single table I need to query to return records that have no
"related records."

Table dataset example:
1. John, Biology
2. Dave, Math
3. Susan, Biology
4. Betty, Sociology

The query should return this dataset:
2. Dave, Math
4. Bett, Sociology

Thanks for any suggestions...

[PC Datasheet]

Create a query. Pull down first name into the first field of the query grid.
Pull down class into the second field. Pull down class again into the third
field. Change the query to a totals query by clicking on the Sigma button on the
toolbar at the top of the screen. In the third field (class) where it says Group
By, use the drop down list and change it to Count. Enter =1 in the criteria of
the third field.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"John" <so*********@hotmail.com> wrote in message
news:90**************************@posting.google.c om...

I've got a single table I need to query to return records that have no
"related records."

Table dataset example:
1. John, Biology
2. Dave, Math
3. Susan, Biology
4. Betty, Sociology

The query should return this dataset:
2. Dave, Math
4. Bett, Sociology

Thanks for any suggestions...

[Yannick Turgeon]

Your request is far from being crystal clear! Your table structure is not
specified and the message subject seems to be the opposite of what I
understand in your message.

If I understand correctly what you need, this should work (change field and
table name for yours):

SELECT Name, Science
FROM YourTable
GROUP BY Science
HAVING COUNT(*) = 1
"John" <so*********@hotmail.com> wrote in message
news:90**************************@posting.google.c om...

I've got a single table I need to query to return records that have no
"related records."

Table dataset example:
1. John, Biology
2. Dave, Math
3. Susan, Biology
4. Betty, Sociology

The query should return this dataset:
2. Dave, Math
4. Bett, Sociology

Thanks for any suggestions...

[John]

The only way I know of to help clear up my question is by giving an
example of what I have. (I know the table is far from being
normalized)

[table]
tbl_Classes:
Student_Name
Class_Name

[table records]
1. John, Biology
2. Dave, Math
3. Susan, Biology
4. Jason, Science
5. Betty, History
6. Larry, Biology

I need a query to return these records:
2, 4, 5

So, I need the query to return only records where the class count (?)
is only equal to one. Since "Biology" occurs in more than one record,
it is excluded from the query results.

This probably won't make sense as to what I'm doing...I'm providing a
simple example of a more complex table.

Thanks again for any help!

"Yannick Turgeon" <no****@nowhere.com> wrote in message news:<4i********************@news20.bellglobal.com >...

Your request is far from being crystal clear! Your table structure is not
specified and the message subject seems to be the opposite of what I
understand in your message.

If I understand correctly what you need, this should work (change field and
table name for yours):

SELECT Name, Science
FROM YourTable
GROUP BY Science
HAVING COUNT(*) = 1
"John" <so*********@hotmail.com> wrote in message
news:90**************************@posting.google.c om...

I've got a single table I need to query to return records that have no
"related records."

Table dataset example:
1. John, Biology
2. Dave, Math
3. Susan, Biology
4. Betty, Sociology

The query should return this dataset:
2. Dave, Math
4. Bett, Sociology

Thanks for any suggestions...

[John]

The only way I know of to help clear up my question is by giving an
example of what I have. (I know the table is far from being
normalized)

[table]
tbl_Classes:
Student_Name
Class_Name

[table records]
1. John, Biology
2. Dave, Math
3. Susan, Biology
4. Jason, Science
5. Betty, History
6. Larry, Biology

I need a query to return these records:
2, 4, 5

So, I need the query to return only records where the class count (?)
is only equal to one. Since "Biology" occurs in more than one record,
it is excluded from the query results.

This probably won't make sense as to what I'm doing...I'm providing a
simple example of a more complex table.

Thanks again for any help!

"Yannick Turgeon" <no****@nowhere.com> wrote in message news:<4i********************@news20.bellglobal.com >...

Your request is far from being crystal clear! Your table structure is not
specified and the message subject seems to be the opposite of what I
understand in your message.

If I understand correctly what you need, this should work (change field and
table name for yours):

SELECT Name, Science
FROM YourTable
GROUP BY Science
HAVING COUNT(*) = 1
"John" <so*********@hotmail.com> wrote in message
news:90**************************@posting.google.c om...

I've got a single table I need to query to return records that have no
"related records."

Table dataset example:
1. John, Biology
2. Dave, Math
3. Susan, Biology
4. Betty, Sociology

The query should return this dataset:
2. Dave, Math
4. Bett, Sociology

Thanks for any suggestions...

[Yannick Turgeon]

John,

Then both PC's solution and mine will work. I'll rewrite mine with your
particular fields and table name:

SELECT Student_Name, Class_Name
FROM tbl_Classes
GROUP BY Class_Name
HAVING COUNT(*) = 1
"John" <so*********@hotmail.com> wrote in message
news:90**************************@posting.google.c om...

The only way I know of to help clear up my question is by giving an
example of what I have. (I know the table is far from being
normalized)

[table]
tbl_Classes:
Student_Name
Class_Name

[table records]
1. John, Biology
2. Dave, Math
3. Susan, Biology
4. Jason, Science
5. Betty, History
6. Larry, Biology

I need a query to return these records:
2, 4, 5

So, I need the query to return only records where the class count (?)
is only equal to one. Since "Biology" occurs in more than one record,
it is excluded from the query results.

This probably won't make sense as to what I'm doing...I'm providing a
simple example of a more complex table.

Thanks again for any help!

"Yannick Turgeon" <no****@nowhere.com> wrote in message

news:<4i********************@news20.bellglobal.com >...

Your request is far from being crystal clear! Your table structure is not specified and the message subject seems to be the opposite of what I
understand in your message.

If I understand correctly what you need, this should work (change field and table name for yours):

SELECT Name, Science
FROM YourTable
GROUP BY Science
HAVING COUNT(*) = 1
"John" <so*********@hotmail.com> wrote in message
news:90**************************@posting.google.c om...

I've got a single table I need to query to return records that have no
"related records."

Table dataset example:
1. John, Biology
2. Dave, Math
3. Susan, Biology
4. Betty, Sociology

The query should return this dataset:
2. Dave, Math
4. Bett, Sociology

Thanks for any suggestions...

[John]

Thanks for the help, Yannick and PC...I was able to use your suggestions.