AnsweredAssumed Answered

performing find on a related table but keeping all records in the parent table

Question asked by med on Dec 23, 2011
Latest reply on Dec 26, 2011 by med

Title

performing find on a related table but keeping all records in the parent table

Post

I have a customer table and an order table, related by customer ID. I need to produce a report for customers in a certain city listing their last order PRIOR to a certain date which is determined at the time of producing the report.

The first step I did is that I went to the database diagram and modified the relation to sort the order table descending by date.

Then I had a problem performing the find.

If I perform a find on the order table first to exclude ordes more recent to the cutoff date. The find is destroyed when I perform a find on customer city. 

If I perform a find on customer city and order date in one step all customers who have orders after the cutoff date do not show.

If I add to this extend find for customers again then all the orders recent to the cutoff date show.

So What can I do?

I do not want to produce a copy of the subset of orders into another table and use it for the report because the table is huge and will take a long time. There must be a better way !!!

Outcomes