Can I Create a Linked Summary Table?

[#1]

Hi All,

I apologize if I am trying to do something not appropriate for JMP, but I was trying to leverage JMP as a database to store a lot of material property data.   I use individual JMP tables to visualize/analyze data, but will have some users of the data that would like to pull up a material summery table that captures means/standard deviations of the different properties I've measured.

For example, let's say I have an (oversimplified) table called Materials Database - Physical Data:

Material	Condition	Property A	Property B
Wood	New	100	5042
Wood	New	97	6005
Wood	New	113	5532
Wood	After Water Exp	76	3680
Wood	After Water Exp	45	4032
Wood	After Water Exp	68	2599
Straw	New	33	1107
Straw	New	21	958
Straw	New	16	1327
Straw	After Water Exp	10	280
Straw	After Water Exp	7	332
Straw	After Water Exp	11	399

I really would like to ALSO have a table called Materials Database - Master Summary Table that links to this table and others of it's like.  The material column would share the same ID, but it is possible that I could have that each table there could be a different number of rows for a given Material ID and condition, depending on the number of replicates tested.  Here's what I'd like the summary table to look like:

Material	Mean (Property A) - New	StDev (Property B) - New	Mean (Property A) - After Water	StDev (Property A) - After Water	Mean (Property B) - New	StDev (Property B) - New	Mean (Property B) - After Water	StDev (Property B) - After Water
Wood	103	9	63	16	5526	482	3437	747
Straw	23	9	9	2	1131	186	337	60

I guess I am looking for a script to put in a given column, so that as we collect more material, I would add rows of new materials, and also add columns as appropriate for the different properties I continue to measure.  I am a JMP novice at best, so while I have been able to calculate means or standard deviations for a set number of rows in variations similar to: If( Modulo( Row(), 3 ) == 1, Col Mean( :Property A, Ceiling( Row() / 3 ) )) thanks to a previous post here, I then have to create a sub table from this, so that I could then have a single row for each material to allow me to use link ID for the Material column across different tables and combine all of those calculated means/standard deviations.

Is this really my best option?  Thank you for any advice!

[#2]

I would suggest creating one Materials Database file which will have all the possible materials and their properties. Then you can use summary platform to get many of different statistics out of it and the data will be grouped properly.

I wouldn't most likely even save this Summary table because it is so easy to create when needed.

After you get more used to JMP and JSL you could create a script which users could use to get this summary table when ran (and if you have to have individual data tables you could concatenate them before creating the summary table).

Welcome to community and hopefully this gives some ideas!

[#3]

JMP can pull from (but not push to) SQLite Database (.db) files natively.  They're real easy to use.  

For instance.  Just drag this db file into JMP.  Or open it however you want.  

You can also script it easily enough.  

New SQL Query(
	Connection( "SQLite:Database=$DESKTOP/chinook.db" ),
	QueryName( "Fun" ),
	CustomSQL("SELECT t1.Title, t2.Name 
		FROM albums t1 
			LEFT OUTER JOIN artists t2
			ON t1.ArtistId = t2.ArtistId"), 
	/*
	//OR instead of CUSTOM SQL
	Select( Column( "Title", "t1" ), Column( "Name", "t2" ) ),
	From(
		Table( "albums", Alias( "t1" ) ),
		Table(
			"artists",
			Alias( "t2" ),
			Join(
				Type( Left Outer ),
				EQ( Column( "ArtistId", "t2" ), Column( "ArtistId", "t1" ) )
			)
		)
	)
	*/
) << Run

I might be behind the times, but the reason I don't do the method described above is because I've seen people try to pull "Master Summary Tables" that can get quite large over a slow VPN and it seriously takes like 20 minutes to query a single row because they have to open the entire table first.