×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

one to many relationship

one to many relationship

one to many relationship

(OP)
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.

RE: one to many relationship

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:

CODE

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

Use your actual field names.

Hope this helps.

Regards
Pete

RE: one to many relationship

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close