Trying to pull first record from groups that are in a field.

[DDP123]

I am somewhat new and am having trouble pulling the data I want. I have multiple records where I am wanting to pull the first record from each group. If I group together the fields than I will group my field on left together where I will show the earliest date for each code and I dont want to do that. I just want the first record from the group. Example of data below.

Code Date PART CD


411 20001031 blank
411 20010101 S09
412 20010201 C12
412 20010701 S14
412 20010801 S09
412 20030701 C65
412 20040501 R20
411 20040510 C06
411 20040516 C65
411 20041110 C05
412 20050216 A10
412 20050301 Blank
412 20050816 C05
412 20061101 C64

Basically what I am trying to accomplish from the data above is below. Is this even possible? I have tried everything from group to putting fields together and I cant get what I want.

Code Date PART ID
411 20001031 blank
412 20010201 C12
411 20040510 C06
412 20050216 A10

Thanks for your help!

[CJ_London]

in databases there is no such thing as first, last, next or previous without an order of some sort. When new records are added, they do not go 'at the end' or 'last'.

[June7]

Records in a table do not have a 'natural' order. Sort criteria determines order. This means what is the 'first' or 'last' record is dynamic. What rule determines 'first' record for you - the earliest date? Here is one method http://allenbrowne.com/subquery-01.html#TopN

[John_G]

MS Access does not recognize "first" in the same way we do. For Access to know what you mean by "first" you have to tell it what the order of the records is, i.e. how they are sorted. It has no concept of "record number" (unless that is actually in the data).

The example list you have shown is ordered by date, but if you were to put that in an Access table with no indexes defined, it might show you the records in the order they were entered, but there is no guarantee of that.

So, without more data in the table, Access cannot do what you want it to in this case.

[DDP123]

Thanks for the reply. Would this be possible in SSRS?

[June7]

It should. Assuming SSRS reporting can be based on queries.