query won't group by month

[robin a]

Hi,
I have a query based on a table [Ftag Data tbl] that returns [RECORD DATE], a total of all records where [DATE COMPLETED] IS Null, and a total of all records where [DATE COMPLETED] is not null. The dates have the criteria of Between [Forms]![Chart_selection]![TxtBDate] And [Forms]![Chart_selection]![TxtEDate]
Which is a form i use to select criteria. My problem is that I need the totals to be grouped by month instead of showing all of the dates. My query is coded as follows:

Month: (Format([RECORD DATE],"mmm/yyyy")) Total: Group By

then

Open: Sum(IIf([Ftag Data tbl]![DATE COMPLETED] Is Null,1,0)) Total: Expression

then

Closed: Sum(IIf([Ftag Data tbl]![DATE COMPLETED] Is Not Null,1,0)) total: expression

Then

Month([RECORD DATE]) Total: Group By, Sort: Ascending, Show: Unchecked, Criteria Between [Forms]![Chart_selection]![TxtBDate] And [Forms]![Chart_selection]![TxtEDate]

I just can't get this to group by month. I've attached a snapshot of my query if this helps. Thank you!

[zmbd]

You have a few problems that seeing what access has written for the SQL would make it easier to help you.

If you will change your query builder from designer-view (as shown in the jpg you posted) to SQL-View (several ways to do this however in 2010, open the query in design mode, then in a clear area of the table section right-click, in the context menu select SQL view and this works well in other versions too); then select the text that shows up and do a copy/paste to the site... once in your post... select the SQL text in the post and click on the <Code/> button in the editor.

Just a few other issues I noted:
You are using the reserved name "Month" as a field name
You are using the reserved name "Open" as a field name
It is normally best practice not to have spaces or non-alphanumerics (other than the underscore) in the table names nor field names... this might cause you issues later down the road.

-z

[zmbd]

Wow was yesterday a hectic day for me!
Non-stop kids and family...

Part of the issue is that the last field is malformed in that it should have a name such as "Expr1:"
Part of the issue is the use of reserved words in the fields.

As you've posted the work you tried and I got some more sleep!!! I built a small test database against one I already run that works.

Here's what I did while looking at your issue.
Table:
Name: tbl_FtagData
[PK_AutoNum] autnumber, primarykey
[RecordDate] date/time, required
[DateCompleted] date/time, nulls allowed

+Notice that I have renamed your table. I pulled out all of the spaces and added a primary key. Many of us use something like this: http://en.wikipedia.org/wiki/Leszyns...ing_convention The main thing here is to use only alphanumeric charactors and the underscore... no spaces. Use upper and lowercase if needed for clarity. Programing and SQL you need a list of reserved words http://msdn.microsoft.com/en-us/libr...(v=vs.80).aspx Do NOT use these as variable names in your VBA code and best practice is not to use them as field or table names either (if you read this http://msdn.microsoft.com/en-us/libr...v=sql.80).aspx you should note that there is quite the overlap between tables!)

Query:
Using the same code you posted... merely changed the names:
name: qry_as_op_built

SELECT (Format([RECORDDATE],"mmm/yyyy")) AS MonthandYear,
   Sum(IIf([tbl_FtagData]![DATECOMPLETED] Is Null,1,0))
      AS OpenRecords, 
   Sum(IIf([tbl_FtagData]![DATECOMPLETED] Is Not Null,1,0))
      AS ClosedRecords
FROM tbl_FtagData
GROUP BY (Format([RECORDDATE],"mmm/yyyy")), 
   Month([RECORDDATE])
HAVING (((Month([RECORDDATE])) 
   Between [Forms]![Chart_selection]![TxtBDate] 
      And [Forms]![Chart_selection]![TxtEDate]))
ORDER BY Month([RECORDDATE]);

I then used the following data tbl_FtagData

