Have some slight problem filtering my data based on a summary field.

Situation:
2 databases: [customer], [order]
each record in database [order] has a customerid , which is the foreign
key to database [customer]
database [order] has also a counted summary field "number_of_orders".

I have a report, which groups by all orders based on a customer.
In the group-by header (print-layout), the number of orders for each
customer is displayed, including the order-detail-records.

Problem is: How do I filter my resultset, displaying only customers
which have more than 2 orders.
Otherwise in SQL:

select c.customerid, count(o.orderid)
from [customer] c
inner join [order] o
on o.customerid = c.customerid
group by c.customerid
HAVING count(o.orderid) >2

Hope someone can help me out.

Regards,
Twan Kennis

--------------------------------------------------------------
SKB Vragenlijst Services
Postbus 12354, 1100 AJ Amsterdam
Bezoekadres J. Geesinkweg 821, 1096 AZ Amsterdam
Telefoon 020 462 78 90, Fax 020 462 78 99
E-mail computer (AT) skb (DOT) nl, Website www.skb.nl

Found out 1 part of the solution myself, one part to solve.

table [order]
"orderid" = number
"customerid" = number
"const" = calculated field: =1
"date" = date field

relationship: [Order].customerid = [Customer].customerid

table [customer]
"customerid" = number
"ordercount" = calculated field: Count(order::const)

relationship: [Customer].customerid = [Order].customerid

Because of the relationship backwards (customer --> order), the number
of orders for a specific customer can be counted by presenting the
calcultaed field "ordercount".

The part to solve:

The counted number of orders for a specific customer is always a total
count for all records.
How to present the number of orders per customer, bought within a
specific (user selectable) date-range.

Regards, Twan
SKB Vragenlijst Services, automatisering




"Twan Kennis" <computer (AT) skb (DOT) nl> wrote

In article <12ntr32nval3658 (AT) corp (DOT) supernews.com>, "Twan Kennis"
<computer (AT) skb (DOT) nl> wrote:


Create three new fields in the Order table:

g_StartDate Global, Date

g_EndDate Global, Date

c_InDateRange Calculation, Number result
= If((Date < g_StartDate) or (Date > g_EndDate),
0,
1)

Then change you OrderCount field to sum this new Calculation field.
ie.
OrderCount Calculation, Number result
= Sum(Order::c_InDateRange)

This field will now give you a total of the number of Order records a
customer has, but only for dates between the dates entered into
g_StartDate and g_EndDate.


Now, to Find the orders to print the report on you can simply:

- Enter the first date of the date range into the g_StartDate field

- Enter the last date of the date range into the g_EndDate field

- Perform a Find putting ">1" (or ">=2") into the OrderCount field

You should get a found set that contains only the Order records for
customers that have 2 or more orders between the two dates.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Tremendously found!

Twan


"Helpful Harry" <helpful_harry (AT) nom (DOT) de.plume.com> wrote