problem with sum of records

[JasnaP]

Hello all,

I have two tables, one table contains different products, their prices, category ID, etc.
The other table contains category names and category ID (each product belongs to a certain category).

Then I made a query, it looks something like this:

nr. product price1 price2 category
1 p1 100 150 c1
2 p2 200 c2
3 p3 300 300 c3

I would like to create a report based on this query. I have to show product categories and
products in a report. I also have to sum prices of the products of each category. But it
should be a sum of only those lines who have all cells filled (in my case, I would only
need a sum of p1 and p2). So, the report should contain all products and should contain
the sum of only those products whose lines have all cells filled. How can I do this??

Any help would be very much appreciated!

Jasna

 

[koresnordic]

I am assuming you mean p1 & p3. And that you still want all threeelines to show, just not all three in the sum. In which case, I would create a query and base the report on that. The query would have three extra columns. The first would contain something along the lines of &quot;p1check:iif([price1}<>0 and [price2]<>0,[price1],0)&quot; and the second would be &quot;p2check:iif([price1]<>0 and [price2]<>0,[price2])&quot; and finally column 3 &quot;pricesum:iif([price1]<>0 and [price2]<>0,[price1]+[price2],iif([price1]<>0,[price1],iif([price2]<>0,[price2],0))). Hope this gives you something to work with.

Graham

 

[JasnaP]

Hello Graham,

thanks for your reply.
The problem is my tables contain hundreds of records...
is there any other possible solution?

Thanks in advance
Jasna