I'm Stumped

[GregOnline]

Hello:

I have a table that has the following fields:

Teacher
Room Number
Number of Students

I want to create a form that shows a "map" of what teacher is in what room. when I make it, only the first teacher record shows up, and then as you scroll through the records the next teacher and so on.

What I really want to do is take the records from the original table - create an operation that creates a new table with one record that has for example teacher1, room1, number of students1, teacher2, room number2, number of students2, etc.

I have looked through the different operations, but can't seem to find the right operation.

Does this make sense or am I so stumped that I can't even explain it?

Thanks,

Greg

[Stan Mathews]

RE: I'm Stumped

in V4 there is no "concatenate text across records" built in function.

Here's a homemade function.

'Date Created: 02-Mar-2005 02:29:08 PM
'Last Updated: 02-Mar-2005 02:29:08 PM
'Created By : Stan Mathews
'Updated By : Stan Mathews
FUNCTION con_text AS C (fldname AS C )
'usage is context("fieldname")
'returns a space separated string of field values for a character field
desc = ""
tbl = table.open("dcavail")'sub your table name here
tbl.fetch_first()
debug(1)
while .not. tbl.fetch_eof()
desc = desc + eval("tbl."+fldname) + " "
tbl.fetch_next()
end while
con_text = desc
tbl.close()
END FUNCTION

By the way, there are built in functions in V% to do the same thing.

[Stan Mathews]

RE: I'm Stumped

If you'll attach a small sample of your table here I'll customize the function to do exactly what you requested.

[Stan Mathews]

RE: I'm Stumped

Or try this .....

'Date Created: 02-Mar-2005 02:29:08 PM
'Last Updated: 02-Mar-2005 02:41:22 PM
'Created By : Stan Mathews
'Updated By : Stan Mathews
FUNCTION cls_str AS C ( )
'syntax is Character Formatted_String_of_data = cls_str()
'sample return value is "Mary, 304, 17, John, 310, 22, Paul, 301, 25, Barbara, 287, 19"
desc = ""
tbl = table.open("classes") 'sub your table name here
tbl.fetch_first()
while .not. tbl.fetch_eof()
'field names in my table are teacher, room, and stu_count
'adjust the next line to fit your field names
desc = desc + alltrim(tbl.teacher) + ", "+tbl.Room+", "+tbl.Stu_count+", "
tbl.fetch_next()
end while
cls_str = left(desc,-2)
tbl.close()
END FUNCTION

[GregOnline]

RE: I'm Stumped

Stan:

I appreciate the time you have taken to help me. I have attached a picture that shows the current database.

Some explanation is needed however. I misunderstood what my friend was saying. I thought he was using a different application, but the need is still pretty much the same. I wasn't being deceptive.

In the database, this will be some sample records in the field order the picture shows:

5010,A-15,1,425,18650,02/02/05,03/01/05,Retired (this value appears if "day_out" has a day in it),28.

5010,A-15,3,430,19210,02/02/05,03/01/05,Retired,28.

What I want is a table that in one record will list all these records.

If it is too much work, I understand.

[GregOnline]

RE: I'm Stumped

Stan:

Picture didn't attach. Trying again.

Greg

[GregOnline]

RE: I'm Stumped

Stan:

Picture won't attach. Here is the structure.

Crop_Number N 4 0
Strain C 5
Room_1 N 2 0
Room_1_Trays N 3 0
Room_1_Footage N 10 3
Day_in D 8
Day_out D 8
Retired C 8
Days_in_Production N 8 0

Sorry,

Greg

[Stan Mathews]

RE: I'm Stumped

Not sure that I understand "What I want is a table that in one record will list all these records."

How can one record list other records?

I can build you a function to concatenate the values in the fields and write that concatenation to another table but unless it is stored in a memo field the length of the string will overwhelm the 255 character limit in under 5 records.

If the string is stored in a memo field, what portion of the string should be written to a "real" field to identify the record? Or will there be only one record with one memo field.

Guess I need some more clarification of your intent.