Create a query that lists usernames that have only been used ONCE

[Barry McConomy]

Hi

I have a table that records transactions by usernames.

I would like to create a query that lists usernames that have only been used
ONCE.

Can any body advise how I go about doing this please.

Regards
Barry

 

[Guest]

Hi Barry - add the Name field twice in your query, then click on the Totals
Icon (looks like a backwards E). This adds a new row into the query, choose
Count in one of the Name fields. This will then count how many of each name
you have. Go back to the counted Name field and set the Criteria line to =1.
This will give you only the 1's. One thing - you can't have too many fields
in the query as it counts using all of them. Hope this helps -

yours,

Dika

 

[Bill Edwards]

Something like the following will work if you want to return fields in
addition to the username:

SELECT *
FROM tblUser
WHERE UserName
IN (SELECT UserName FROM tblUser GROUP BY UserName HAVING count(UserName) =
1)

or if you are just interested in the username:

SELECT UserName FROM tblUser GROUP BY UserName HAVING count(UserName) = 1

 

[Douglas J Steele]

In fact, if you want to know userids that have only been used once, then you
can't have any other field than userid in the query!

 

[Barry McConomy]

Kernow

Thanks for the advice.

Regards
Barry

Hi Barry - add the Name field twice in your query, then click on the
Totals
Icon (looks like a backwards E). This adds a new row into the query,
choose
Count in one of the Name fields. This will then count how many of each
name
you have. Go back to the counted Name field and set the Criteria line to
=1.
This will give you only the 1's. One thing - you can't have too many
fields
in the query as it counts using all of them. Hope this helps -

yours,

Dika

 

[Barry McConomy]

Bill

Thanks for the advice.

Regards
Barry

Something like the following will work if you want to return fields in
addition to the username:

SELECT *
FROM tblUser
WHERE UserName
IN (SELECT UserName FROM tblUser GROUP BY UserName HAVING count(UserName)
= 1)

or if you are just interested in the username:

SELECT UserName FROM tblUser GROUP BY UserName HAVING count(UserName) = 1

 

[Barry McConomy]

Hi Bill

I tried to run a report from the query and get the following message:-

" The specified field "name" could refer to more then one table listed in
the from clause of your SQL statement"

Can you help on this?

Regards
Barry

 

[Bill Edwards]

What is the text of your query?

Hi Bill

I tried to run a report from the query and get the following message:-

" The specified field "name" could refer to more then one table listed in
the from clause of your SQL statement"

Can you help on this?

Regards
Barry

 

[Barry McConomy]

Bill

Data Type = Number

SQL used:-

SELECT Bathing_Schedule.Name
FROM Bathing_Schedule
GROUP BY Bathing_Schedule.Name
HAVING count(Bathing_Schedule.Name)=1;

In the Bathing_Schedule the name is populated with a dropdown box which is a
filed from a table called resident_details

Regards
Barry

 

[Bill Edwards]

I created a Bathing_Schedule table and cut and paste your code.
Your SELECT statement worked fine in Access 97,2000, XP and also SQL Server
2000.

The error:
" The specified field "name" could refer to more then one table listed in
the from clause of your SQL statement"
that you have 2 tables in your FROM clause, there is a field that they each
have in common, and that field name was not fully qualified in the SELECT
clause. None of these apply to your SELECT statement.

You might try enclosing Name (it is a reserved word) in square brackets as
below, but I don't really think that is the problem.

SELECT Bathing_Schedule.[Name]
FROM Bathing_Schedule
GROUP BY Bathing_Schedule.[Name]
HAVING count(Bathing_Schedule.[Name])=1;

I don't have any other ideas.

 

[Barry McConomy]

Bill

Again thanks for your help.

Regards
Barry

I created a Bathing_Schedule table and cut and paste your code.
Your SELECT statement worked fine in Access 97,2000, XP and also SQL
Server 2000.

The error:
" The specified field "name" could refer to more then one table listed in
the from clause of your SQL statement"
that you have 2 tables in your FROM clause, there is a field that they
each have in common, and that field name was not fully qualified in the
SELECT clause. None of these apply to your SELECT statement.

You might try enclosing Name (it is a reserved word) in square brackets as
below, but I don't really think that is the problem.

SELECT Bathing_Schedule.[Name]
FROM Bathing_Schedule
GROUP BY Bathing_Schedule.[Name]
HAVING count(Bathing_Schedule.[Name])=1;

I don't have any other ideas.

Bill

Data Type = Number

SQL used:-

SELECT Bathing_Schedule.Name
FROM Bathing_Schedule
GROUP BY Bathing_Schedule.Name
HAVING count(Bathing_Schedule.Name)=1;

In the Bathing_Schedule the name is populated with a dropdown box which
is a filed from a table called resident_details

Regards
Barry