sortedsites — 2010-02-02T08:31:26-05:00 — #1
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.
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
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 — 2010-02-02T09:27:57-05:00 — #2
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 — 2010-02-02T09:35:55-05:00 — #3
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 — 2010-02-02T10:05:01-05:00 — #4
, r.Fullname AS LeadName
Orders AS o
JOIN ( SELECT OrderID
, MIN(RegistrationID) AS first_id
BY OrderID ) AS m
ON m.OrderID = o.OrderID
JOIN Registrations AS r
ON r.OrderID = m.OrderID
AND r.RegistrationID = m.min_id
sortedsites — 2010-02-02T10:11:42-05:00 — #5
Thanks but there's a small hitch...
"Enter Parameter value for m.min_id"
sortedsites — 2010-02-02T10:19:09-05:00 — #6
Wait - fixed it. m.min_id should be m.first_id.
PS: I brought your book - hope it saves me posting so many dumb questions.
r937 — 2010-02-02T10:21:52-05:00 — #7
oh, i'm such a numpty
inside the subquery i called it first_id, but in the ON clause i called it min_id