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.

by "first" you mean the lowest RegistrationID, right?

usually, "first" is determined by a DATETIME column, because using an autonumber isn't completely reliable

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.

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

---

Thanks but there's a small hitch...

"Enter Parameter value for m.min_id"

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.

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 :)