Line Item Budget SQL Statement For Access 2003

Posted on 2012-08-22
Last Modified: 2012-08-22
I have an attached table that has my budget line items for 2012.  I need a SQL statement that will create the AccountID, AccountNumber and Detail fields for 2013.  I need the FiscalYear field to be 2013 and the months to be zero.  Can anyone help with this?
Question by:submarinerssbn731
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    There is no 2003 data in the table...

    Where is this to be used?
    In Access or Excel?

    If in Excel, then you can simply autoFill 2013 in all the rows you need.

    Why do you want Zeros in the Month cells?

    In other words, ...I'm confused....

    Can you first take a step back and explain exactly what you have here and what your ultimate goal is?

    ...But let's see what other experts post, in case I am just not understanding something...

    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    How about you just post the *exact* output you are looking for?

    Author Comment

    The original Excel sheet is an export from an Access 2003 database table.  The file represents our accounts for creating our companies budgeting process.  I would like an SQL  statement that would replicate the 2012 line items and create 2013 line items in the same table.  I have attached a revised Excel sheet showing the desired output that I would like appended to the original table.  I hope this helps.  Thanks!
    LVL 39

    Accepted Solution

    If you like to add records to the same table (LineItemDetail, you can use this query:
    INSERT INTO LineItemDetail ( FiscalYear, AccountID, AccountNumber, Detail )
    SELECT IIf([FiscalYear]=2012,2013,0) AS Y, LineItemDetail.AccountID, LineItemDetail.AccountNumber, LineItemDetail.Detail
    FROM LineItemDetail
    WHERE ([FiscalYear]=2012);

    Open in new window


    Author Closing Comment

    Great answer...Thanks!!!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Access Web App: Primary & Foreign Key, Fetch Values? 12 28
    TSQL Update Field Older Than 5 Days 10 36
    Order by 8 20
    query question 4 20
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    734 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

    20 Experts available now in Live!

    Get 1:1 Help Now