Access Sub Query

[SortedSites]

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.

[r937]

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

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

[SortedSites]

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.

[r937]

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

[SortedSites]

Thanks but there's a small hitch...

"Enter Parameter value for m.min_id"

[SortedSites]

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.

[r937]

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