[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