Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2005
    Posts
    5

    Unanswered: simple access problem that will give me some extra credit

    Hello I don't know if any one will know this one but, I need to turn this data



    ALso lets say that I have a field called player ...
    there are Feature1, 2, 3, 4 fields

    by default the table is set up like this ...
    Player feature1 feature2 feature3 feature4
    p1 data1 data2 data3 data4
    p2 data4 data5 data6
    p3 data7 data8
    p4

    in to someting like this

    player feature
    p1 data1
    p1 data2
    p1 data3
    p1 data4
    p2 data4
    p2 data5
    p2 data6
    p3 data7
    p3 data8
    No p4



    if any one can help it would be great>>> I would go from this

    to this

    justin

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Justin

    The ideal would be to design the table correctly in the first place. Are you in a position where you can change your table structure to the second example you posted? The current structure is not good (as evidenced by the fact you are trying to get away from it).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You might be able to do that with a cross tab if you use player and feature as row headings, I've never tried it though...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Teddy
    I've never tried it though...
    Probably a good reason for that eh Ted...
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    Hi Justin

    The ideal would be to design the table correctly in the first place. Are you in a position where you can change your table structure to the second example you posted? The current structure is not good (as evidenced by the fact you are trying to get away from it).
    Dan ... And we've both seen posts where they want to go to the top example from the bottom example ... Did I not post on that once?

    Justcraz:

    This can be done with some VBA code AND assuming that you have a fixed number of columns ... Just read each record and write out the desired # of rows.
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Not sure why we're helping with homework, but a fairly simple union query will produce the desired result.
    Paul

  7. #7
    Join Date
    Nov 2005
    Posts
    5

    Thanks

    Sweet, thoes sound like some great ideas, and unless i run out of time I will be able to figure it out real good.


    I am new to the access ring as you can tell and have been craming this stuff down for a while.

    The only problem is I have to study forms real quick at the same time because I have a quiz



    thanks again

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Be careful what you study "real quick".
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by M Owen
    Dan ... And we've both seen posts where they want to go to the top example from the bottom example ... Did I not post on that once?
    Indeed you did Mike - and yours was an example of taking a normalised table (or at least the little we saw of it it looked like one) and manipulating the output for a very specific presentation requirement. This is the opposite - taking data stored in what appears to be a presentation requirement and normalising the output.

    Also - I'm quite happy respond to homework questions - it is the nature of the repsonse that requires judgement. None of the responses can be accused of doing any more than offering some ideas that may turn out to be fruitful. I think that is appropriate especially as this guy hasn't done what some do (copy and paste assignment and demand someone does it for them).

    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Woaah - how did I find myself back here? Recursive linking
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by pootle flump
    Woaah - how did I find myself back here? Recursive linking
    What can I say, I'm sneaky...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    Indeed you did Mike - and yours was an example of taking a normalised table (or at least the little we saw of it it looked like one) and manipulating the output for a very specific presentation requirement. This is the opposite - taking data stored in what appears to be a presentation requirement and normalising the output.

    Also - I'm quite happy respond to homework questions - it is the nature of the repsonse that requires judgement. None of the responses can be accused of doing any more than offering some ideas that may turn out to be fruitful. I think that is appropriate especially as this guy hasn't done what some do (copy and paste assignment and demand someone does it for them).

    Yes. It was normalized ... And as for homework/class projects, I sometimes MIGHT give a hint or 2 (like in this case) ... This particular problem is UGLY no matter how you do it (case in point: Ted's previous post). My point on this post was that using VBA AND knowing the # of columns involved you can make this a bit less ugly ...
    Back to Access ... ADO is not the way to go for speed ...

  14. #14
    Join Date
    Nov 2005
    Posts
    5

    answer to the question

    I don't konw if posting the answer is against any debate about the ethics of this type of help ... but in my case the teacher mentioned that this question was a internet search type of question. ANy way that you could get the problem doen minus the whole one guy in class finds it and then tells the others . but any way

    here it is
    the as thing changes the feature1 field to the feature title
    SELECT DVD.Player, DVD.Feature1 as feature
    FROM DVD;

    UNION
    SELECT DVD.Player, DVD.Feature2 as feature
    FROM DVD;

    UNION
    SELECT DVD.Player, DVD.Feature3 as feature
    FROM DVD;

    UNION
    SELECT DVD.Player, DVD.Feature4 as feature
    FROM DVD;


    it was a little harder trying to figure it out on my own I got as fast as finding the help examples of the union in the help file.... but getting it work ... I ran into a fight with the little lady and a time issue ... but I was this close

    oh well.....at least I learned something

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Thumbs up

    Quote Originally Posted by Justcraz
    oh well.....at least I learned something
    Justin - it is terribly refreshing to have a student post, get some guidence, go off and figure out the solution from that. As you say - you learned something - a lot of your peers are not, I am afraid, doing even that. Good job.

    BTW - although the solution solves your immediate problem I still hold that the problem itself arises from what is (probably - there could of course be extenuating circumstances) a poor table design.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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