Results 1 to 12 of 12
  1. #1
    svantevid is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    6

    Chart from query


    I built up a database for test results (pressured drop vs air flow) of our product. I also defined a query that filters out the data by part number, test number, materials used in sample etc. Now I would like to make a chart of test results with multiple series defined by the query. I tried putting a graph in a report but I always get a strange graph with months in the x axis. Does anyone know what I'm doing wrong or how to approach this?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I do not play around with the Chart control very much. Did you use the Wizard to create the chart? Have you tried looking at in From View? While in design view of your form, there will be placeholder info that was designed before you purchased Access.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    what does your source data look like and what do you want your final product (graph) to look like. There is not enough information in this post to give you direction.

  4. #4
    svantevid is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    6
    You're right, I should be more specific.
    Lets say that my source data is combined of two tables, first one has different basic information about the test samples and the second one has test values (pressure drop at specific air flow values). I want to create some sort of report that displays test results of specific test sample numbers filtered out from source data based on specific basic information (for example all test samples with the same sample info2).
    My plan was to make a query that filters out data based and then use this data to make a chart of test results of filtered test samples to compare them visually.


    Source data example:
    Test name Sample Info1 Sample Info2 Sample Info3 Sample Info4
    FT01 AB1 AC1 AD3 AE1
    FT02 AB1 AC2 AD4 AE2
    FT03 AB3 AC3 AD4 AE3
    FT04 AB4 AC3 AD4 AE4
    FT05 AB4 AC4 AD7 AE2
    FT06 AB6 AC4 AD8 AE6


    Test name 100 200 300 400 500 600 -> airflow values
    FT01 18 52 94 143 207 283 -> pressure drop values
    FT02 12 32 57 89 127 169
    FT03 14 36 64 99 141 187
    FT04 20 50 91 136 195 258
    FT05 9 21 36 49 69 89
    FT06 9 22 36 50 69 90

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    better, but there is no connection between the sample info data (AB1 - AE6) and your column headings and values in the desired result set, how are you getting the airflow values based on your original dataset and how are you getting the values (pressure drop values).

  6. #6
    svantevid is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    6
    I don't know if I understand your comment. First table is used to filter out the desired test names, for example; I want to see a graph with all test results of test names that include the sample info AC3 an

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I don't care about your graph right now, the basic problem is converting a set of data to something you *can* graph and there is no way for me to understand how you got from your 'source' data to your 'desired' output. Maybe if you supplied a sample database that might be more productive.

  8. #8
    svantevid is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    6
    Actually this is the sample database. Two tables, first one has info for each test name, second one has test results for each test name. There's one entry in the first table for each test name and one entry in the second one for each test name.

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    If you can't supply a sample db I can't really help. Your descriptions are not really helpful, nor have you indicated what you want the final product to be. I would expect a set of sample data and what the final graph should look like based on the sample data, all you've given is two 'tables' that have no visible link to one another other than the test name (which shouldn't be the link it should be primary key field not a text field).

  10. #10
    svantevid is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    6
    ok, now i understand. i attached a sample database and example of a graph

    graph_example.zip
    TEST DB pressure drop.zip

  11. #11
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    ok first a few things about your database

    1. Don't use special characters (# , ; * !, spaces, etc.) in your field or object names, the only exception being an underscore (_). They will cause no end of problems as you progress with your database
    2. If there is a 1 to 1 relationship between tblTestInfo and tblTestResults you don't need to split these tables at all if there will always be a result for a given test.
    3. Your supplier, filter media, media type and filter size should all be options from supporting tables. If these are free-form type in fields a media type of 'pollen' would be different from 'polleen' or 'polen' if the field was mistyped.

    for example:
    Code:
    tblSupplier
    S_ID  S_Name ----> other supplier specific fields
    1     Supplier1
    2     Supplier2
    
    tblFilterMedia
    M_ID  M_Name ---> other media specific fields
    1     Filter Media 1
    2     Filter Media 2
    3     Filter Media 3
    
    tblMediaType ---> other media type specific fields
    MT_ID  MT_Name
    1      Pollen
    2      Carbon
    
    tblFilterSize
    FS_ID  FS_Name ----> other filter size specific fields
    1      Filter Size 1
    2      Filter Size 2
    3      Filter Size 3
    This also would allow you to set up criteria for your graph very, very easily rather than relying on free-form fields.

    Lastly, you are storing one of the two coordinates as a field name which is really not a good practice and is probably causing your issues with graphing. If your test type sets the air pressures and they are standard for a particular test those should be stored in a separate table and the results stored in your actual test table

    i.e.
    Code:
    tblTestType
    TT_ID TT_Name  ---> other standard test specific fields
    1      Standard Test 1
    2      Standard Test 2
    
    tblTestTypePressures
    TTP_ID  TT_ID  TTP_Pressure 
    1       1      100
    2       1      168.78
    3       1      253.17
    4       1      337.56
    5       1      421.95
    6       1      506.33
    Then your testing results would be split into two tables

    Code:
    tblTest
    T_ID  TestName  TT_ID  S_ID  M_ID  MT_ID  FS_ID  
    1     FT0001    1      1     1     1      1
    2     FT0002    1      1     1     2      2
    3     FT0003    1      2     2     1      1
    4     FT0004    1      2     3     2      1
    
    tblTestResults
    TR_ID  T_ID  TTP_ID  TR_Measurement
    1      1     1       8
    2      1     2       18
    3      1     3       32
    4      1     4       47
    5      1     5       64
    6      1     6       83
    7      2     1       8
    8      2     2       18
    9      2     3       31
    10     2     4       46
    11     2     5       62
    12     2     6       80
    etc...
    As it stands with your dataset you would have to artificially create the x axis value to go along with your y axis recorded value. It is possible with your current setup but if this is a 'new' project you might want to consider creating a more valid data structure from the start and make it as flexible as possible.

  12. #12
    svantevid is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    6
    @rpeare

    Your last post has been very helpful, i discovered that I was still looking at Access (or databases on general) through excel eyes.
    I completely reworked my database so now only the real test result data values are freeform fields, everything else is more or less indexed.

    I also discovered that charts in Access are pretty...clumsy, so perhaps a better way would be to export query data and use excel's VBA to make a graph for multiple tests.

    Anyway, thanks a bunch!

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

Similar Threads

  1. Limit chart to date range , Chart isnt updating .
    By fluffyvampirekitten in forum Access
    Replies: 5
    Last Post: 06-30-2015, 12:27 AM
  2. Replies: 4
    Last Post: 04-07-2015, 11:37 AM
  3. Replies: 2
    Last Post: 04-06-2015, 12:08 PM
  4. Replies: 9
    Last Post: 01-29-2013, 06:44 PM
  5. Replies: 2
    Last Post: 07-12-2010, 05:39 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