need help to match the sum of column values to another table

[sandy16]

Hi,



I am using access 2010. I have "classlevel" table with 2 columns-Class and Value1 .Value1 column has numeric values that i ll input from webpage (webpage to ms access connectivity).

I want to sum the values of column "Value1" and i have another table-"Volume" which has 2 columns "VolumeLevel" and "Value2". So i want to match the sum that i calculated from first table-"ClassLevel" with the "value2" column in "Volume" table and get the corresponding "volumelevel" column value from that table and there is a third table that will get this volumelevel value.

There is no common column to join these tables.

Classlevel-


Class Value1
Class 0 3000
Class 1 2000
Class 2 300
Class 3 400
Class 4 500




Volume-


Volumnlevel Value2
Vo 0
V1 5000
V2 10000
V3 30000
....upto V10




Answer that i am looking for is V1 as sum=6200 lies between 5000 and 10000 so its V1.


Table 3-


Volume-
V1




Please help on how to do this.

[ranman256]

sorry, this is messy...

Q1 will be:
select sum([Value1]) as SumVol from Classlevel

Q2 =
SELECT Max(Volume.Value2) AS MaxOfValue2, [q1].[SumVal]>=[Value2] AS IsLessTotFROM Volume, q1
GROUP BY [q1].[SumVal]>=[Value2]
HAVING ((([q1].[SumVal]>=[Value2])=True));

Q3=
SELECT Min(Volume.Value2) AS MinOfValue2, [q1].[SumVal]<=[Value2] AS IsLessTot
FROM Volume, q1
GROUP BY [q1].[SumVal]<=[Value2]
HAVING ((([q1].[SumVal]<=[Value2])=True));


the ANSWER:
Q4 = SELECT Volume_1.Volumnlevel, Q2.MaxOfValue2, Q1.SumVal, Q3.MinOfValue2, Volume.VolumnlevelFROM Q1, Q3 INNER JOIN Volume ON Q3.MinOfValue2 = Volume.Value2, Q2 INNER JOIN Volume AS Volume_1 ON Q2.MaxOfValue2 = Volume_1.Value2;