i need to build a little table with 2 column:

first columns with a first letter from item ( =left("CompanyName",1) ) second columns with the sum of (=left("CompanyName",1))

CompanyName is an items in a collection of entities "Company"

how i can do it ? Where i mast put the code ?

PS: im sorry bat I'm not expert programmer !

Are you trying to generate a table or a query that aggregates these two columns? The aggregate would have something like this.

Source table

CompanyName Albert's Tuna Shop Alavean Bochco Cramah

and then you would get something like this from the query

A | 2 B | 1 C | 1

yes...

bclubb wrote:

Are you trying to generate a table or a query that aggregates these two columns? The aggregate would have something like this.

Source table

CompanyName Albert's Tuna Shop Alavean Bochco Cramah

and then you would get something like this from the query

A | 2 B | 1 C | 1

so to translate this into SQL statement, it would be something like

select C.letter, Count(*) as occurence
from
(
select left("CompanyName",1) as letter from Companies
)C
Group By C.letter

right?

The first thing that comes to my mind is to create a View in the database,

with the follwoing script

select C.letter, Count(*) as occurence
from
(
select left("CompanyName",1) as letter from Companies
)C
Group By C.letter

Then you can directly map this view to a TypedView at design time and fetch it at runtime.

Or a View with the following simple script

select ID, left("CompanyName",1) as letter from Companies

But then you will have to specify the aggregate and group by parameters when fetching the TypedView (Note: The Count() aggregate will run against the ID field.)

The first option is better if you won't have other opertaions done on the simple view

well.... i can't use view in data base becose it's the base data from anther software, i can only read data but not modify any.

i'd like to make my table by generated code, so if the database change in next relase of software i must only change my code.

invers72 wrote:

well.... i can't use view in data base becose it's the base data from anther software, i can only read data but not modify any.

i'd like to make my table by generated code, so if the database change in next relase of software i must only change my code.

Your table is a table with redundant data, so I wouldn't create a table for it, only a view. I don't understand why you can add a table but not a view .

You can also read the data into memory in a dynamic list and then build the actual list in memory, though you then miss the sql query abilities with it.