I'm simplifying my actual situation for illustrative purposes, so please read through everything I've written before you answer.
Let's say I have an existing table (Table1) containing 3 columns: Customer Number, Transaction Date, and Transaction Number. The data in the Table1 might look like this:
Customer Number Transaction Date Transaction Number
111 1/1/2000 1
111 12/15/2002 2
111 7/1/2005 3
222 10/1/1999 1
222 9/1/2007 2
333 1/1/2007 1
333 6/30/2007 2
As you can see in the above table, the same Customer Number can appear multiple times. Also note that the Transaction Number relates to individual Customer Numbers. In other words, if a given customer purchases something for the first time, he is assigned a Transaction Number of 1. If that same customer purchases something again, he is assigned a Transaction Number of 2. If a DIFFERENT customer comes in for the first time, he is assigned a Transaction Number of 1. If he comes in again, he is assigned a Transaction Number of 2, etc, etc.
What I need to do:
1. I need to create a new table (I'll call it Table2) and load it with unique Customer Numbers (each Customer Number can only occur once) based on the most current record for each Customer Number ( using the Transaction Date field or the Transaction Number field - please show me both ways) in Table1. I need to point out here that using the Customer Number field alone to select unique records is not good enough. I need to select the most current record ( identifiable by the values in the Transaction Date or Transaction Number columns) So for a given Customer Number, I need to somehow be able to identify the most current date record or the highest numbered Transaction Number for that Customer (which would also indicate the most current record).
2. Nightly I need to update the table I created (Table2) with the most up-to-date records from Table1. This could include either entirely new Customer Numbers (which would have a Transaction Number of 1 for that day) or existing Customer Numbers with new transactions ( for example, the prior Transaction Number might have been 3 and now it's 4).
So in summary, I need to load an empty table with unique Customer Numbers based on the most current Transaction Date or Transaction Number (using either one will work but please show me both ways).
AND I need to update the new table (Table2) nightly with updated records from Table1.
Again I need to stress..........using the Customer Number field alone to select unique Customer Numbers is not good enough. I need to select the most current records from Table1 for each Customer Number. I've left out numerous fields from my actual table, so you can't see entirely what's going on. I only included fields needed to achieve what I'm trying to achieve.