I have restaurant winth dishes of two foods each. Therefore i have to create three forms:

1. "foods" Contains fields "name"-txt and "price"-currency

2. "dishes" Contains a field "name"-txt, fields "food1" and "food2" which are lookup links on the foods.name 

3. "sales" Contains a field "dish" which is lookup link on the dishes.name and a field "number" is integer.

i want a report which can show me a total data where number of sales is multiplicated with price of foods 

smthng like (sorry for my sql)

select foods.name as name, sum(sales.number * foods.price) as totl from foods dishes sales where sales.dish = dishes.name and (dishes.food1 = foods.name or dishes.food2 = foods.name) group by foods.name;

result like:

name      totl

potato      50$

fish      30$

tomato      70$

but reports on ZC can be based on the one table only. What can i do?