Unique values & Record count

[Kim]

I am new to Access and I can't for the life of me figure out how to to this

I have a column of account numbers such as

723031
723032
723034
723031
723031

Can Access display only the UNIQUE items in this column and then give me a
count of those UNIQUE items? I don't want to see any that may be repeated.

I am pretty sure I can accomplish this by doing a query, but need step by
step instructions for setting this up.

Thanks in Advance

 

[Ofer Cohen]

Hi Kim, try this SQL in a query

Select [Account_Number] , Count([Account_Number]) As CountOfAccounts
From TableName
Group By [Account_Number]

You need to change the table name, and the name of the [Account_Number]
field name to the right one.

 

[Kim]

When I try this it asks for Parameter Value of the Account Number?

Hi Kim, try this SQL in a query

Select [Account_Number] , Count([Account_Number]) As CountOfAccounts
From TableName
Group By [Account_Number]

You need to change the table name, and the name of the [Account_Number]
field name to the right one.

--
Good Luck
BS"D

I am new to Access and I can't for the life of me figure out how to to this

I have a column of account numbers such as

723031
723032
723034
723031
723031

Can Access display only the UNIQUE items in this column and then give me a
count of those UNIQUE items? I don't want to see any that may be repeated.

I am pretty sure I can accomplish this by doing a query, but need step by
step instructions for setting this up.

Thanks in Advance

 

[Ofer Cohen]

Have you changed the name of the Account number to the name of the field in
your table?

--
Good Luck
BS"D

When I try this it asks for Parameter Value of the Account Number?

Hi Kim, try this SQL in a query

Select [Account_Number] , Count([Account_Number]) As CountOfAccounts
From TableName
Group By [Account_Number]

You need to change the table name, and the name of the [Account_Number]
field name to the right one.

--
Good Luck
BS"D

I am new to Access and I can't for the life of me figure out how to to this

I have a column of account numbers such as

723031
723032
723034
723031
723031

Can Access display only the UNIQUE items in this column and then give me a
count of those UNIQUE items? I don't want to see any that may be repeated.

I am pretty sure I can accomplish this by doing a query, but need step by
step instructions for setting this up.

Thanks in Advance

 

[Kim]

Yes I Did. I am actually using Claim Numbers and an Internal Assigned ID#.

Have you changed the name of the Account number to the name of the field in
your table?

--
Good Luck
BS"D

When I try this it asks for Parameter Value of the Account Number?

Hi Kim, try this SQL in a query

Select [Account_Number] , Count([Account_Number]) As CountOfAccounts
From TableName
Group By [Account_Number]

You need to change the table name, and the name of the [Account_Number]
field name to the right one.

--
Good Luck
BS"D


:

I am new to Access and I can't for the life of me figure out how to to this

I have a column of account numbers such as

723031
723032
723034
723031
723031

Can Access display only the UNIQUE items in this column and then give me a
count of those UNIQUE items? I don't want to see any that may be repeated.

I am pretty sure I can accomplish this by doing a query, but need step by
step instructions for setting this up.

Thanks in Advance

 

[Ofer Cohen]

Can you post the field name, table name, and the SQL you have now?

--
Good Luck
BS"D

Yes I Did. I am actually using Claim Numbers and an Internal Assigned ID#.

Have you changed the name of the Account number to the name of the field in
your table?

--
Good Luck
BS"D

When I try this it asks for Parameter Value of the Account Number?

:

Hi Kim, try this SQL in a query

Select [Account_Number] , Count([Account_Number]) As CountOfAccounts
From TableName
Group By [Account_Number]

You need to change the table name, and the name of the [Account_Number]
field name to the right one.

--
Good Luck
BS"D


:

I am new to Access and I can't for the life of me figure out how to to this

I have a column of account numbers such as

723031
723032
723034
723031
723031

Can Access display only the UNIQUE items in this column and then give me a
count of those UNIQUE items? I don't want to see any that may be repeated.

I am pretty sure I can accomplish this by doing a query, but need step by
step instructions for setting this up.

Thanks in Advance

 

[LauriS]

Can Access display only the UNIQUE items in this column and then give me a

count of those UNIQUE items? I don't want to see any that may be repeated.

When you say a "count of those unique items" do you mean a count of how many
times each unique number appears in the column or how many unique numbers
there are in all?

Using your example here:

723031
723032
723034
723031
723031

Do you want to see that 723031 occurs 3 times or that there are 3 unique
numbers total in the list?

Lauri

 

[PaulSchrum]

Select [Account_Number] , Count([Account_Number]) As CountOfAccounts
From TableName
Group By [Account_Number]

Ofer,

It happens that I am needing to do the same thing as Kim was, so your
advice to her was very helpful to me also.

However, I also need a count of NULLs, preferably all in the same
query. Can you explain how to do that?

Thanks.

- Paul
Schrum

 

[PaulSchrum]

Never mind. See below.

Select [Account_Number] , Count([Account_Number]) As CountOfAccounts
From TableName
Group By [Account_Number]

Ofer,

However, I also need a count of NULLs, preferably all in the same
query. Can you explain how to do that?

Never mind. I figured it out. I have to use the NZ function:

Select [Account_Number] , Count(NZ([Account_Number])) As
CountOfAccounts

From TableName

Group By [Account_Number]

- Paul