Results 1 to 6 of 6
  1. #1
    Steven.Allman is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2010
    Posts
    118

    Graphs using difficult table data

    Ok, so please be kind. I wasnt future proofing this when originally built and now it has expanded.
    This is a very generic personnel attendance tracker for one month. It sit on a sharepoint normally, so users can login and report the status of their employees.
    Now, what someone wants is a graph to show who in what division is working in the building on what days.
    So I will not go into great detail, but I have a column for each day of the month 1st, 2nd 3rd.....31st. Everyday, each user is set as one of the many options.


    As a result, i already change the input for many queries and reports daily. You will see the 20th query, which was the first day I ran the program, and today I changed the field to the 8th, tomorrow, the 9th. It works, doesnt take me much time, and I get it done.
    NOW, someone wants to see the following...
    A graph, for the month of how many people are "Present for Duty"
    I tried a few different methods where I groupby DIV, where "present for duty", count DIV.. and of course I would need to make 30 of those queries, but when I tested it with a single query combining them all, I got 50,000 results, which obviously is inaccurate.
    How do I build, and what do I build to use the system in place to show me a
    Daily total, grouped by DIV, Present for Duty....example2.accdb

    Any help would be appreciated.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    See if this gets you started:

    SELECT [PerStat v1].DIVISION, Count(IIf([1st]="present for duty",1,Null)) AS Day1, Count(IIf([2nd]="present for duty",1,Null)) AS Day2, Count(IIf([3rd]="present for duty",1,Null)) AS Day3
    FROM [PerStat v1]
    GROUP BY [PerStat v1].DIVISION;

    Alternative is to normalize data structure and use a CROSSTAB query.

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Steven.Allman is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2010
    Posts
    118
    that works!! i knew there was a method. thanks

  4. #4
    Steven.Allman is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2010
    Posts
    118
    Click image for larger version. 

Name:	data.jpg 
Views:	16 
Size:	77.1 KB 
ID:	42110OK, so now the issue is getting the graph to come up properly.
    I think because my "dates" are column names and not field data, its not showing properly, and I cant figure out what it is looking for.
    I can get a LINE for each day, but the intention is the data show the (Date) across the X axis, with the data result across the Y axis, and a single line per Division. However, thats not at all what I am getting. Ive watched a few youtube videos and they just click three thing and BOOM, perfection.
    But its not cooperating at all with me.

    Irritating because I have a database that will export a PDF report and email it to selected recipients based on the current record, plus do other lookup to include supervisors in CC line... and can figure those out, but a simple graph is kickin my a$$

  5. #5
    Steven.Allman is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2010
    Posts
    118
    Click image for larger version. 

