Computed controls

[wib]

I've got a table called Plants, in whcih records have an "ID", a "Common name" and other fields. I also have one called Clients, in which each record has a ClientPlants field, which is a comma-separated list of Plant IDs (from the Plants table).

I want to create a report to display the "Common names" of each of the plants listed in the ClientPlants field of the Clients table, and it seems the way to do that is to used a computed control -- however, I have no idea about how to create an expression that will find the common names of each plant in the ClientPlants field and list them.

Help and pointers would be very much appreciated.

 

[grnzbra]

When you say that you have a field with a comma seaprated list of plant IDs, I get the impression that you might be going about this the hard way.

I visualize a situation in which any given client can be associated with sevearal plants. Is this correct?

Are you stuck with the table in its present form or do you have the ability to change it? How many client records exist in the table?

The easiest way would be to create a subroutine which would take the client ID as an input. The function would open a recordset consisting of the client ID and the PlantID fields from the Client table. You will now have one record.
Open another recordset to add records to a temporary table. Using the Mid function loop through the PlantID table, one character at a time (Mid(PlantID, 1, counter)) where the counter is a counter that increases by 1 each time the loop is repeated (I'm not sure of the order of the 1 (how many characters you want to see) and counter-If it doesn't work one way, reverse their positions in the function.). Each time through the loop, check if what you got back was a space or a comma or a character in the ID. If it was a character in the ID, concatinate it to a string to built up an ID, if it is a comma, take the ID in the string and append it to the temporary table in an ID field and the client ID to the Client ID field of the temp table. If it is a space (I assume individual IDs don't contain spaces) clear the string value and keep looping. When you hit the next comma, append the new Plant ID along with the same customer ID to the temp table. You will end up with a linking table between the client table and the plant table. then deal with this as you would any report with a client and a number of transactions.

I think it reads worse than it is: It might look something like this

Open recoreset consisting of ClientID and PlantID field of Clients table

Dim IDstr as string
Dim IDchr as string
dim plID as integer
dim cntr as integer

plID = Len(PlantID)
cntr = 1

do while cntr < plID
IDchr = Mid(PlantID, 1,cntr) NOTE:May have to reverse 1 and cntr

Select Case cntr
case &quot;,&quot;
append ClientID and IDstr to tempfile
case &quot; &quot;
IDStr = &quot;&quot;
case else
IDstr = IDStr & IDchr
End select

recordset.move next
IDStr = &quot;&quot;
cntr = cntr + 1
Loop

What you might want to do is to have nested loops (this one being the inner loop) which you use to create a permenant linking table. then just use the linking table to link the plants to the clients with one record for each client/plant combination. The get rid of the PlantID field of the Clients table and link the Linking table to the Client table by the ClientID and to the Plant table by the PlantID

 

[grnzbra]

Oops there is an error. That shoul be
Select Case IDChr