How to Get the first Detail entered and the last one
-
Hi!
I have a master and detail tables what I want to extract from the detail table is the first row entered for a particular master and the last row entered.
Basically it would give me the First KeyCode entered for a building and the last Key Code. I would show that info into a grid so they could search on the keycodes. (Everytime they change a lock for a building they enter the new KeyCode needed for the new lock)
Thanks Joel
Here an example of my structure:
Master
BuildingID
Description
Etc..
Detail
BuildingID
DetailID (auto increment)
KeyCode
The select bellow give me the right rows but doesn't give me the KeyCode.
SELECT BUKey.[BuildingID]
, min(BUKeyDetail.KeyDetailID),
,Max(BUKeyDetail.KeyDetailID)
FROM [BUKey]inner join [BUKeyDetail] on BUKey.Buildingid=BUKeyDetail.Buildingid
Group BY BUKey.[BuildingID]
-
The SQL you provided results in the master and detail tables keys, so just join to the master and detail tables (with the detail table appropriately aliased to "first" and "last")
select <column names>
FROM BUKey
join (SELECT BUKeyDetail.BuildingID
, min(BUKeyDetail.KeyDetailID),
, Max(BUKeyDetail.KeyDetailID)
FROM BUKeyDetail
Group BY BUKey.BuildingID
 
as BUKeyDetail_FL -- First and Last( BuildingID, KeyDetailID_First, KeyDetailID_Last)
on BUKeyDetail_FL.Buildingid = BUKey.Buildingid
join BUKeyDetail as BUKeyDetail_First
on BUKeyDetail_First.KeyDetailID = BUKeyDetail_FL.KeyDetailID_First
join BUKeyDetail as BUKeyDetail_Last
on BUKeyDetail_Last.KeyDetailID = BUKeyDetail_FL.KeyDetailID_Last
SQL = Scarcely Qualifies as a Language
-
Thank you very much.
it's working fine.
Joel
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply