Query to sum sales by SKU & portion of date

[toddi76]

I am trying to write an Access query that will report sales by SKU for a given month. The data table has the following columns:

OrderNumber    OrderDate        SKU     Title        QuantityShipped
10             1/23/2016        XXXX    Product X       1
11             3/12/2016        XXXX    Product X       1
12             10/2/2017        XXXX    Product X       1
13             12/22/2017       XXXX    Product X       1
14             4/25/2018        XXXX    Product X       3
15             4/27/2018        XXXX    Product X       1
16             1/15/2016        YYYY    Product Y       2
17             5/7/2017         YYYY    Product Y       1
18             5/18/2017        YYYY    Product Y       1
19             3/29/2018        YYYY    Product Y       3

I need only one line per SKU per month/year combination, i.e. output similar to this:

SKU      Title          Date     TotalShipped
XXXX     Product X     Jan-16     1
XXXX     Product X     Mar-16     1
XXXX     Product X     Oct-17     1
XXXX     Product X     Dec-17     1
XXXX     Product X     Apr-18     4
YYYY     Product Y     Jan-16     2
YYYY     Product Y     May-17     2
YYYY     Product Y     Mar-18     3

I can't seem to tease out the date format using DATEPART and I can't seem to isolate the SKU's into one line. Any hlep would be appreciated.

[zmbd]

toddi76,
Welcome to Bytes.com!

Normally we ask that you show your work; however, with some of the more complex things, or when the obvious isn't the easiest solution (red-herrings abound in Access - in this case, DatePart()!)... and I've done this type of summary dozens of times in the lab:

SELECT byte971242.SKU, byte971242.Title
   , Format([Odate],"mmm-yy") AS OrdrDate
   , Sum(byte971242.Qty) AS SumOfQty
FROM byte971242
GROUP BY byte971242.SKU
   , byte971242.Title
   , Format([Odate],"mmm-yy")
ORDER BY First(byte971242.Title)
   , First(byte971242.Odate);

I used this thread's id as a generic table name.
Shortened the [Title] entries a tad for the example :)
Instead of the DatePart() used the simple Format()
should give you (I prefer a 4 digit year the Above SQL will return the 2 digit year as shown in your OP):

The sort order is the tricky part :)
Here I used the [Title]; however, you can just as easily use the [SKU] depending on your requirement/preference.

[NeoPa]

If you use Format([ODate],'yyyymm') for your ORDER BY clause you should find it a little more straightforward.

You must use the same expression in the GROUP BY clause as you do in the SELECT one or it'll complain.

[zmbd]

NeoPa,
You are absolutely correct in the GROUP BY; however, the sort is happening in the ORDER BY so there is a bit of SQL-slight of hand being pulled here!

When you use the Format([ODate],'yyyymm') in place of the ORDER BY we then get the alphabetical sort by the Month name of [ODate] instead of the numerical Date sort - Tried this very early on for my lab reports - almost enough to drive a Chemist crazy... as if one could tell the difference @;@ ;

thus,

Either

SELECT t_byte971242.SKU, t_byte971242.Title, Format([Odate],"mmm-yyyy") AS OrdrDate
  , Sum(t_byte971242.Qty) AS SumOfQty
FROM t_byte971242
GROUP BY t_byte971242.SKU
  , t_byte971242.Title
  , Format([Odate],"mmm-yyyy")
ORDER BY First(t_byte971242.Title)
, First(Format([Odate],"mmm-yyyy"));

OR

SELECT t_byte971242.SKU, t_byte971242.Title
   , Format([Odate],"mmm-yyyy") AS OrdrDate
   , Sum(t_byte971242.Qty) AS SumOfQty
FROM t_byte971242
GROUP BY t_byte971242.SKU, t_byte971242.Title
   , Format([Odate],"mmm-yyyy")
   , t_byte971242.Title, Format([Odate],"mmm-yyyy")
ORDER BY t_byte971242.Title
, Format([Odate],"mmm-yyyy");

Yield the same alphabetical sort by month name instead of by numerical value (well, sort of... with this data a bit random - ):

Even with:
ORDER BY First(t_byte971242.Title), First(Format([Odate],"mmm-yyyy"));
the alphabetical sort on the month name happens.

As far as the Access-SQL parser is concerned the Format() is returning a string value

However, in VBA - the parser can be told to accept "Jan-2018" as a date value and all is well:

Attached the DB that the screen shots were taken from in the previous post (#2) :)

[NeoPa]

Hi Z.
I think you may need to check you're doing that right. Sorting by yyyymm sorts numerically by month - not alphabetically.
LMK if you're still getting wrong results after following my earlier instructions carefully.

[zmbd]

silly ole bear - I didn't catch the "YYYY" first and the change to "mm" - maybe I need more sleep, or honey.

SELECT t_byte971242.SKU, t_byte971242.Title
, Format([Odate],"mmm-yyyy") AS OrdrDate, Sum(t_byte971242.Qty) AS SumOfQty
FROM t_byte971242
GROUP BY t_byte971242.SKU, t_byte971242.Title
 , Format([Odate],"mmm-yyyy"),
 Format([Odate],"yyyymm")
ORDER BY First(t_byte971242.Title)
 , Format([Odate],"yyyymm");

I also tried with leaving the "First" so that the format by YYYYMM didn't appear in the group by - notice it's not in the select clause... curious that.
does indeed return the numeric sort as the previous...

[NeoPa]

ZMBD:
I also tried with leaving the "First" so that the format by YYYYMM didn't appear in the group by - notice it's not in the select clause... curious that.

Check back to my earlier post (#3) where I explain why certain things need to go where.

PS. I just looked again and, though I didn't really understand what you were saying, I now think you found the yyyymm was also required in the GROUP BY clause. That's a pain. The reason will be to do with the fact that unless it's in there it doesn't know that it's the same data as the mmm-yyyy and thinks it isn't aggregated unless it's in there. As I say - a pain.

So, it turns out that it needs to be in the GROUP BY clause even if it's in the ORDER BY clause and not the SELECT clause.

[zmbd]

NeoPa,
Yet using my original SQL, the first([Odate]) only appears in the ORDER BY clause of the SQL - avoiding the potential "expanded" records (where everything shows up) issue. I stumbled on this a few years back - my guess is that because everything is being grouped at the month and year level then the "First" conditional simply looks at the first record in the aggregate and takes that value for the sort.
Either method works, is reproducible across different datasets, and appears to be reliable.


Re-reading this, I don't think I was very clear:

I also tried with leaving the "First" so that the format by YYYYMM didn't appear in the group by

What I intended to say was - leaving the format(yyyymm) in the first() we still get the proper time-based sort:

SELECT t_byte971242.SKU, t_byte971242.Title
, Format([Odate],"mmm-yyyy") AS OrdrDate
, Sum(t_byte971242.Qty) AS SumOfQty
FROM t_byte971242
GROUP BY 
t_byte971242.SKU
, t_byte971242.Title
, Format([Odate],"mmm-yyyy")
ORDER BY 
First(t_byte971242.Title)
, First(Format([Odate],"yyyymm"));
 ^^^^^^^ Not in the Group Clause! ^^^^^^^

[NeoPa]

Yes Zmbd. That's a good solution. We know they're the same - and SQL frankly doesn't need to. Like it :-)

[toddi76]

This worked perfectly for me.

Thank you zmbd and NeoPa for your assistance!

[NeoPa]

Always a pleasure. I've just set Zmbd's last post as Best Answer as it has the actual solution posted in it.