Access - Append query to record with a "Run" sequential number for that date only
Posted on 2012-08-25
I am importing Client Trial Balances into a db. The db will have multiple period ends of Trial Balances. However, it can have multiple runs (or versions) of TrialBalances for the same period.
As part of what I am doing, I am creating an Append query. The table being appended to has a field called TBRunNbr (Double). As part of that Append query overall routine I want that query to look at the table that is being updated to see if there is already a record for that date and if there exists a record for that date I want the "TBRunNbr" field to increment the TBBRunNbr by 1. The TBRunNbr is not a PrimaryKey field and the TBRunNbr can have duplicates (ie if I have T/B for period ends 12/31/10 and 1/31/11 each will have a TBRunNbr 1)
IE if the client sends me a trial balance and I work on the part I do and then they send me an updated Trial Balance for that period (which they have a habit of doing) my db will have multiple Trial Balances for the same period. Thus in order to have a trail of what's being changed I want to be able to track a "Run Number" of the trial balance they've sent.
Can this be done as part of an Append Query?
The Code I have to get the PeriodEnd Date is
SELECT Last(Temp_TB_Import.PeriodEnd) AS LastOfPeriodEnd, Temp_TB_Import.TBStatus
GROUP BY Temp_TB_Import.TBStatus;
I am then going to copy this to an Append Query to update my ClientTBControl table.