How do I cont only unique characters in a table

  • Thread starter Thread starter Guest
  • Start date Start date
Timmy

Can you give us an explaination of what you are trying to accomplish? What
is the purpose of counting unique characters in the table?
 
II have a table that has the same company with two different states. I need
the company count to be 1 and states count to be 2. Any help is greatly
appreciated
 
Timmy

SELECT [Company], COUNT([State])
FROM MyTable
GROUP BY [Company]

You can do this in the Query designer if you set up a query on your table
and click on the Totals icon on the query toolbar. All the selected fields
will have Group By on the Totals line. Change the state field's total line
to Count.
 
Thank you so much Bill. One more question if I can. What if I have one
company, two states and three products. How can I get the company count to be
1, states count to be 2, and product counts to be 3?

Bill Mosca said:
Timmy

SELECT [Company], COUNT([State])
FROM MyTable
GROUP BY [Company]

You can do this in the Query designer if you set up a query on your table
and click on the Totals icon on the query toolbar. All the selected fields
will have Group By on the Totals line. Change the state field's total line
to Count.

--
Bill Mosca, MS Access MVP


Timmy said:
II have a table that has the same company with two different states. I
need
the company count to be 1 and states count to be 2. Any help is greatly
appreciated
Click to expand...
Click to expand...
 
Timmy

You can add a COUNT for the product as well.
SELECT [Company], COUNT([State]), COUNT([Product])
FROM MyTable
GROUP BY [Company]
Click to expand...
Click to expand...

--
Bill Mosca, MS Access MVP


Timmy said:
Thank you so much Bill. One more question if I can. What if I have one
company, two states and three products. How can I get the company count to
be
1, states count to be 2, and product counts to be 3?

Bill Mosca said:
Timmy

SELECT [Company], COUNT([State])
FROM MyTable
GROUP BY [Company]

You can do this in the Query designer if you set up a query on your table
and click on the Totals icon on the query toolbar. All the selected
fields
will have Group By on the Totals line. Change the state field's total
line
to Count.

--
Bill Mosca, MS Access MVP


Timmy said:
II have a table that has the same company with two different states. I
need
the company count to be 1 and states count to be 2. Any help is
greatly
appreciated


:

Timmy

Can you give us an explaination of what you are trying to accomplish?
What
is the purpose of counting unique characters in the table?

--
Bill Mosca, MS Access MVP


Is there a way to cont only unique characters in a table?
Click to expand...
Click to expand...
Click to expand...
 
I did that and my state count is 3 (because there are 3 products), when in
reality I have only 2 unique states (NY and NJ). NY state has 2 products
and NJ has one product (3 rows in all). Thanks again Bill

Bill Mosca said:
Timmy

You can add a COUNT for the product as well.
SELECT [Company], COUNT([State]), COUNT([Product])
FROM MyTable
GROUP BY [Company]
Click to expand...
Click to expand...

--
Bill Mosca, MS Access MVP


Timmy said:
Thank you so much Bill. One more question if I can. What if I have one
company, two states and three products. How can I get the company count to
be
1, states count to be 2, and product counts to be 3?

Bill Mosca said:
Timmy

SELECT [Company], COUNT([State])
FROM MyTable
GROUP BY [Company]

You can do this in the Query designer if you set up a query on your table
and click on the Totals icon on the query toolbar. All the selected
fields
will have Group By on the Totals line. Change the state field's total
line
to Count.

--
Bill Mosca, MS Access MVP


II have a table that has the same company with two different states. I
need
the company count to be 1 and states count to be 2. Any help is
greatly
appreciated


:

Timmy

Can you give us an explaination of what you are trying to accomplish?
What
is the purpose of counting unique characters in the table?

--
Bill Mosca, MS Access MVP


Is there a way to cont only unique characters in a table?
Click to expand...
Click to expand...
Click to expand...
 
OK, let's do it this way:
Create the first query to get the number of states per company.
Create a second query to get the number of products per company.
Create a new query using both of the other queries as tables and join them
on the company.

That shouild get the right counts for state and products.

--
Bill Mosca, MS Access MVP


Timmy said:
I did that and my state count is 3 (because there are 3 products), when in
reality I have only 2 unique states (NY and NJ). NY state has 2 products
and NJ has one product (3 rows in all). Thanks again Bill

Bill Mosca said:
Timmy

You can add a COUNT for the product as well.
SELECT [Company], COUNT([State]), COUNT([Product])
FROM MyTable
GROUP BY [Company]
Click to expand...

--
Bill Mosca, MS Access MVP


Timmy said:
Thank you so much Bill. One more question if I can. What if I have one
company, two states and three products. How can I get the company count
to
be
1, states count to be 2, and product counts to be 3?

:

Timmy

SELECT [Company], COUNT([State])
FROM MyTable
GROUP BY [Company]

You can do this in the Query designer if you set up a query on your
table
and click on the Totals icon on the query toolbar. All the selected
fields
will have Group By on the Totals line. Change the state field's total
line
to Count.

--
Bill Mosca, MS Access MVP


II have a table that has the same company with two different states.
I
need
the company count to be 1 and states count to be 2. Any help is
greatly
appreciated


:

Timmy

Can you give us an explaination of what you are trying to
accomplish?
What
is the purpose of counting unique characters in the table?

--
Bill Mosca, MS Access MVP


Is there a way to cont only unique characters in a table?
Click to expand...
Click to expand...
Click to expand...
 
Thank you Bill

Bill Mosca said:
OK, let's do it this way:
Create the first query to get the number of states per company.
Create a second query to get the number of products per company.
Create a new query using both of the other queries as tables and join them
on the company.

That shouild get the right counts for state and products.

--
Bill Mosca, MS Access MVP


Timmy said:
I did that and my state count is 3 (because there are 3 products), when in
reality I have only 2 unique states (NY and NJ). NY state has 2 products
and NJ has one product (3 rows in all). Thanks again Bill

Bill Mosca said:
Timmy

You can add a COUNT for the product as well.
SELECT [Company], COUNT([State]), COUNT([Product])
FROM MyTable
GROUP BY [Company]

--
Bill Mosca, MS Access MVP


Thank you so much Bill. One more question if I can. What if I have one
company, two states and three products. How can I get the company count
to
be
1, states count to be 2, and product counts to be 3?

:

Timmy

SELECT [Company], COUNT([State])
FROM MyTable
GROUP BY [Company]

You can do this in the Query designer if you set up a query on your
table
and click on the Totals icon on the query toolbar. All the selected
fields
will have Group By on the Totals line. Change the state field's total
line
to Count.

--
Bill Mosca, MS Access MVP


II have a table that has the same company with two different states.
I
need
the company count to be 1 and states count to be 2. Any help is
greatly
appreciated


:

Timmy

Can you give us an explaination of what you are trying to
accomplish?
What
is the purpose of counting unique characters in the table?

--
Bill Mosca, MS Access MVP


Is there a way to cont only unique characters in a table?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top