MySQL and 'this' kind of table

[RyanY]

Lets say for example ....



___________________________Size of fruit____________
Name of fruit________1________2________3_________ 4
------------------------------------------------------------------------------
Grapes____________$1_______$2_______$5_________$6
Apples____________$2________$4_______$5________$7


Where i get 2 inputs for an example if name_of_fruit was Grapes and the size were to be 4 ... the output would be $6.

Or

When Name of fruit is Apples and price is $5 , the resulting answer would be size = 3.

Would i be able to make this kind of table in MySQL? The normal method doesnt seem to work ... are there alternatives?

Hope someone can help me with this ?

[LucGuldentops]

You could do it like you show: a table with columns Name, Size1, Size2, Size3 and Size4.

The sql to get values out of this table for size 4 of Grape would look like this:

SELECT Table3.Name, Table3.Size4
FROM Table3
WHERE (((Table3.Name)="Grape"));

Now, tou will have to setup this sql statement thru vb code, as the column where you want to get the value out will depent on the selected size, here's an example to get values for Grape with size 3:

dim nSize as long
dim sSql as string

nSize = 3
ssql = "SELECT Table3.Name, Table3.Size" & nSize & " FROM Table3 WHERE (((Table3.Name)="Grape"));
set rs=db.execute(ssql)

Now, it would be better to setup a different table structure, like Name, Size, Price because you are then not limited in the number of different sizes and you can use simplified sql statements with normal conditions like:

SELECT Name, Size, Prize FROM Table3 WHERE Name = 'Grape' AND Size = 3;

Hope this helped you out,

Luc