473,322 Members | 1,806 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

query won't group by month

robin a
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, 375 views)
Aug 16 '12 #1
7 5259
zmbd
5,501 Expert Mod 4TB
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
5,501 Expert Mod 4TB
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
5,501 Expert Mod 4TB
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
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
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
5,501 Expert Mod 4TB
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
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

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

Similar topics

5
by: Julia Baresch | last post by:
Hi everyone, I haven't found any reference to this problem on this group or in Access 97 help. I hope someone here knows this - probably a simple thing, but not obvious. I designed a query to...
3
by: DFS | last post by:
I've been working around this for years (I believe), so I figured someone here might know: Why won't a crosstab query accept a value from a form reference? TRANSFORM...
3
by: Hyphessobricon | last post by:
Hallo, Indeed, a count of a query with a group by function gives more records than there are and so for-next structures don't function. How is this to be mended. Anyone? Everyone in fact....
4
by: question | last post by:
In my database i have a count for each day, but i need to sum the total for each month and the total for the year on one report. THe months must be the columns so i have to use a cross tab query. ...
4
by: British | last post by:
Hey guys need some advice I have this simple string query code that collects data from the "form collection" page below (1). 1.FORM.ASP <html> <head> <title>Form collection</title> ...
5
AdusumalliGopikumar
by: AdusumalliGopikumar | last post by:
can anybody write a query using where,group by ,and having and explain me
3
by: JCCDEVEL | last post by:
Hello, I'm writing a basic query in Sql Server Mgmt Studio 2005. Basically, I'm trying to query a table to get all "issues" reported in a month by "Project Category". The query is working fine...
4
by: Mike MacCana | last post by:
Hi folks, What's the proper way to query the passwd and group database on a Unix host? I'd like to fetch the users in a group (obviously from name services), but my many varied searches can't...
0
by: =?Utf-8?B?Qmx1ZUNoaXBweQ==?= | last post by:
I am trying to find the members of an Active Dir group in code, but unfortunately the name of the group contains "*". I have no problem returning members of any other groups we have. I have...
23
by: colintis | last post by:
I have a query here which works without the GROUP BY clause SELECT A.USERNAME,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.