Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41

    Question Unanswered: 3 fields in access table to make a grid

    Ok, this one is kinda complex to explain but ill give it a shot. There are 3 fields in one of my access tables. I want to create a form that displays this data. Now heres the complex part. Field 1 provides the x-axis, field 2 provides the y-axis and field 3 provides the data.

    Example
    Field 1 Field 2 Field 3
    Record 1 1 1 7
    Record 2 1 2 9
    Record 3 2 1 5
    Record 4 2 2 6

    This would produce a grid that looks like this
    1 2
    1 7 9
    2 5 6

    Any ideas?

    PS. The formatting on here doesnt help, i had it all spaced out nicely
    Last edited by blade1981m; 06-22-07 at 17:58.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blade1981m
    PS. The formatting on here doesnt help, i had it all spaced out nicely
    HTML dude -spaces & tabs don't show. Try using the <code> </code> tags next time (replace < with [ and > with ]).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also your data and results don't make sense to me. Fancy trying again (or someone else who gets it let me know ). For example - what would the field names of the results be?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41
    Code:
    Table1
    
    Field 1 would be called xaxis
    Field 2 would be called yaxis
    Field 3 would be called data
    
    The table in access would look like:-
    
                 xaxis     yaxis    data
    Record1     1          1         7
    Record2     1          2         9
    Record3     2          1         5
    Record4     2          2         6
    
    Based on this table i want to create a form looks like
    
         1     2
    1    7     9
    
    2    5     6

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depending on where you want to use this, and how much data you want then you could do this in a VB function that set values in probably an array, that means you couldn't use the function in a SQL query.

    so a methodology could be
    declare an array in the form to hold the values (eg dim MyArray(<norows>,2) as int

    use the function to read all the results into the array,
    then display those results as required using say a fixed number of rows per page (say 10..20).
    have some buttons or a slider on the form to indicate which records the user wants to see.

    if you intend to allow the user to makes changes then you need to set an isdirty flag so that you make sure you write back the changes. If you do go down that route Id also reccomend that you haev an 'update button' which is enabled when the user makes a change, and at the same time disables the display other records controls (other buttons or slider). you may care to have a changes made message box "do you want to save the changes"

    the isdirty flag is should be cleared after you have updated the data,
    you should check that before unloading if the isdirty flag is set then it indicates that changes are pending.. and say offer the user the option to save changes or discard them

    you would probably need a update fucntion (although to be fair you coudl do it as a monolithic handledata function, with a flag to indicate if the values are being read or set form the array.

    over to you, have a go at it and come back if/when you get stuck
    #
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - makes a bit more sense with the formatting

    Are x and y infinite or limited to upper and lower values?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oops - sniped by a glory hunter - where were you when the question made no sense?

    .. and I thought I was in the SQL Server forum....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    Oops - sniped by a glory hunter - where were you when the question made no sense?

    .. and I thought I was in the SQL Server forum....
    I cant help it if you are too thick to work it out for yourself
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    I cant help it if you are too thick to work it out for yourself
    Quote Originally Posted by blade1981m
    I want to create a form that displays this data.
    Pffff - shows how much you know. OP - two words: Crosstab.

    .... or is that one word? ....ah crap....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    Pffff - shows how much you know. OP - two words: Crosstab.

    .... or is that one word? ....ah crap....
    ...which is fine, providing all you want to do is display the data, if you want to edit the data, or add to it, then someone is going to have to do some graft......
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41
    Ok, i like the sound of this crosstab option, but i am unsure of how to do it. I tried to create a crosstab query using the table, but it puts in a Total option and defaults to group by and if i just leave it at that I get an error that says "You cant specify group by, expression, or where in the total row for this column"

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Something like:

    Code:
    TRANSFORM First(x_and_y.the_data) AS FirstOfthe_data
    SELECT x_and_y.x_axis AS _
    FROM x_and_y
    GROUP BY x_and_y.x_axis
    PIVOT x_and_y.y_axis
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41

    Thumbs up

    Perfect. Thank you so much

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    I cant help it if you are too thick to work it out for yourself
    .
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    .

    smug factor 9 and rising
    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
  •