PK_AutoNum;     RecordDate;     DateCompleted; 
1;              1/1/2012;       2/1/2012;
2;              1/2/2012;       2/2/2012;
3;              1/3/2012;       (null);
4;              2/1/2012;       3/1/2012;
5;              2/2/2012;       (null);
6;              2/3/2012;       (null);
7;              3/1/2012;       4/1/2012;
9;              3/2/2012;       4/2/2012;
10;             3/3/2012;       4/3/2012;
11;             4/1/2012;       (null);
12;             4/2/2012;       5/1/2012;
13;             4/3/2012;       (null);
14;             5/1/2012;       6/1/2012;
15;             5/2/2012;       (null);
16;             5/3/2012;       (null);

Where the (null) is a null value in the field NOT the word null.

Ran the query using, 20120101 and 20120601 as the date ranges, with the following results:

MonthandYear;   OpenRecords;    ClosedRecords; 
Feb/2012;       2;              1; 
Mar/2012;       0;              3; 
Apr/2012;       2;              1; 
May/2012;       2;              1;

This appears to do a you have asked it to do...

-z

[zmbd]

Sorry,
Had to goto mass and then there was a parade...

To continue, I would break your issue into a subquery against tbl_FtagData to filter out the required data and then do the totals query otherwise you might run into some issues where you don't return the expected data:

SELECT 
   (Format([subqry].[RECORDDATE],"mmm/yyyy")) AS MonthandYear, 
   Sum(IIf([subqry].[DATECOMPLETED] Is Null,1,0)) AS OpenRecords, 
   Sum(IIf([subqry].[DATECOMPLETED] Is Not Null,1,0)) AS ClosedRecords
FROM (SELECT 
      tbl_FtagData.PK_AutoNum, 
      tbl_FtagData.RecordDate, 
      tbl_FtagData.DateCompleted, 
   FROM 
      tbl_FtagData
  WHERE 
        Format([recorddate],"m/yyyy") 
        Between Format([Forms]![Chart_selection]![TxtBDate],"m/yyyy") 
      And 
        Format([Forms]![Chart_selection]![TxtEDate],"m/yyyy"))  AS subqry
GROUP BY Format([RECORDDATE],"mmm/yyyy"), Month([recorddate])
ORDER BY Month([recorddate]);

When ran against the same data and the same start and end dates you get:

MonthandYear	OpenRecords	ClosedRecords
Jan/2012             1                2
Feb/2012             2                1
Mar/2012             0                3
Apr/2012             2                1
May/2012             2                1

Notice that now we get Jan/2012 results whereas we didn't get that information from the OP query. Also notice lines 12 thru 15 where we hav Format([recorddate],"m/yyyy")... this way if the user types in 2012-01-01 or 2012-01-02 you'll still get the same record counts for the month of January/2012 otherwise the counts would have changed to 1 and 1 instead of the 1 and 2.

-z

[robin a]

Thank you so much for your help. Here is the SQL from the query:

SELECT 
    Format([RECORD DATE],"mmm/yyyy") 
        AS MonthandYear, 
    Sum(IIf([FtagData_tbl]![DATE COMPLETED] Is Null,1,0)) 
        AS OpenRecords, 
    Sum(IIf([FtagData_tbl]![DATE COMPLETED] Is Not Null,1,0)) 
      AS ClosedRecords
FROM 
    FtagData_tbl
GROUP BY 
    (Format([RECORD DATE],"mmm/yyyy")), 
    Month([RECORD DATE])
