Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2013
    Posts
    25

    Unanswered: Pivotchart design questions (fixed categories)

    I need a pivotchart to have fixed categories, and just the fixed categories i specify. Access just supresses the categories in which the value of the Y axis is 0 and i need them displayed. It doesn't have to be foolproof, the user will always add values for the categories (the Y axis is the count of records in which a field has a certain integer which is always between -20 and 20) how do i do? Do you need more information? Thanks in advance!

  2. #2
    Join Date
    Sep 2013
    Posts
    25
    Please if somebody would help me here, i need it for my job!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what query have you tried so far
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Sep 2013
    Posts
    25
    I had literally no clue on how to make it lol. Maybe if you can point me in the general direction i might be able to pull it on my own? In this 15 minutes i've been thinking of making a query, defining a field for each of the 52 different values the x variable can have and a counter for the times the same value was entered in a form (as the y axis) but surely there must be some other way other than defining 52 fields! plus the pivotchart would end up making a different graph for each field so it wouldn't work.

  5. #5
    Join Date
    Sep 2013
    Posts
    25

    Make a query that counts the number of fields with a value (even 0) in a set range?

    I need to make a pivotchart with fixed categories and i think this is the way to make it. I need one query based on a table with these fields:
    Measurement Id (an autonumber)
    Measurement: (an integer which can only go from -26 to 26)
    I need a query that lists:
    measurement Id
    Measurement
    Count of Measurement (if there are 3 measurments with the Id 23 that measure "8", then the query should list 23 8 3 for example).
    So far i can and did make it, but i also need that, even if there are no records with the measurement 10, i need the query to list them with a 0 count (23 10 0 in the previos example) so then i can make a pivotchart and that way the graph will be visually more representative of the issue.
    Thanks in advance! And btw if this is not the way to make a chart with fixed categories (yeah i need the x axis to be of categories, even though it can be made with dispersion), please tell me how it would be!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    not knowing your data its tough to give an answer
    however it is down to the query.

    you need to create a join that returns all the categories and any data for those categories

    what query have you got so far
    what is your table design for the required data
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    one trick is to create a numbers table
    eg
    Numbers
    No Integer (PK)
    insert into NumbersTable -26
    insert into NumbersTable -25
    ....
    insert into NumbersTable 26

    then create a query which returns all vaues in the numbers column and COUNTs the frequency that number appears int he measurement table
    Code:
    SELECT Numbers.No, count(Measurements.Measurement)
    FROM Numbers LEFT JOIN Measurements ON Numbers.[No] = Measurements.Measurement
    group by Numbers.no
    if you also define a foreign key from Measurements to the Numbers table then you will enforce that only valid measurements can be recorded (ie enforce at the table level that a user an only enter values for measurement that already exist in the numbers table.

    an advantage of this is if the business requiremnt changes then by adding new valus in the numbers table it automatically ripples that change through to the measurement table. but also bear in mind that if you choose to reduce the range then you have to make a decision as to whether you use delete cascade to automatically prune what will become invalid data or decide to manually edit out what will become redundant data. FWIW my preference would be to do a manual edit
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Sep 2013
    Posts
    25
    Thank you man, that was really helpful. It works perfectly!

  9. #9
    Join Date
    Sep 2013
    Posts
    25
    Actually i have a little problem, it still excludes the numbers where the count is zero from the x axis, leaving me with a visually useless graph! Do you know what i should do?
    It gets even weirder, 50 tests form up an experiment with an experiment iD, so i added that field so as to filter the measurements by experiment. Then i made some records for verifying. The records with count = 0 return the experiment iD as Null (not 0, null) even though in the table they have an ID = 1. When count is not 0 it works perfectly, but that little thing breaks my graph lol!
    Last edited by JoacoSM; 09-24-13 at 10:30.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    can you post your table design, your query

    the query works fine here
    plonking the query results into a simple chart shows the results Im expecting, although as ever it looks wierd but thats because its using the bog standard chart control

    so its something you are doing at your end

    it could be the graphing tool you are using is 'odd'
    a possible fix would be to expressly set a value of zero if the frequency is null using either an IIF or the NZ function
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •