Order Header Table Order Detail Table Order Number INNER JOIN Order Number Customer ID GROUP BY Item Number GROUP BY Invoice Date MAX (latest) Invoice Number Individual record Quantity Individual record Price Individual record Unit of Measure Individual record Discount Percent Individual record
I have created a query (without aggregation) using the two tables above. My objective is to find out the last time each item number was purchased by a customer based on order history. I want to GROUP BY Customer ID and Item Number, find the last date that the item was purchased, and then pull the remaining information in from the record that was selected. This initially seemed pretty easy, but I soon found out it is more difficult than I expected. I think I need to use a subquery in a query, but I'm having trouble figuring out the logical steps and the syntax. I want to use this query for a Customer Item Report and again for a Customer Item Enter/Edit Form. Can someone help?