SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Need Help on Query

    Hello all,

    I have a question about Access 2007, hope you can help me out. Here it is:

    The "Customers" table:
    Code:
    ID Name Parent 
    1    A
    2    B
    3    C     5
    4    D     1
    5    E
    Now I want to make a recordset look like this:

    Code:
    ID Name Parent 
    1    A
    4    D     1
    2    B
    5    E 
    3    C     5
    How can I do that in Access 2007?

    Thanks in advance.

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,031
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    What is the logic for the new resultset? Why did you order the records that way?

    Normally to indicate in which order the results should be returned you use ORDER BY in your query, but I don't see the logic of the order in your preferred results.

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The logic is: order the child right below it's parent.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    ORDER BY COALESCE(parent,id),id

    this works only if you're using NULL in the parent column to represent no parent

    oh, and you might need to use the Access NV function instead of COALESCE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    ORDER BY COALESCE(parent,id),id

    this works only if you're using NULL in the parent column to represent no parent

    oh, and you might need to use the Access NV function instead of COALESCE
    I got the error "Undefined function 'COALESCE' in expression".

    What is Access NV function?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by notooth View Post
    What is Access NV function?
    omg, typo

    i meant to say NZ function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    omg, typo

    i meant to say NZ function
    That works, but I don't understand the expression. Can you explain it?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i think da manual would do a better job of it

    try this -- http://office.microsoft.com/en-ca/ac...288901033.aspx
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you help me with another logic: order by name and it's child right below?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by notooth View Post
    Can you help me with another logic: order by name and it's child right below?
    yes, if you show me the exact query you're running
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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