[AccessD] Aquarium Life

[Jim Dettman]

Not as hard as you would think, but it's going to be some ugly SQL (long)

It boils down to what you said; looking at the outside ranges.   Here's all
the possibilities:

                       measure start       measure end

Existing Recs               S ------------------E
New#1             S----E
New#2                   S-------E
New#3                              S-----E
New#4                                       S------E
New#5                                                S-----E   
New#6          S---------------------------------------------E       

 So in cases #2, 3, 4,and 6 are OK, #1 and #5 are not.  Your where check is
then:

WHERE [existing start] <= New end AND [existing end] >= New Start

 The check is simple, but because you'd need to do this for each measure
(temp, PH, etc), the SQL will be a little ugly.

 This is actually pretty common logic used for checking conflicts for things
like reservations.

Jim.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
Sent: Tuesday, March 03, 2015 10:46 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Aquarium Life

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

*Fish* ID 	FI_TankSize 	FI_Difficulty 	FI_Temperament 	FI_TempLow 
FI_TempHi 	FI_HardnessLo 	FI_HardnessHi 	FI_PHLo 	FI_PHHi
FI_MaxSize 
FI_Price 	FI_Name 	FI_URL
12 	30 	Easy 	Peaceful 	72 	79 	2 	12 	5.8
7 	2 	5.99 	Albino Aeneus 
Cory Cat 
_http://www.liveaquaria.com/product/prod_display.cfm?c=747+870+953&pcatid=95
3_ 




ID 	FI_TankSize 	FI_Difficulty 	FI_Temperament 	FI_TempLow
FI_TempHi 
FI_HardnessLo 	FI_HardnessHi 	FI_PHLo 	FI_PHHi 	FI_MaxSize
FI_Price 
FI_Name 	FI_URL
17 	30 	Easy 	Peaceful 	74 	79 	4 	10 	6
7 	2 	1.89 	Cherry Barb 
_http://www.liveaquaria.com/product/prod_display.cfm?c=830+2855+1008&pcatid=
1008_ 



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

ID 	FI_TankSize 	FI_Difficulty 	FI_Temperament 	FI_TempLow
FI_TempHi 
FI_HardnessLo 	FI_HardnessHi 	FI_PHLo 	FI_PHHi 	FI_MaxSize
FI_Price 
FI_Name 	FI_URL
18 	10 	Easy 	Peaceful 	73 	77 	8 	12 	6.5
7 	2 	2.59 	Turquoise 
Danio 
_http://www.liveaquaria.com/product/prod_display.cfm?c=830+2855+948&pcatid=9
48_ 



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.
*Fish* ID 	FI_TankSize 	FI_Difficulty 	FI_Temperament 	FI_TempLow 
FI_TempHi 	FI_HardnessLo 	FI_HardnessHi 	FI_PHLo 	FI_PHHi
FI_MaxSize 
FI_Price 	FI_Name 	FI_URL
12 	30 	Easy 	Peaceful 	72 	79 	2 	12 	5.8
7 	2 	5.99 	Albino Aeneus 
Cory Cat 
_http://www.liveaquaria.com/product/prod_display.cfm?c=747+870+953&pcatid=95
3_ 

13 	30 	Easy 	Peaceful 	72 	79 	2 	12 	5.8
7 	2 	5.99 	False Julii Cory 
Cat 
_http://www.liveaquaria.com/product/prod_display.cfm?c=830+1161+1176&pcatid=
1176_ 

14 	30 	Easy 	Peaceful 	70 	77 	0 	15 	6.2
7.8 	3 	9.99 	Sterba's 
Cory 
_http://www.liveaquaria.com/product/prod_display.cfm?c=830+1161+846&pcatid=8
46_ 

15 	10 	Easy 	Peaceful 	72 	77 	6 	10 	6
6.5 	2 	1.99 	Harlequin 
Rasbora 
_http://www.liveaquaria.com/product/prod_display.cfm?c=830+2855+1065&pcatid=
1065_ 

16 	10 	Easy 	Peaceful 	72 	77 	4 	8 	6
6.5 	2 	4.39 	Bleeding Heart 
Tetra 
_http://www.liveaquaria.com/product/prod_display.cfm?c=830+890+906&pcatid=90
6_ 

17 	30 	Easy 	Peaceful 	74 	79 	4 	10 	6
7 	2 	1.89 	Cherry Barb 
_http://www.liveaquaria.com/product/prod_display.cfm?c=830+2855+1008&pcatid=
1008_ 

18 	10 	Easy 	Peaceful 	73 	77 	8 	12 	6.5
7 	2 	2.59 	Turquoise 
Danio 
_http://www.liveaquaria.com/product/prod_display.cfm?c=830+2855+948&pcatid=9
48_ 



Fish -- John W. Colby
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com