Results 1 to 5 of 5
  1. #1
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84

    Chart with trailing zero counts problem

    I have a DB that tracks services provided to customers. I have built into the DB the ability to produce line charts showing the number of services provided over a range of dates selected by the user. As it currently is coded, an sql query returns service name and the dates the service was provided and this is stored in a temp table named tmpALLsvcCTtable that is then used as the source for the chart. The dates are grouped by month. Counts of the service per month are charted in a line format. This code produces reasonably useful charts EXCEPT when there are months where No service was provided. This is because the query doesn't return ALL the months in the range of dates that were requested by the user, but rather, only the dates where the service was provided within that range of dates. The chart doesn't look that great if, for example, the service was provided 2 times in the first month, 3 times in the second month, and then zero times for the rest of the months in the range of dates. In this example, the last data point in the chart is the second month of the range of dates. So trailing months with zero service counts ( and preceding zero service counts) don't show up. I would like to improve the appearance of the charts.

    Here is the code for the query that returns the service names and the dates they were performed into a temp table. The range of dates are entered by the user in [forms]![Print_Client_Reports_Menu]![text2] And [Forms]![Print_Client_Reports_Menu]![text4]. The Indate refers to the date the service was provided and the Svc_name is the name of the service.

    Code:
    strsql = "SELECT tblServices.Svc_Name, Instance.InDate INTO tmpAllSvcCttable FROM tblServices " & _
    "LEFT JOIN (SELECT tblServiceInstance.InDate, tblServicesHistory.Amount, tblServiceInstance.ServiceMonth, tblServicesHistory.Service_ID FROM tblServiceInstance " & _
    "INNER JOIN tblServicesHistory  ON tblServiceInstance.Instance_ID = tblServicesHistory.ID)  AS Instance ON tblServices.Svc_ID = Instance.Service_ID " & _
    "WHERE((Not(tblServicesHistory.Service_ID=5)AND(Instance.InDate) Between [forms]![Print_Client_Reports_Menu]![text2] And [Forms]![Print_Client_Reports_Menu]![text4]));"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strsql
    DoCmd.SetWarnings True
    strsql = ""
    So my problem is to figure out a way to have my query return all the months in the range of dates, not just those when a service was provided. My temp table would then have all the months listed and services listed in the months in which they occurred. There would be some months with no service data. I had 2 thoughts on how to do this, but neither is working.

    My first thought was to create a second temp table that contains all the months in the selected date range followed by preforming a select query using the two temp tables. The problem here is there is no common field to join.

    Here is the code for the query to create a list of all the months in the date range. It returns dates for the first of each month in the [EachMonth] field along with a number ID. for the range selected by the user. I planned to format this later so that it omitted the day of the month and only showed the month and the year.

    Code:
    CurrentDb.Execute "create table tmptempdatetable (dateID integer NOT NULL, EachMonth datetime )", failonerror
    
    startdate = [Forms]![Print_Client_Reports_Menu]![Text2]
    enddate = [Forms]![Print_Client_Reports_Menu]![Text4]
    CurDate = Format(startdate, "mmm 01 yy")
    counter = 1
    
    ' loops through all the potential dates between start and end date and adds a line to the table
    While CurDate <= enddate
        CurrentDb.Execute "insert into tmptempdatetable (dateID, EachMonth) Values ('" & counter & "',  '" & CurDate & "')"
        CurDate = DateAdd("m", 1, CurDate)
        counter = counter + 1
    Wend
    My second thought was to make a union query out of the two queries listed above but the data types aren't the same. I'm stumped. I'm also wondering if there isn't some simple step I am missing that would solve my problem!



    Any help or advice would be greatly appreciated.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    your first method, using a table to list the months, is the way to go, but there may be smarter ways of doing it - for example matching on month name or number

    Not sure why you need the temp table - just use the query as the source to your chart

    would also be a good idea to use meaningful names for your controls - 'text2'?

    and this won't work (or I'll be very surprised if it does) because they need to be surrounded with the # characters and unless you use the US style of dates of mm/dd/yyyy or unambiguous (such as dd mmm yyyy) a date of 6th October (06/10/2021) will be interpreted as 10th June

    Between [forms]![Print_Client_Reports_Menu]![text2] And [Forms]![Print_Client_Reports_Menu]![text4]))

  3. #3
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    Quote Originally Posted by Ajax View Post
    your first method, using a table to list the months, is the way to go,
    But how do I make a join when the two tables (or their underlying queries)don't have a common field?

    Not sure why you need the temp table - just use the query as the source to your chart
    I agree; I think that when I first created this chart several months ago it was easier to keep the steps of the process clear in my mind

    and this won't work (or I'll be very surprised if it does) because they need to be surrounded with the # characters and unless you use the US style of dates of mm/dd/yyyy or unambiguous (such as dd mmm yyyy) a date of 6th October (06/10/2021) will be interpreted as 10th June
    It does work, no problems; perhaps because in the form where the dates are entered they must be entered as dates and there is code to trap entry errors ( or the user can use the datepicker).

  4. #4
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    I've been working with the match idea and have made some progress. The query I created is shown below. If I make the date in the [EACHMONTH] field of the table [tmptempdatetable] match exactly to the [INDATE] field of the table [tmpAllSvcCTtable] I get the output I want. However, I have had no success getting matches with using the Month, Year, or Datepart functions to just look at the month and the date of the [INDATE] field.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	13 
Size:	18.5 KB 
ID:	46403

  5. #5
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    I have made progress in this task but have hit a roadblock that I can't solve. Using a date temp table to create a range of dates and a cross tab query containing an Nz formula, I am able to get the data I want to chart. However, when I attempt to change the chart's rowsource, I get an error that the object can't be updated. Access will create a line chart ( without the zero values I want), but it errors if I try to modify the rowsource that Access automatically generates, specifically when I modify the rowsource with an expression that wraps the value field of the cross tab with "Nz" ( see below in bold print) I even tried using VBA to change the chart rowsource but I have failed. Perhaps this can't be done? I am about to abandon this effort unless I can find a way to modify the rowsource of the Modern chart I have created in a report.

    Here is the code to crate the date table:

    Code:
    CurrentDb.Execute "create table tmptempdatetable (dateID integer NOT NULL, EachMonth datetime  )", failonerror
    
    startdate = [Forms]![Print_Client_Reports_Menu]![Text2]
    enddate = [Forms]![Print_Client_Reports_Menu]![Text4]
    CurDate = startdate
    counter = 1
    
    ' loops through all the potential dates between start and end date and adds a line to the table
    While CurDate <= enddate
        CurrentDb.Execute "INSERT INTO tmptempdatetable (dateID, EachMonth) VALUES ('" & counter & "',  '" & CurDate & "')"
        CurDate = DateAdd("d", 1, CurDate)
        counter = counter + 1
    Wend
    This is query 1 that generates the services provided by month.
    Code:
    SELECT tmptempdatetable.EachMonth, serviceList.Svc_Name
    FROM tmptempdatetable LEFT JOIN (SELECT tblServices.Svc_Name, Instance.InDate FROM tblServices LEFT JOIN (SELECT tblServiceInstance.InDate, tblServicesHistory.Amount, tblServiceInstance.ServiceMonth, tblServicesHistory.Service_ID FROM tblServiceInstance INNER JOIN tblServicesHistory ON tblServiceInstance.Instance_ID = tblServicesHistory.ID)  AS Instance ON tblServices.Svc_ID = Instance.Service_ID WHERE ((Not(tblServicesHistory.Service_ID=5)AND Instance.InDate Between #01/01/2020# And #10/08/2021#)))  AS serviceList ON tmptempdatetable.EachMonth = serviceList.InDate;
    Here is the crosstab sql query that generates the data I want to chart; the bold print is the expression that Access will not allow. Access will not allow the Count([Svc_name]) to be wrapped with Nz. However, If I don't use Nz, I don't get zero values that I need. I also tried an IIF statement but I get the same error.

    Code:
    TRANSFORM Nz(Count([Svc_Name]),0) AS CountOfSvc_Name
    SELECT Format([EachMonth], "mmm 'yy")
    FROM Query1
    GROUP BY Format([EachMonth], "mmm 'yy"), Year([EachMonth])*12 + Month([EachMonth])-1
    ORDER BY Year([EachMonth])*12 + Month([EachMonth])-1
    PIVOT [Svc_Name];
    Here is the result of that query using some sample data.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	9 
Size:	26.2 KB 
ID:	46418

    Why I can't get a line chart using that data, I do not know. Any help would be appreciated.
    Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-11-2018, 03:33 PM
  2. Unique counts problem in a Crosstab query
    By dgmdvm in forum Queries
    Replies: 5
    Last Post: 06-24-2018, 07:20 PM
  3. some problem in showing chart legend
    By afshin in forum Queries
    Replies: 5
    Last Post: 09-16-2012, 10:03 AM
  4. Chart problem
    By jjob in forum Reports
    Replies: 2
    Last Post: 09-10-2011, 05:17 AM
  5. Chart form problem
    By hunsnowboarder in forum Forms
    Replies: 0
    Last Post: 07-08-2011, 06:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums