Coding and Order in Access Query

[Ucpaul]

Hello,



I need to create a column from Access Query that assign Code =1 to the last number of each series (in column Trip_ID). I also need to create a column to order records (please see attached example). I need to do it in Access, not Excel.

I appreciate any help.

Thanks

[June7]

I think what you want is virtually impossible in query with the data shown. There is no way to distinguish records within a group from each other. Is there a unique ID field in table?

[Ucpaul]

There is no unique ID. I want to mark the last number whenever the series changes value. Thanks

[June7]

Records in table have no inherent order. All the records in a series are the same. It's irrelevant to Access which is 'last' or 'first' - Access cannot 'see' last/first record in series because they all look the same, therefore they are all last/first.

Why is this needed? Why are there multiple identical records? What are you really trying to accomplish?

[Ucpaul]

I need to mark when the series changes. I could do it easily in Excel. If TRIP_ID is in column A, then : Cell B1=if(A1<>A2,1,"").

But I need to do it in Access because other connections and cannot explain here. Thanks

[June7]

The Trip_ID itself is an indicator the series changes.

If the ultimate goal is to generate a dataset of unique trip IDs, that can be done by:

SELECT DISTINCT Trip_ID FROM tablename;

or

SELECT TRIP_ID FROM tablename GROUP BY TRIP_ID;

That's about all I can offer without a better understanding of this situation.