I have a fixed length ASCII table that has 55,000 records in it. I have imported the table into MSAccess 2003 and established an Autonumber field as an index. Each record is an ACCOUNT record and has detail about the transaction for each account record.
The problem; the first record for each account has the account number, however an account may have 10 or more transaction records. The account number field is populated in the first record only. I won’t go into detail as to why the data is in such a state unless you need more information for a solution.
What I need to do is write an SQL Statement that will look at a field and if it is NULL, populate the Account Number field with the Account Number form the first record in the series.
Here is an example of what the data looks like
ID |ACCTNUM | Debits|Credits| Date |
1|123456 | $100| | 6/01/2007|
2| | $ 20| | 6/02/2007|
3| | | $120 | 6/15/2007|
4|987654 | $37| | 5/23/2006|
5| | | $15| 5/28/2006|
6| | | $22| 6/15/2007|
As I stated there are 55,000 records and about 12,000 accounts. As you can see this creates a major problem for data conversion. If you can help, I would gratly appreciate and advice you can give.
Thanks, Ray