First and Last rows

[ryetee]

Can I determine what the 1st and last entries are with a key from a different table

For example I have a job parts table (ID= JOBPARTID). There is a specific part that may be replaced several times. I keep these parts on a separate table called swaps. The swaps table is purely
SWAPID (KEY)
JOBPARTID
PART
PARTDESCRIPTION

SO on file I'll have

SWAPID JOBPARTID PART PARTDESCRIPTION
001 1234 PART1 DESC1
002 4231 PART2 DESC2
003 1234 PART3 DESC3
004 1229 PART4 DESC4
005 3241 PART5 DESC5
006 1230 PART6 DESC6
007 1234 PART7 DESC7
008 3289 PART8 DESC8

For JOBPARTID I want to be able to get the first record (SWAPID =001 for PART1) and the last one (SWAPID = 007 for PART 7).

How do i go about it?
Thanks in advance.

 

[jdraw]

A little confused with your post -not following your example-- what exactly is a JobPart vs a Part?
A specific Part that can be replaced several times???
My first thought was interchangeable Parts or equivalent Parts, but that does not seem to be the case after re reading the post.

 

[namliam]

How about using min and max in a (sub) select?

Select Jobpartid
     , min(swapid) FirstSwap
     , max(swapid) LastSwap 
from yourtable
Group by Jobpartid

 

[ryetee]

A little confused with your post -not following your example-- what exactly is a JobPart vs a Part?
A specific Part that can be replaced several times???
My first thought was interchangeable Parts or equivalent Parts, but that does not seem to be the case after re reading the post.

I'm very good at confusing people especially when I'm confused in the first place!

Right the Job parts table contains the parts that have been a replaced on a particular job. After testing, a particular part may have to be replaced again so the swaps table keeps a track of of any part that has been replaced more than once. What I failed to state is that the swaps table contains the part serial number as well which uniquely identifies any part.

So yes a part can be replaced several times. For reporting I need to know the original part (the first one) and the one that is used in the end (last one). I should have mentioned the serial number to point out that the part is the same part(!!) but the serial number makes it unique.

Does that clarify things or muddy the waters?

 

[ryetee]

How about using min and max in a (sub) select?

Select Jobpartid
     , min(swapid) FirstSwap
     , max(swapid) LastSwap 
from yourtable
Group by Jobpartid

Thanks, I'll give it a go. I'm going to be using a query to do this I presume it's quite easy to do that sort of thing in query design mode.