one to many relationship

[Jeff Warner]

I have a customer table and an order table. I want to create a report that selects all customers whose orders have all been placed two opr more years. If any one order has an order date < two years, I don't want to select that customer. So its' a one to many relationship. The tables look like this:

Customer file: order file:

cust number order number
customer name date ordered
last invoice date date billed
balance due amount billed
date last paid customer number

I think I must use an inner join using the customer number in both tables. Is there an SQL query or Select statement that meets this selection criteria? Or do I need a sub report that selects all the orders I don't know that syntax.

 

[pmax9999]

Using an inner join will work, as long as you don't want to see customers who have never had any orders.

Create a group on the Customer Number field, and use a Group Select statement (Report ==> Selection Formula ==> Group) to display only those where the last order (ie Maximum of the Order Date for that customer) is more more than 2 years ago.

A group select formula along the lines of the following should work:

Maximum({Table.Order.Date}, {Table.Cust_No}) < DateAdd('yyyy', -2, CurrentDate)

Use your actual field names.

Hope this helps.

Regards
Pete

 

[lbass]

Your post is a little unclear. If you are looking for customers whose orders have all occurred within the last two years, then the group selection formula should be:

Minimum({table.OrderDate},{table.customerno})>=dateadd(‘yyyy’,-2,currentdate)

If you are looking for customers who haven’t had an order in the last two years, then Pete’s formula is the one to use.

-LB