Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2012
    Posts
    126

    Unanswered: Query to sum total in multiple fields based on date

    I know this can be done (to a degree) with a crosstab query but I need this to do more than what a cross tab query can offer. I have four feilds, Item#, Date, QtySold, TimesPackaged. I need to have Item, QtySoldThis Month, TimesPackaged this month, qtysold Last month, times packaged last month, qty sold 2 months ago, times packaged 2 months ago and so on for a total of four months. I can build a query for each month and then put them together but this seems like the wrong way to do this. Anyone have any other ideas?

    My sql:
    SELECT qryItemPickHistory.ItemNumber, qryItemPickHistory.Date, Sum(qryItemPickHistory.Bottles) AS SumOfBottles, Count(qryItemPickHistory.Bottles) AS CountOfBottles
    FROM qryItemPickHistory
    GROUP BY qryItemPickHistory.ItemNumber, qryItemPickHistory.Date
    HAVING (((Sum(qryItemPickHistory.Bottles))>0));

  2. #2
    Join Date
    Jan 2005
    Posts
    146
    Here is a bit messy attempt.

    Code:
    SELECT Sum(IIf(DateDiff("m",[SellDate],Date())=0,[QtySold],0)) AS QtySoldThisMonth, Sum(IIf(DateDiff("m",[SellDate],Date())=1,[QtySold],0)) AS QtySoldLastMonth, Sum(IIf(DateDiff("m",[SellDate],Date())=2,[QtySold],0)) AS QtySoldTwoMonthsAgo, Sum(IIf(DateDiff("m",[SellDate],Date())=3,[QtySold],0)) AS QtySoldThreeMonthsAgo, Sum(IIf(DateDiff("m",[SellDate],Date())=4,[QtySold],0)) AS QtySoldFourMonthsAgo, Sum(IIf(DateDiff("m",[SellDate],Date())=0,[TimesPackaged],0)) AS TimesPackagedThisMonth, Sum(IIf(DateDiff("m",[SellDate],Date())=1,[TimesPackaged],0)) AS TimesPackagedLastMonth, Sum(IIf(DateDiff("m",[SellDate],Date())=2,[TimesPackaged],0)) AS TimesPackagedTwoMonthsAgo, Sum(IIf(DateDiff("m",[SellDate],Date())=3,[TimesPackaged],0)) AS TimesPackagedThreeMonthsAgo, Sum(IIf(DateDiff("m",[SellDate],Date())=4,[TimesPackaged],0)) AS TimesPackagedFourMonthsAgo
    FROM Table2;

  3. #3
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by billmeye View Post
    Here is a bit messy attempt.

    Code:
    SELECT Sum(IIf(DateDiff("m",[SellDate],Date())=0,[QtySold],0)) AS QtySoldThisMonth, Sum(IIf(DateDiff("m",[SellDate],Date())=1,[QtySold],0)) AS QtySoldLastMonth, Sum(IIf(DateDiff("m",[SellDate],Date())=2,[QtySold],0)) AS QtySoldTwoMonthsAgo, Sum(IIf(DateDiff("m",[SellDate],Date())=3,[QtySold],0)) AS QtySoldThreeMonthsAgo, Sum(IIf(DateDiff("m",[SellDate],Date())=4,[QtySold],0)) AS QtySoldFourMonthsAgo, Sum(IIf(DateDiff("m",[SellDate],Date())=0,[TimesPackaged],0)) AS TimesPackagedThisMonth, Sum(IIf(DateDiff("m",[SellDate],Date())=1,[TimesPackaged],0)) AS TimesPackagedLastMonth, Sum(IIf(DateDiff("m",[SellDate],Date())=2,[TimesPackaged],0)) AS TimesPackagedTwoMonthsAgo, Sum(IIf(DateDiff("m",[SellDate],Date())=3,[TimesPackaged],0)) AS TimesPackagedThreeMonthsAgo, Sum(IIf(DateDiff("m",[SellDate],Date())=4,[TimesPackaged],0)) AS TimesPackagedFourMonthsAgo
    FROM Table2;
    Great idea! Let me give this a shot

Posting Permissions

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