How to get Total form lookup fields

S

sebastico

I have the table

Table Herd
IDUT Autonumber
CodFarmer txt
CodSemester txt
CodAnimal txt (lookup)
AnimalQuantity number
AnimalUnit number


I create the following sql to get the AnimalCarryingCapacity

SELECT F2Herd.CodFarmer, F2Herd.CodFarmer, F2Herd.CodAnimal,
F2Herd.AnimalQuantity, F2Herd. AnimalQuantity, [F2Herd]![
AnimalQuantity]*[F2Herd]![AnimalUnit] AS AnimalCarryingCapacity

FROM F2Herd

WHERE (((F2Herd.CodFarmer)="11") AND ((F2Herd. AnimalQuantity)>0));

The table shows all the AnimalCarryingCapacity per CodAnimal, CodSemester
and CodFarmer.

How can I create another table showing the CodFarmer with its total of
AnimalCarryingCapacity per CodSemester?

Your help is highly appreciated
 
M

Michel Walsh

SELECT SUM(AnimalQuantity*AnimalUnit), CodFarmer, CodSemester
FROM herd
GROUP BY CodFarmer, CodSemester


Hoping it may help,
Vanderghast, Access MVP
 
S

sebastico

Works excellent. Thanks againg
Michel Walsh said:
SELECT SUM(AnimalQuantity*AnimalUnit), CodFarmer, CodSemester
FROM herd
GROUP BY CodFarmer, CodSemester


Hoping it may help,
Vanderghast, Access MVP


sebastico said:
I have the table

Table Herd
IDUT Autonumber
CodFarmer txt
CodSemester txt
CodAnimal txt (lookup)
AnimalQuantity number
AnimalUnit number


I create the following sql to get the AnimalCarryingCapacity

SELECT F2Herd.CodFarmer, F2Herd.CodFarmer, F2Herd.CodAnimal,
F2Herd.AnimalQuantity, F2Herd. AnimalQuantity, [F2Herd]![
AnimalQuantity]*[F2Herd]![AnimalUnit] AS AnimalCarryingCapacity

FROM F2Herd

WHERE (((F2Herd.CodFarmer)="11") AND ((F2Herd. AnimalQuantity)>0));

The table shows all the AnimalCarryingCapacity per CodAnimal, CodSemester
and CodFarmer.

How can I create another table showing the CodFarmer with its total of
AnimalCarryingCapacity per CodSemester?

Your help is highly appreciated
Click to expand...
Click to expand...
 
Top