Results 1 to 3 of 3
  1. #1
    Join Date
    May 2013
    Posts
    56

    Unanswered: YYYY/MMM In Crosstab Query

    I am using Access 2013 and trying to create a crosstab query that shows months as columns and the costs as the value.

    Is there an easy way to sum dollars by date and show the Year and Month? I am able to create the query but the results lump all January dates regardless of the year in the same column. The data is entered by Date (MM/DD/YYYY) and the Costs in a record.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how are you pivoting the data?
    make certain you specify year and month. if in doubt format your date data using format(mydatecolumn,"YYYYMM")

    the data entry format is irrelevant, PROVIDING you are storing sate vales in a column of type date. if you store it as text then you are stuffed with a capital F.

    Its irrelavent as Access, like all databases, uses its own internal storage mechanism which doesn't care about external representation, providign it can recognise what you are sendign it.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2013
    Posts
    56
    I have been able to display what I need, but it is in "format" and cannot be used in a comparison. What I am trying to do is create a table that sums data by year/month into two different fields plan vs actual from two different tables. One table has the forecasted data by project number by date; the other has the actual invoice data by project # by date. I want to merge the two by month/year and ignore the day.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •