I've got some data that's in the format of:-
Account No. | Credited Date | Credit Amount | Credit Note Number
123456 | 01/04/2012 | 100 | 3000123
123456 | 05/04/2012 | 200 | 3000124
654321 | 01/04/2012 | 100 | 3000125
456789 | 01/04/2012 | 100 | 3000126
So there can be multiple credits per account number; and I need to put this into a format that is:-
Account No. | Credited Date1 | Credit Amount1 | Credit Note Number1 | Credited Date2 | Credit Amount2 | Credit Note Number2
Up to six credit notes per account number, (although there's potential for the data to have more, in which case I'd just want to take the first six and disregard the rest for that account).
One option is to put the data in Excel and then sort by Account Number then Credited Date, and use an IF statement looking at the account number to number the credits 1 through 6 and then mark the rest for deletion, and then take this into Access and do a crosstab query to create the columns I need; but this is for a data set I have to prepare each month and I have the rest of the data set up on queries linked by macros so it can literally be done at the click of a button as I need to be able to pass the task onto others to perform in case of my absence; so I'm really looking for a way to do this with queries in Access so it can be included in the macro.
Any help is much appreciated, and if I've not explained what I need very well please ask me to clarify any details.
Account No. | Credited Date | Credit Amount | Credit Note Number
123456 | 01/04/2012 | 100 | 3000123
123456 | 05/04/2012 | 200 | 3000124
654321 | 01/04/2012 | 100 | 3000125
456789 | 01/04/2012 | 100 | 3000126
So there can be multiple credits per account number; and I need to put this into a format that is:-
Account No. | Credited Date1 | Credit Amount1 | Credit Note Number1 | Credited Date2 | Credit Amount2 | Credit Note Number2
Up to six credit notes per account number, (although there's potential for the data to have more, in which case I'd just want to take the first six and disregard the rest for that account).
One option is to put the data in Excel and then sort by Account Number then Credited Date, and use an IF statement looking at the account number to number the credits 1 through 6 and then mark the rest for deletion, and then take this into Access and do a crosstab query to create the columns I need; but this is for a data set I have to prepare each month and I have the rest of the data set up on queries linked by macros so it can literally be done at the click of a button as I need to be able to pass the task onto others to perform in case of my absence; so I'm really looking for a way to do this with queries in Access so it can be included in the macro.
Any help is much appreciated, and if I've not explained what I need very well please ask me to clarify any details.