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.
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.
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