How to open a recordset like this?

[briggsy79]

Hi everyone.
I have a problem, i have been trying to use the SHAPE provider to open a recordset like the following, it will be displayed on a heirarcharcial flex grid: (The "+" expands to show child records)

NAME TEST SCORE
-David Test 1 30
Test 2 31
Test 3 29
+John
-Peter Test 1 34
Test 2 35
Test 3 36
+Mark
+Ben

...etc...etc....

But i have not been able to. I have all the data in one Access table at the moment, and was hoping that i would only need one table.
The table is set up like the following:

NAME TEST SCORE
David Test 1 30
David Test 2 31
David Test 3 29
John Test 1 32
Peter Test 1 34
Peter Test 2 35
Peter Test 3 36
Mark Test 1 31
Ben Test 1 30


Any Ideas?
Any Code?


Thanks

 

[jmerencilla]

if you simply want to generate the desired ouput, use Data Environment designer. simply define the between the tables that contain the name, test number and score. once the command objects are complete, simply drag the parent command on the form and it will automatically load a MSHFlexGrid object

 

[GrantDB]

Cant you just use a simple SQL query
'Select name, test, score FROM TableName ORDER BY name'

Then move through the recordset with an if statement or two checking for the values in Name, something like

currentName=rs.fields(0)
if currentName=oldName then
else
end if
oldName=rs.fields(0)

this is very simplistic, but it should give you an idea

Grant

 

[briggsy79]

Thanks for such prompt replies
But i assumed that there would be some SQL command that i could use, as i know there are SQL commands that can do anything .

Thanks again

 

[GrantDB]

I am a bit confused as to what you are trying to do. Are you trying to get a recordset with only some Name fields expanded? Not particularly useful

If you want to find out if there are children then this will give you a count, such as:

Name CountOfName
Ben 1
David 3

SELECT Name, Count(Name) AS CountOfName FROM table2 GROUP BY Name



Grant

 

[briggsy79]

It doesnt matter what Name fields are expanded ,i was just trying to show a possible structure when viewed on a flex grid. I want the recordset to group the distinct names as shown in the first example, when the data appears in an access database in the format shown in the second example. Thnks again sorry about ambiguity.

 

[GrantDB]

As posted before, this will sort you results by 'name' as below, you coulf also order by a second field as well - ORDER BY name, test.
'Select name, test, score FROM TableName ORDER BY name'

Ben Test 1 30
David Test 1 30
David Test 2 31
David Test 3 29
John Test 1 32
Mark Test 1 31
Peter Test 1 34
Peter Test 2 35
Peter Test 3 36


Any nearer to an asnwer?

 

[briggsy79]

I have found the answer:

SHAPE {SELECT DISTINCT Name FROM Table1 ORDER BY Name } AS Command2 APPEND
({SELECT Test, Score FROM Table1 WHERE (Name = ?) ORDER BY Name, Test} AS
Command3 RELATE 'Name' TO PARAMETER 0) AS Command3


Thanks for all your help although i'm bound to be back.