[AccessD] Aquarium Life

John W. Colby jwcolby at gmail.com
Tue Mar 3 21:51:24 CST 2015


Anyone out there doing freshwater aquariums?  And yes this will lead 
into databases.

I found a site which labels the fish on a bunch of scales
Difficulty
Temperament
Temp Hi
Temp Lo
Hardness Hi
Hardness Lo
PH Hi
PH Lo
Max Size

So... it turns out that selecting a mix of fish to coexist in a tank is 
not straightforward.  I have pulled 34 fish into a table with these 
columns all filled out.  So now I need to build a method that will allow 
me to select fish.  As I select new fish, it has to track back and make 
sure that the new selection meets all of the requirements of the 
previous selection(s).  See the table below.

So if the first fish selected is a cory cat, (12,13,or 14), the second 
fish selected must be able to live in the water temperature, hardness 
and PH that the Cory Cat can live in.

Simple eh?

Algorithmically, take the outside ranges, and keep the innermost value 
of each end as a new fish is selected.  Take temp for example
*Query1* ID 	FI_TankSize 	FI_Difficulty 	FI_Temperament 	FI_TempLow 
FI_TempHi 	FI_HardnessLo 	FI_HardnessHi 	FI_PHLo 	FI_PHHi 	FI_MaxSize 
FI_Name
12 	30 	Easy 	Peaceful 	72 	79 	2 	12 	5.8 	7 	2 	Albino Aeneus Cory Cat


*Query1* ID 	FI_TankSize 	FI_Difficulty 	FI_Temperament 	FI_TempLow 
FI_TempHi 	FI_HardnessLo 	FI_HardnessHi 	FI_PHLo 	FI_PHHi 	FI_MaxSize 
FI_Name
17 	30 	Easy 	Peaceful 	74 	79 	4 	10 	6 	7 	2 	Cherry Barb


Given these two fish, the temp range is now 74-79, Hardness is 4-10, PH 
is 6-7

Query1
*Query1* ID 	FI_TankSize 	FI_Difficulty 	FI_Temperament 	FI_TempLow 
FI_TempHi 	FI_HardnessLo 	FI_HardnessHi 	FI_PHLo 	FI_PHHi 	FI_MaxSize 
FI_Name
18 	10 	Easy 	Peaceful 	73 	77 	8 	12 	6.5 	7 	2 	Turquoise Danio

Query1
If we add the Danio, the Temp is now 74-77, Hardness is 8-10, and PH is 
6.5-7

So is this possible just using SQL?  Select a PK to add to a "Potential 
selection" table and have SQL return new Min/Max values as I have done 
above manually?  Or should I just do it the grunt (code) way?

The objective is to build a "aquarium calculator" which returns the 
characteristics required to keep the selected fish alive, as well as 
discover as fish are added, whether they are compatible to the existing 
selection.
*Query1* ID 	FI_TankSize 	FI_Difficulty 	FI_Temperament 	FI_TempLow 
FI_TempHi 	FI_HardnessLo 	FI_HardnessHi 	FI_PHLo 	FI_PHHi 	FI_MaxSize 
FI_Name
12 	30 	Easy 	Peaceful 	72 	79 	2 	12 	5.8 	7 	2 	Albino Aeneus Cory Cat
13 	30 	Easy 	Peaceful 	72 	79 	2 	12 	5.8 	7 	2 	False Julii Cory Cat
14 	30 	Easy 	Peaceful 	70 	77 	0 	15 	6.2 	7.8 	3 	Sterba's Cory
15 	10 	Easy 	Peaceful 	72 	77 	6 	10 	6 	6.5 	2 	Harlequin Rasbora
16 	10 	Easy 	Peaceful 	72 	77 	4 	8 	6 	6.5 	2 	Bleeding Heart Tetra
17 	30 	Easy 	Peaceful 	74 	79 	4 	10 	6 	7 	2 	Cherry Barb
18 	10 	Easy 	Peaceful 	73 	77 	8 	12 	6.5 	7 	2 	Turquoise Danio

Query1

-- 
John W. Colby



More information about the AccessD mailing list