Tax Year Calculations

Posted on 2011-05-10
Last Modified: 2012-05-11
I have transaction data which I want to group into Tax Years April to April.  Could this be done linking to a table which stores the Tax Year dates or will there need to be a separate query for each year/transaction catagory?
Question by:JSSenior
    LVL 77

    Accepted Solution

    I would have thought that a tax year table would be a better solution.

    You would need a table that had the Tax year name  and then the start and end dates as separate fields.

    You can then use an update query on the transaction table to allocate the correct taxyearname to each transaction.

    Update transactions inner join taxtable
    on transactions.transactiondate >=taxtable.startdate and transactions.transactiondate<=taxtable.enddate  
    set TaxYearName =  taxtable.taxyearname

    Author Comment

    Hi i cant seem to get the sql right what would it look like in design view?
    LVL 77

    Expert Comment

    Can't be done in design view because it restricts you to relationships based on equality.

    Post your version.

    Author Closing Comment

    Managed to do it in design view. Did not use a relationship
    LVL 77

    Expert Comment

    Ok - a where condition will achieve the same result .

    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

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    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…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    728 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

    19 Experts available now in Live!

    Get 1:1 Help Now