Completely group by month in query?

Question

I am trying to write a SQL query in Access and it is driving me crazy.

Table 1 has my date column (which will only have either only the 1st or the 15th of a month, in format mm/dd/yyyy). I'm also using the ID number here for each field to get the count. It has dates back to 2013 but I only want 2018.

Table 2 has the month in format (mmmmm/yy) and the projection for quantity that month.

The goal again is to be able to join both tables to compare actual vs projected.

Whatever query I use though, when I join, I'm not getting the right totals. I.e. January of 2018 could have had a total of 350 (between the 1st and 15th), but when I join the tables, only the records from the 1st of each month will come across, and show 300 for example for January of that year. I've been playing around with the date formats like crazy and for whatever reason, it refuses to work like I want it to. If I do a Left Join it will show 2 entries for each month-no corresponding projection data from the 15th dates on the table 2, so that's gotta be the problem. Just can't fix it. Here's my query below. Thank you so much.

SELECT Format(([Table1].[Date]), "m/yy") AS [Month],
   Count([Table1].[ID]) AS Total,
   [Table2].Projection,
   FROM [Table1]
   INNER JOIN [Table2]
   ON [Table1].[Date] = [Table2].[Projection Date]
   WHERE ((([Table1].[Date]) Between [Enter Begin Date] And [Enter End Date])) 
   '''using all of 2018, so between 1/18 and 12/18
   GROUP BY Format(([Table1]),"m/yy"),[Table2].[Projection];

Sample of Table 1 data:

ID       Date
1107     1/01/2018
1233     1/15/2018
1500     2/1/2018

Sample of Table 2 Data:

Projection Date     Projection
January18              270
February18             283

Sample output of using the code above:

Date     Total    Projection
Jan18     300         270
Feb18     301         283
March18   373         300

Sample output of using a left join instead:

Date     Total    Projection
Jan18     300         270
Jan18     50          
Feb18     301         283
Feb18     60
March18   373         300
March18   38

Desired Output

Date     Total    Projection
Jan18     350         270
Feb18     361         283
March18   411         300

Edit your question and provide sample data and desired results. — Gordon Linoff, Aug 30 '18 at 02:06
As stated, it's impossible to say what's wrong without seeing what's involved and knowing what result you expect. — ashleedawg, Aug 30 '18 at 02:42
When you say `Table 2 has the month in format (mmmmm/yy)`, does that imply the field is stored as a **text**? (Values in a table with a data type of `Date` aren't formatted.) — ashleedawg, Aug 30 '18 at 02:46
First of all change all date fields to datatype `Date/Time`. A value of "January 2018" can be stored as the first of the month, so `1/1/2018`. As you may have noticed, this can be quite essential when computing with these values. — Wolfgang Kais, Aug 30 '18 at 07:41

T4roy · Answer 1 · 2018-08-30T03:32:43.263

You need to convert [Table1].[Date] to a nvarchar in your INNER JOIN, which should be a LEFT JOIN.
I assume [Table2].[Projection Date] is nvarchar "string"?
Your GROUP BY needed to match the non-aggregates in the SELECT.
Also on your SELECT you will want to return 0 projection when join returns NULL.

If [Table2].[Projection Date] is stored as text try using the below;

    SELECT 
        Format(([Table1].[Date]), "MMM/yy") AS [Month],
        Count([Table1].[ID]) AS Total,
        ISNULL([Table2].Projection, 0) AS 'Projection'
    FROM [Table1]
        LEFT JOIN [Table2]
            ON FORMAT([Table1].[Date], 'MMM/yy') = [Table2].[Projection Date]
    WHERE ((([Table1].[Date]) Between [Enter Begin Date] And [Enter End Date])) 
    GROUP BY Format(([Table1].[Date]),"MMM/yy"),[Table2].[Projection];

If [Table2].[Projection Date] is stored as a date, you will need to add EOMONTH() to the join and no need to format [Table1].[Date], try this;

SELECT 
    Format(([Table1].[Date]), "MMM/yy") AS [Month],
    Count([Table1].[ID]) AS Total,
    ISNULL([Table2].Projection, 0) AS 'Projection'
FROM [Table1]
    LEFT JOIN [Table2]
        ON EOMONTH([Table1].[Date]) = EOMONTH([Table2].[Projection Date])
WHERE ((([Table1].[Date]) Between [Enter Begin Date] And [Enter End Date])) 
GROUP BY Format(([Table1].[Date]),"MMM/yy"),[Table2].[Projection];

Sorry for all the edits!

Completely group by month in query?

1 Answers1