HAVING 
    (Month([RECORD DATE]
        Between [Forms]![Chart_selection]![TxtBDate] 
            And
                [Forms]![Chart_selection]![TxtEDate])
ORDER BY Month
    ([RECORD DATE]);

[robin a]

oh, and i am using access 2007. I did change the ftagdata_tbl name but i kept RECORD DATE as is for now. My query returns no results, so i didn't get the same as yours. my PK for the ftagdata_tbl is TAG_ID. I don't know why yours works great and mine doesn't.

[zmbd]

Ahh... and that's a trick too... try entering just month and year (1/2012 and 4/2012) otherwise it may have something else to do with your data.

I'm not really to happy with the query posted in #3 as it has that month/year quirk and doesn't seem to like January.

The sql in #4 likes January and it likes the date no matter what format one uses. The other thing is that if the user enters 1/2/2012 or 1/1/2012 or even 1/31/2012 as start dates (or the equivalent for end dates) then the results include the entire monthly data in the months of interest not just the fraction...

-z

[robin a]

Hi again,
I have two queries now. If I choose dates on my form from the present year, then the results are fine, but, if i choose 2011 or something else, it all goes out of whack. I'm at a total loss. here is the sql for my first query:

SELECT DISTINCTROW (Format([RECORD_DATE],"mm/yyyy")) AS Record_Month, FtagData_tbl.LINE, FtagData_tbl.TYPE, Sum(IIf([FtagData_tbl]![DATE_COMPLETED] Is Null,1,0)) AS [Open], Sum(IIf([FtagData_tbl]![DATE_COMPLETED] Is Not Null,1,0)) AS Closed 
FROM FtagData_tbl 
GROUP BY (Format([RECORD_DATE],"mm/yyyy")), FtagData_tbl.LINE, FtagData_tbl.TYPE, FtagData_tbl.RECORD_DATE 
HAVING (((FtagData_tbl.LINE)=[Forms]![Chart_selection]![CboLine]) AND ((FtagData_tbl.TYPE)=[Forms]![Chart_selection]![CboType]) AND ((FtagData_tbl.RECORD_DATE) Between [Forms]![Chart_selection]![TxtBDate] And [Forms]![Chart_selection]![TxtEDate])) OR (((FtagData_tbl.TYPE)=[Forms]![Chart_selection]![CboType]) AND ((FtagData_tbl.RECORD_DATE) Between [Forms]![Chart_selection]![TxtBDate] And [Forms]![Chart_selection]![TxtEDate]) AND (([Forms]![Chart_selection]![CboLine]) Is Null)) OR (((FtagData_tbl.LINE)=[Forms]![Chart_selection]![CboLine]) AND ((FtagData_tbl.RECORD_DATE) Between [Forms]![Chart_selection]![TxtBDate] And [Forms]![Chart_selection]![TxtEDate]) AND (([Forms]![Chart_selection]![CboType]) Is Null)) OR (((FtagData_tbl.RECORD_DATE) Between [Forms]![Chart_selection]![TxtBDate] And [Forms]![Chart_selection]![TxtEDate]) AND (([Forms]![Chart_selection]![CboLine]) Is Null) AND (([Forms]![Chart_selection]![CboType]) Is Null)) 
ORDER BY FtagData_tbl.RECORD_DATE;

here is the sql for my last query I base on that one:

SELECT DISTINCTROW Ftags_Chart_calcs.Record_Month, Ftags_Chart_calcs.LINE, Ftags_Chart_calcs.TYPE, Sum(Ftags_Chart_calcs.Open) AS [Open Ftags], Sum(Ftags_Chart_calcs.Closed) AS [Closed Ftags]
FROM Ftags_Chart_calcs
GROUP BY Ftags_Chart_calcs.Record_Month, Ftags_Chart_calcs.LINE, Ftags_Chart_calcs.TYPE
HAVING (((Ftags_Chart_calcs.LINE)=[Forms]![Chart_selection]![CboLine]) AND ((Ftags_Chart_calcs.TYPE)=[Forms]![Chart_selection]![CboType])) OR (((Ftags_Chart_calcs.TYPE)=[Forms]![Chart_selection]![CboType]) AND (([Forms]![Chart_selection]![CboLine]) Is Null)) OR (((Ftags_Chart_calcs.LINE)=[Forms]![Chart_selection]![CboLine]) AND (([Forms]![Chart_selection]![CboType]) Is Null)) OR ((([Forms]![Chart_selection]![CboLine]) Is Null) AND (([Forms]![Chart_selection]![CboType]) Is Null));

i don't know what else to do to this thing