Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

Tax Year Calculations

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?
0
JSSenior
Asked:
JSSenior
  • 3
  • 2
1 Solution
 
peter57rCommented:
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
0
 
JSSeniorAuthor Commented:
Hi i cant seem to get the sql right what would it look like in design view?
0
 
peter57rCommented:
Can't be done in design view because it restricts you to relationships based on equality.

Post your version.
0
 
JSSeniorAuthor Commented:
Managed to do it in design view. Did not use a relationship
0
 
peter57rCommented:
Ok - a where condition will achieve the same result .
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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