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

Posted on 2012-08-25
Last Modified: 2012-08-26
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.
Question by:wlwebb
    LVL 29

    Accepted 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

    Author Closing Comment

    Thanks Irog...  That did the trick

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now