Sum of one field, many rows, one 'client'

[afterdigital]

Hi everyone,

i have 3 tables (table1 'customer', table2 'inventory', table 3 'orders'). I've created an orders report which consist of info from customers and inventory. When the report is viewed, each customer has all orders directly under him ( so the next customer has all orders directly under him and so forth). How can i calculate one field from many orders? Example would be 'number of items' in each order. Say there is 5 orders and each order has 'two items', that would be a total of 10.

I have tried the following:
<cfquery name=&quot;calculate&quot; query=&quot;db&quot;>
select sum(item) as calitem
from table1, table2
where table1.customerid = table2.customerid
</cfquery>

I am using access as my database. Could my syntax be wrong. I've also tried queryofqueries but no success.

Thanks in advance for your replies...

Javier
NYC

 

[CFDude]

There are many ways to do this -- the code you posted should work, but it may be just as easy to add it up as you cfoutput it.

e.g:

<cfset TotalItems = 0>

<cfoutput query=&quot;myQuery&quot;>
#Customer# Number of items: #NumItems#
<cfset TotalItems = TotalItems + NumItems>
<!--- Where numitems is the number of items per customer --->

</cfoutput>

<cfoutput>Total Number of Items: #TotalItems#</cfoutput>