By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,318 Members | 2,107 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,318 IT Pros & Developers. It's quick & easy.

query won't group by month

robin a
P: 30
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:

Expand|Select|Wrap|Line Numbers
  1. Month: (Format([RECORD DATE],"mmm/yyyy")) Total: Group By
then
Expand|Select|Wrap|Line Numbers
  1. Open: Sum(IIf([Ftag Data tbl]![DATE COMPLETED] Is Null,1,0)) Total: Expression
then
Expand|Select|Wrap|Line Numbers
  1. Closed: Sum(IIf([Ftag Data tbl]![DATE COMPLETED] Is Not Null,1,0)) total: expression
Then
Expand|Select|Wrap|Line Numbers
  1. 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!
Attached Images
File Type: jpg snapshot.jpg (27.0 KB, 244 views)
Aug 16 '12 #1
Share this Question
Share on Google+
7 Replies


zmbd
Expert Mod 5K+
P: 5,397
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
Aug 19 '12 #2

zmbd
Expert Mod 5K+
P: 5,397
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
Expand|Select|Wrap|Line Numbers
  1. SELECT (Format([RECORDDATE],"mmm/yyyy")) AS MonthandYear,
  2.    Sum(IIf([tbl_FtagData]![DATECOMPLETED] Is Null,1,0))
  3.       AS OpenRecords, 
  4.    Sum(IIf([tbl_FtagData]![DATECOMPLETED] Is Not Null,1,0))
  5.       AS ClosedRecords
  6. FROM tbl_FtagData
  7. GROUP BY (Format([RECORDDATE],"mmm/yyyy")), 
  8.    Month([RECORDDATE])
  9. HAVING (((Month([RECORDDATE])) 
  10.    Between [Forms]![Chart_selection]![TxtBDate] 
  11.       And [Forms]![Chart_selection]![TxtEDate]))
  12. ORDER BY Month([RECORDDATE]);
I then used the following data tbl_FtagData
Expand|Select|Wrap|Line Numbers
  1. PK_AutoNum;     RecordDate;     DateCompleted; 
  2. 1;              1/1/2012;       2/1/2012;
  3. 2;              1/2/2012;       2/2/2012;
  4. 3;              1/3/2012;       (null);
  5. 4;              2/1/2012;       3/1/2012;
  6. 5;              2/2/2012;       (null);
  7. 6;              2/3/2012;       (null);
  8. 7;              3/1/2012;       4/1/2012;
  9. 9;              3/2/2012;       4/2/2012;
  10. 10;             3/3/2012;       4/3/2012;
  11. 11;             4/1/2012;       (null);
  12. 12;             4/2/2012;       5/1/2012;
  13. 13;             4/3/2012;       (null);
  14. 14;             5/1/2012;       6/1/2012;
  15. 15;             5/2/2012;       (null);
  16. 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:
Expand|Select|Wrap|Line Numbers
  1. MonthandYear;   OpenRecords;    ClosedRecords; 
  2. Feb/2012;       2;              1; 
  3. Mar/2012;       0;              3; 
  4. Apr/2012;       2;              1; 
  5. May/2012;       2;              1; 
This appears to do a you have asked it to do...

-z
Aug 19 '12 #3

zmbd
Expert Mod 5K+
P: 5,397
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.    (Format([subqry].[RECORDDATE],"mmm/yyyy")) AS MonthandYear, 
  3.    Sum(IIf([subqry].[DATECOMPLETED] Is Null,1,0)) AS OpenRecords, 
  4.    Sum(IIf([subqry].[DATECOMPLETED] Is Not Null,1,0)) AS ClosedRecords
  5. FROM (SELECT 
  6.       tbl_FtagData.PK_AutoNum, 
  7.       tbl_FtagData.RecordDate, 
  8.       tbl_FtagData.DateCompleted, 
  9.    FROM 
  10.       tbl_FtagData
  11.   WHERE 
  12.         Format([recorddate],"m/yyyy") 
  13.         Between Format([Forms]![Chart_selection]![TxtBDate],"m/yyyy") 
  14.       And 
  15.         Format([Forms]![Chart_selection]![TxtEDate],"m/yyyy"))  AS subqry
  16. GROUP BY Format([RECORDDATE],"mmm/yyyy"), Month([recorddate])
  17. ORDER BY Month([recorddate]);
When ran against the same data and the same start and end dates you get:
Expand|Select|Wrap|Line Numbers
  1. MonthandYear    OpenRecords    ClosedRecords
  2. Jan/2012             1                2
  3. Feb/2012             2                1
  4. Mar/2012             0                3
  5. Apr/2012             2                1
  6. 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
Aug 20 '12 #4

robin a
P: 30
Thank you so much for your help. Here is the SQL from the query:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     Format([RECORD DATE],"mmm/yyyy") 
  3.         AS MonthandYear, 
  4.     Sum(IIf([FtagData_tbl]![DATE COMPLETED] Is Null,1,0)) 
  5.         AS OpenRecords, 
  6.     Sum(IIf([FtagData_tbl]![DATE COMPLETED] Is Not Null,1,0)) 
  7.       AS ClosedRecords
  8. FROM 
  9.     FtagData_tbl
  10. GROUP BY 
  11.     (Format([RECORD DATE],"mmm/yyyy")), 
  12.     Month([RECORD DATE])
  13. HAVING 
  14.     (Month([RECORD DATE]
  15.         Between [Forms]![Chart_selection]![TxtBDate] 
  16.             And
  17.                 [Forms]![Chart_selection]![TxtEDate])
  18. ORDER BY Month
  19.     ([RECORD DATE]);
  20.  
Aug 21 '12 #5

robin a
P: 30
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.
Aug 21 '12 #6

zmbd
Expert Mod 5K+
P: 5,397
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
Aug 21 '12 #7

robin a
P: 30
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:
Expand|Select|Wrap|Line Numbers
  1. 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 
  2. FROM FtagData_tbl 
  3. GROUP BY (Format([RECORD_DATE],"mm/yyyy")), FtagData_tbl.LINE, FtagData_tbl.TYPE, FtagData_tbl.RECORD_DATE 
  4. 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)) 
  5. ORDER BY FtagData_tbl.RECORD_DATE; 
here is the sql for my last query I base on that one:
Expand|Select|Wrap|Line Numbers
  1. 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]
  2. FROM Ftags_Chart_calcs
  3. GROUP BY Ftags_Chart_calcs.Record_Month, Ftags_Chart_calcs.LINE, Ftags_Chart_calcs.TYPE
  4. 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));
  5.  
i don't know what else to do to this thing
Aug 22 '12 #8

Post your reply

Sign in to post your reply or Sign up for a free account.