Name:	data2.jpg 
Views:	15 
Size:	58.6 KB 
ID:	42111this is as close as I can get, but I want a line graph and its not coming together.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Issue is this data is not normalized. A UNION query can rearrange fields to normalized structure. Either UNION the raw data and use that dataset for subsequent processing or use query I already provided as source for UNION.

    Example of UNION of raw table:
    Code:
    SELECT [_ID], [G-STAFF], DIVISION, [_Content Type], [1st] AS Data, 1 AS Dy, [_Workflow Instance ID], [_File Type], [_URL Path], [_Path], [_Item Type], [Created By], [Modified By], Modified, Created, [_Encoded Absolute URL] FROM [PerStat v1]
    UNION SELECT [_ID], [G-STAFF], DIVISION, [_Content Type], [2nd], 2, [_Workflow Instance ID], [_File Type], [_URL Path], [_Path], [_Item Type], [Created By], [Modified By], Modified, Created, [_Encoded Absolute URL] FROM [PerStat v1]
    UNION SELECT [_ID], [G-STAFF], DIVISION, [_Content Type], [3rd], 3, [_Workflow Instance ID], [_File Type], [_URL Path], [_Path], [_Item Type], [Created By], [Modified By], Modified, Created, [_Encoded Absolute URL] FROM [PerStat v1]
    UNION SELECT [_ID], [G-STAFF], DIVISION, [_Content Type], [4th], 4, [_Workflow Instance ID], [_File Type], [_URL Path], [_Path], [_Item Type], [Created By], [Modified By], Modified, Created, [_Encoded Absolute URL] FROM [PerStat v1]
    UNION SELECT [_ID], [G-STAFF], DIVISION, [_Content Type], [5th], 5, [_Workflow Instance ID], [_File Type], [_URL Path], [_Path], [_Item Type], [Created By], [Modified By], Modified, Created, [_Encoded Absolute URL] FROM [PerStat v1]
    UNION SELECT [_ID], [G-STAFF], DIVISION, [_Content Type], [6th], 6, [_Workflow Instance ID], [_File Type], [_URL Path], [_Path], [_Item Type], [Created By], [Modified By], Modified, Created, [_Encoded Absolute URL] FROM [PerStat v1]
    UNION SELECT [_ID], [G-STAFF], DIVISION, [_Content Type], [7th], 7, [_Workflow Instance ID], [_File Type], [_URL Path], [_Path], [_Item Type], [Created By], [Modified By], Modified, Created, [_Encoded Absolute URL] FROM [PerStat v1]
    UNION SELECT [_ID], [G-STAFF], DIVISION, [_Content Type], [8th], 8, [_Workflow Instance ID], [_File Type], [_URL Path], [_Path], [_Item Type], [Created By], [Modified By], Modified, Created, [_Encoded Absolute URL] FROM [PerStat v1];

    There is no wizard for UNION, must type or copy/paste in SQLView. Limit of 50 SELECT lines.

    Then use that query in another query which can be RowSource for chart.

    TRANSFORM Count(PerStatUNION.[_ID]) AS Cnt
    SELECT PerStatUNION.Dy
    FROM PerStatUNION
    WHERE (((PerStatUNION.Data)="present for duty"))
    GROUP BY PerStatUNION.Dy
    PIVOT PerStatUNION.DIVISION;

    Problem with Crosstab is there will be Nulls when there is no data for particular dy/div. This causes gaps in line. And days 6 and 7 don't have any "present for duty" for any division so they don't even show in chart at all.

    But, hey, we got days on x-axis and count value on y-axis and line for each division that has data.

    Alternative is to emulate CROSSTAB with expressions like in the first query I provided only now use normalized dataset as source. That would be a calculated field for each division and I count 15 divisions. Here is example with 1 field calculated.

    SELECT PerStatUNION.Dy, Count(IIf([Division]="AVZ" And [Data]="present for duty",1,Null)) AS AVZ
    FROM PerStatUNION
    GROUP BY PerStatUNION.Dy;

    I included "present for duty" criteria in expression instead of a filter in WHERE clause because days 6 and 7 would not show since they don't have any "present for duty" for any division.

    Now every day is on x-axis and every division has an unbroken line - probably with a lot of dips down to 0.

    Is this really better than the column chart?

    FYI, here is UNION using the original DailyCount query as source and this could be chart RowSource:
    SELECT 1 AS Dy, Sum(IIf(Division="AVZ", Day1, 0)) AS AVZ, Sum(IIf(Division="CSO-R", Day1, 0)) AS CSOr, Sum(IIf(Division="CSO-S", Day1, 0)) AS CSOs FROM DailyCount
    UNION SELECT 2, Sum(IIf(Division="AVZ", Day2, 0)), Sum(IIf(Division="CSO-R", Day2, 0)), Sum(IIf(Division="CSO-S", Day2, 0)) FROM DailyCount
    UNION SELECT 3, Sum(IIf(Division="AVZ", Day3, 0)), Sum(IIf(Division="CSO-R", Day3, 0)), Sum(IIf(Division="CSO-S", Day3, 0)) FROM DailyCount;


    Last edited by June7; 06-09-2020 at 03:51 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. How difficult would it be to export data to create a web page?
    By wcook101 in forum Import/Export Data
    Replies: 4
    Last Post: 03-15-2020, 10:15 PM
  2. Replies: 21
    Last Post: 08-19-2018, 02:38 AM
  3. Replies: 8
    Last Post: 04-03-2018, 03:41 PM
  4. Replies: 2
    Last Post: 04-09-2013, 07:42 PM
  5. Ways of preparing data for graphs
    By ajetrumpet in forum Queries
    Replies: 0
    Last Post: 09-11-2010, 10:26 PM

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