help me please, calculation in ms access

[kulunggoy]

im using ms access 2003, i want to get the sum of a certain field which there is a condition that it will only sum up the field table1 which have the same word or text..
then the total will be output in the next table2.. please help..sorry if i cant explain it much clearer i can send you the illustration if you wanted it.. thanks

 

[Margarita]

Select sum(FieldName) as SumofField
from TableName
where FieldName like '...' or FieldName like '...';

Am I misinterpreting- are you looking for something that is not a simple sum? Please explain.

 

[kulunggoy]

for example: in "table 1" i have a 2 fields which is "item, withdraw" then under my "item" field i have a 2 same data "kulangot" the "withdraw" data of the first "kulangot" is "2" and the other one is "3". then another 2 same data in "item" field called "tae" which have "5" and "2" data in "withdraw" field.

then in table 2 i have also 2 fields which is "item, eoh" this will be the summary total of the items in table 1.. for example it will sum up the 2 "kulangot" data which the result is "5" then the "tae" which is "7"

by the way.. thanks for helping!!

 

[kulunggoy]

h t t p://i97.photobucket.com/albums/l223/kulunggoy/db1-3.png.. here's the illustration of what i want to do

 

[Margarita]

You need to have a table that stores all the unique Items in your Withdraws table. This table (call it Items) needs to be in a one to many relationship with the Withdraws table (that is, for every unique item in the Items table, there can be more than one entry in the Withdraws table). Then you would do:

SELECT I.Item, sum(Withdraw) as Sum_Of_Withdrawals_per_Item
FROM (Items as I) LEFT JOIN (Withdraws as W) 
ON I.Item= W.Item
GROUP BY I.Item;

 

[kulunggoy]

thanks again for the reply.. i found a solution but it was not satisfying, i tried crosstab in query.. I want to try yours.. can you send me some basic sample? thanks again very much!!!!

 

[Margarita]

You are most welcome. You should be able to produce a sample yourself with the example you gave me. For example, if you have it Items table two entries:
Item1
Item2

and in Withdrawals table three entries:
Item1-- 2
Item1-- 4
Item2-- 8

the query I gave you should give you the following output:

Item1-- 6
Item2-- 8

 

[kulunggoy]

ok ill try it.. then ill send it to you .. thanks! whats your email?

 

[kulunggoy]

i create a relationship which is the left table is "item" then the right is "withdraw" then i select "item" field as a matching relationship. i create a query and i put all the fields from my "withdraw and item" table. Is it right? Then i dont know where do i put the code you gave to me.. do i put it in "criteria" in query? thanks so much again!!