Results 1 to 6 of 6
  1. #1
    kmw is offline Novice
    Windows XP Access 2007
    Join Date
    May 2012
    Posts
    27

    Query to create Histogram based on user input into text boxes

    I would like to create a query that will count the number of fields, group by employer then group by month.



    The query I currently have seems to just count the number of incidents and group by EMPLOYER. It disregards the start, end point and interval of the partition.


    SELECT Count(TABLE.ID) AS Incidents, Partition([INCIDENT_DATE],[Forms]![FORM_HISTOGRAM]![TextSTART],[Forms]![FORM_HISTOGRAM]![TextEND],1) AS [Date], TABLE.EMPLOYER
    FROM TABLE
    GROUP BY Partition([INCIDENT_DATE],[Forms]![FORM_HISTOGRAM]![TextSTART],[Forms]![FORM_HISTOGRAM]![TextEND],1), TABLE.EMPLOYER;

    Thanks-

    Kristen

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You may want to create a query that pulls the data encompassing the time period of interest first and create a calculated field within that query for the particular period (month-year). Then use that query as the basis for your aggregate query that uses the Partition() function

    SELECT cLng(month(Incident_date) & year(incident date)) as Period, ...
    FROM table..
    WHERE Incident_Date BETWEEN [Forms]![FORM_HISTOGRAM]![TextSTART] AND [Forms]![FORM_HISTOGRAM]![TextEND]

  3. #3
    kmw is offline Novice
    Windows XP Access 2007
    Join Date
    May 2012
    Posts
    27
    Could you explain the two queries a little bit more? What would be in the period field? the month and year?
    Then I would do a second query with the partition?

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    What would be in the period field? the month and year?
    Yes, the Period field would be the month & year. Actually it might be better to switch the month and year in the expression for a more appropriate way to sort (later on). The expression for the calculated Period field: cLng(year(incident date) & format(month(Incident_date),"##")) concatenates the year with the two digit month (formatted with a leading zero for months 1 through 9). This part of the expression yields a string, but you really need a number for sorting purposes, so I used the cLng() function to convert the string to a number. So 3/13/2012 would yield a period of 201203, 12/15/2012: 201212 and so forth

    SELECT cLng(year(incident date) & format(month(Incident_date),"00")) as Period, ...
    FROM table..
    WHERE Incident_Date BETWEEN [Forms]![FORM_HISTOGRAM]![TextSTART] AND [Forms]![FORM_HISTOGRAM]![TextEND]

    The above query pulls the records that meet your date range criteria.

    You would save the above query, then create a new query with the above query as its record source. You would use the partition function in that new query.

    I'm not sure what your data looks like so it is hard to give you more specifics. Would it be possible for you to zip & post a copy of your database with some sample data? Make sure to run the compact & repair utility from the Access toolbar before zipping to compact the file size.

  5. #5
    kmw is offline Novice
    Windows XP Access 2007
    Join Date
    May 2012
    Posts
    27

    attached database

    Infection Control.zip

    The query is called HISTOGRAM

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    This turned out to be a little trickier than I had initially thought. Since the partition function requires you to supply the range start and stop values, you have to use your two textbox controls on the form for that as well as filtering the data. So I created a simple query that does the filtering based on the date range entered on the form. In this query I created two calculated fields StartRange and EndRange. I again set these these up similar to the period function so they look like yyyymm. That query is below:

    query name: qryInfectionDateData

    SELECT INFECTION_CONTROL.EMPLOYER, cLng(year(incident_date) & format(month(Incident_date),"00")) AS Period, cLng(year(Forms!HISTOGRAM!TextSTART) & format(month(Forms!HISTOGRAM!TextSTART),"00")) AS StartRange, cLng(year(Forms!HISTOGRAM!TextEND) & format(month(Forms!HISTOGRAM!TextEND),"00")) AS EndRange
    FROM INFECTION_CONTROL
    WHERE (((INFECTION_CONTROL.INCIDENT_DATE) Between Forms!HISTOGRAM!TextSTART And Forms!HISTOGRAM!TextEND));

    I then created the partition query based on the above query and pushed the start and end range values into the partition function (shown in red)
    query name: qryCountOfInfectionByPeriodEmployer

    SELECT qryInfectionDateData.EMPLOYER, Partition(qryInfectionDateData.Period,startrange,endrange,1) AS Range, Count(qryInfectionDateData.EMPLOYER) AS InfectionCount, MonthName(CLng(Right([range],2))) & " " & left(range,4) AS MonthNameAndYear
    FROM qryInfectionDateData
    GROUP BY qryInfectionDateData.EMPLOYER, qryInfectionDateData.period, qryInfectionDateData.startrange, qryInfectionDateData.endrange, MonthName(CLng(Right([startrange],2)));

    I tied this query to the button on your form. Your database with the new queries is attached.
    Attached Files Attached Files

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

Similar Threads

  1. Replies: 9
    Last Post: 06-04-2014, 10:45 AM
  2. How do you select a field based on user input?
    By technesurge in forum Queries
    Replies: 5
    Last Post: 06-20-2012, 02:04 PM
  3. Replies: 15
    Last Post: 04-01-2011, 11:41 AM
  4. Replies: 2
    Last Post: 08-09-2010, 06:34 AM

Tags for this Thread

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