SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Access Sub Query

    Going around in circles again with Access SQL

    I have a database that lets people register themselves and their friends for a working holiday. There is a registration fee to be paid for each person signing up to join.

    I have an Orders Table and a Registrations table.

    Orders
    OrderID PK
    Date
    Total

    Registrations
    RegistrationID PK
    OrderID FK
    Fullname


    I need to do a report that shows a list of orders by date, but imports the Fullname from the first registration found connected to this order to give a LEAD NAME

    EG: OUTPUT

    Order ID | Date | Lead Name | Total

    This should be fairly simple nested sub-query (right?) but I'm getting tied in knots with Access.

    Any help appreciated.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    by "first" you mean the lowest RegistrationID, right?

    usually, "first" is determined by a DATETIME column, because using an autonumber isn't completely reliable
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes

    By first I did mean the lowest registrationid. Although I have a date of registration, I have no time and pretty much all registrations corresponding to the same order will be placed on the same date. So I preferred to use the lowest registrationid.

    Should be accurate enough for my purposes, if it was written to the database first, then its the lead name - that's how the interface works it.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT o.OrderID
         , o.Date
         , r.Fullname AS LeadName
         , o.Total
      FROM (
           Orders AS o
    INNER
      JOIN ( SELECT OrderID
                  , MIN(RegistrationID) AS first_id
               FROM Registrations
             GROUP
                 BY OrderID ) AS m
        ON m.OrderID = o.OrderID
           )
    INNER
      JOIN Registrations AS r
        ON r.OrderID        = m.OrderID
       AND r.RegistrationID = m.min_id
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks but there's a small hitch...

    "Enter Parameter value for m.min_id"
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  6. #6
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wait - fixed it. m.min_id should be m.first_id.

    Thanks Rudy.

    PS: I brought your book - hope it saves me posting so many dumb questions.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    oh, i'm such a numpty

    inside the subquery i called it first_id, but in the ON clause i called it min_id

    pick one
    r937.com | rudy.ca | 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
  •