count for different values in a field

[bulletje]

I want to be able to count into a memvar how many different values a specific field occurs, z.b. how many different "countries" there are in a database.

 

[audiopro]

Try select distinct

Keith

http://www.studiosoft.co.uk

 

[mjcmkrsr]

Bulletje,

SELECT DISTINCT Countries from tblTable INTO ARRAY aCountries

?ALEN(aCountries) or

?_Tally

hth

Mark

 

[Dan Freeman]

SELECT COUNT(Distinct country) FROM Customers Into Array laCount

 

[DSummZZZ]

Or maybe something like:

SELECT Countries, CNT(Countries) AS nHowMany ;
   FROM MyTable ;
   GROUP BY Countries ;
   ORDER BY Countries ;
   INTO ARRAY aSomeArray

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[bulletje]

Thanks all for the info, but it only a partial solution.
I want to have a field in a form whitch updates every time another country is somehow added to the database

Thanks in advance

 

[Olaf Doschke]

There is no live update of any computed data. Computed data is computed and therefore must be recomputed everytime you want the actual figures. So you simply repeat code counting the distinct values.

There are ways to centralise this, eg in a database (if you speak of a dbc and not of the outdated dbase term for a single table) you can create triggers automatically running in case of an insert/update or delete, so you can execute the count of distinct countries in these triggers and update some secondary table holding that number.

Bye, Olaf.

 

[JRB-Bldr]

"I want to have a field in a form which updates every time another country is somehow added to the database"

That was not mentioned in your original question.

You can use a Timer object to periodically run the SQL Query to get a list of the distinct values and then, from that value update a Form textbox.

Good Luck,
JRB-Bldr

 

[bulletje]

Thank You all for helping me with this. I know now for sure that I have to work arround this. You all helped me a lot.

bulletje

 

[JRB-Bldr]

Alternatively to using a Timer, you can set an INDEX on your table and then do a SEEK on a newly entered value and, if not found, then increment a counter to be displayed in your Form's textbox.

If it is found, then you would not increment the counter.

NOTE - since users often find many 'creative' ways to enter character strings, you would want to 'normalize' the SEEK string.
Something like:

* --- Build your Index on the UPPERCASE of the field value ---
USE MyTable IN 0 EXCL
SELECT MyTable
INDEX ON UPPER(ThisField) TAG UpperFld

* --- Then elsewhere in your code when you need to use it ---
* --- Likely in the Valid Method For txtEnterFld ---
SELECT MyTable
nLenThisField = LEN(MyTable.ThisField)

cChk4New = PADR(ALLTRIM(UPPER(ThisForm.txtEnterFld.Text)), nLenThisField)

SELECT MyTable
SET ORDER TO UpperFld
IF !SEEK(cChk4New)
* --- Value Not Found, Increment Counter And Display ---
nCounter = nCounter + 1
ThisForm.txtCounter.Value = nCounter
ELSE
* --- Value Already Exists, Do Nothing ---
ENDIF

Good Luck,
JRB-Bldr

 

[Dan Freeman]

I want to have a field in a form whitch updates every time another country is somehow added to the database

So what you're saying is that the number of countries is an important data entity in your database?

If that's the case, normalize the names of countries into their own tables, and this form field can simply report the Recno() of the Country table. If you bind the control to =Recno("country") it should even auto-refresh as other users add countries depending on your SET REFRESH setting.

But make sure you're solving the *right* problem, eh? This might not be a query issue.

 

[Olaf Doschke]

Dan, you're right, that repeated values indicate a database normalization need. Good point.

If the country field is a foreign key field already, the stats would be about the countries, which indeed are referenced.

How would that be solved using a schematic change, so this statistics is reduced to some easy expression?

Bye, Olaf.

 

[Dan Freeman]

Olaf, I think the answer to "how many countries are there?" is presumably "the number of countries in the country table" (subject to the normalization rules of the database).

Realistically, if that kind of bean counting is going on someone is also likely to ask "for each country, how many customers are there?" although that question is not on the table. We can make up a million scenarios to answer questions that have not been asked.

The OP asked "how many countries are there?" )

 

[Olaf Doschke]

Bulltje asked "z.b." which is the german abbreviation meaning "for example", in conjunction to a dutch sounding user name this suggests to me this is indeed meant just as an example.

So he generally asked about some statistical value of the database, which might or might not be such a simple figure like a record count.

Let's not start a flame here. A few days ago you were the one saying: Why not simply answer the direct question? You presume Bulltje doesn't know something as substantial as normalisation, is that really considerate?

Let's wait for bulltjes reaction now.

Bye, Olaf.

 

[brigmar]

If that's the case, normalize the names of countries into their own tables, and this form field can simply report the Recno() of the Country table. If you bind the control to =Recno("country") it should even auto-refresh as other users add countries depending on your SET REFRESH setting.

RECCOUNT(), not RECNO()