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

sandy16

Registered User.
Local time
Today, 15:50
Joined
Jun 12, 2015
Messages
12
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.
 
What if sum=9000? Or sum=6500?

Welcome to the forum by the way!
 
Hi, welcome to the forum,

You can do this with a sub-query, so your first query is . . .
Code:
SELECT Sum(Value1) 
FROM classlevel
. . . which returns a single value. Now we can use that value as a criteria, so if your rule is you want the next lowest volume level, then we write another query like . . .
Code:
SELECT TOP 1 VolumeLevel 
FROM Volume
WHERE Value2 < 
   (
   SELECT Sum(Value1)
   FROM classlevel
   )
ORDER BY Value2 DESC
. . . so we select Value2 values that are less than our Sum(), sort them DESCENDING, and select the first one. That will be largest one that is less than our Sum() in classlevel.

Makes sense?
 
its like a range from 0-5000 its V0 ,5000-10000 V1 and so on.

I am looking to get Volume values in the same table using case when . How to do that?
 
Thanks.

First i want to take the sum of the column in the same table and then use this comparison.My table is-

Class Value Sum of Value Volume
C0 1000 15000 V1
C1 2000 15000 V1
C2 3000 15000 V1
C3 4000 15000 V1
C4 5000 15000 V1

Is it possible to calculate this sum in one column and after that i apply IIF that is there is the other link?
 
Yes you can display the Sum in one field and the most efficient way is to:

1. Create a totals query that will sum just the Value field. Only the summed field should be present in this query, nothing else.
2. Add this query to your original query and drop the summed field.

And you can use either the IIF() solution in post #2 or the other in post #6.
 
Can you be please more specific as in what needs to be done?

I tried select sum() from table but it dint work.
 
Its on the basis of group by but i dont have to group the classes and sum it..is it possible to embed sum into IIF statement?
Like IIF(sum(Values)>0 and sum(values)<5000,"V0",
IIF(sum(values 0)>5001 and sum(values)<1000,"V1"),0)
 
That sum is with group by ..i just want to sum the values and use it.
 
You don't need the Group By, that's a generic example.

1. In a new query include only the Value field
2. Click the Totals button
3. Under the Value field change Group By to Sum
4. Save the query and close it

Now open your other query and include this new query in there. Then drop the summed field.
 
Yes i got the sum..it is coming in the query window only
 
Good, now open the other query and this new totals query there.
 
I am sorry i am new to ms access. What do i need to do after getting sum?
 
Open your original query and add the newly created query to it.
 
I got the sum and volume in my query window now but i wanted it in the table window .Is it possible?
 
Thanks.

I have one more table containing column -"Class level" (Class 0 to Class 4 but repeating across many rows) and few more fields and i want to join this table with the table i mentioned in the above comments containing columns- "Class Level" and "Values" ,join on the basis of class level values ..and in the join i calculate the sum of "values" column but again i am not going to use group by i want the total sum.
 

Users who are viewing this thread

Back
Top Bottom