performing find on a related table but keeping all records in the parent table
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 !!!