Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 672
  • Last Modified:

Access - Append query to record with a "Run" sequential number for that date only

Hello all
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
FROM Temp_TB_Import
GROUP BY Temp_TB_Import.TBStatus;

I am then going to copy this to an Append Query to update my ClientTBControl table.
1 Solution
I'm not sure why you have the data type for TBRunNbr set to Double when it will only be holding whole numbers.  Integer data type would be more appropriate.  You could use something like this:

RunNum: NZ(DMax("TBRunNbr","ClientTBControl","PeriodEnd"),0)+1
wlwebbAuthor Commented:
Thanks Irog...  That did the trick

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now