I want to see only the nost recent record with the same name (1 Viewer)

[Jackske]

Hi,
I have a form based on a table ordered by 'name'.
If the same name appears several times, I would like to change the propriety of all same names (except the last one) to visible=false.
Thus I will only see in my form the most recent record with this name.
Thanks in advance,
Jackske - Belgium

 

[Galaxiom]

How are you defining "last"?

Records in a table have no intrinsic order and what you see when you open it can randomly change from time to time especially after compaction.

BTW: Name and Visible are reserved words and not good names for fields.

 

[Jackske]

Hi Galaxiom,
Thanks
Next the name, I have also date() and time()
Thanks in advance,
Jackske - Belgium

 

[Jackske]

Hi Galaxiom,
Sorry my English is not so good.
I would like to put the propriety visible of all (same) names on false behalve the most recent. Checking date() and time()
Thanks,
Jackske

 

[Galaxiom]

It would be easier if you had the Date and Time combined in one field in the table.

Then you can show the most recent for each name using an Aggregate Query with GroupBy on the Name and Max on the date/time.

However you don't have to actually change to combining the date and time permanently because you could do that in a subquery then apply the aggregate.

Note though that this query can only have the name and date/time included. Any other fields will cause it it show more records. Also this query is not updateable.

If this is not suitable please explain your ultimate goal.

 

[Jackske]

Thanks for your good will,
I did delete the field time and changed the field date to 'general' which includes the time.

I have 5 fields in my table.
An autonumber
2 text fields just for information
The date which fills automatically when I enter a new record.
1 text field with the name of a product.

Every time somebody moves a product to another place, he has to put his name f.e. "Jackske", the name of the product f.e. "book nr.2" and the new emplacement f.e. "Room 5".

Of course there are several people who moves several products.

Let's take for example the "book nr.2".
After 6 months, this book has moved 90 times. Thus when I open the table, I see 90 lines with "book nr.2".

I don't want to delete these lines, because if we don't find the "book nr.2", I want to be able to see the history of the movements of this "book nr.2".

I was searching for a way (f.e. by opening the program) where all the records with "book nr.2" would get invisible (propriety visible has to change to false) EXCEPT the most recent record.

Thus I see where "book nr.2" is now and if I cannot find him on his place, I will give back propriety visible to true; to find out the previous location.

If you give me an email, I sent you my program. It is very small, very useful but I am stuck with this problem.

Thanks in advance,

Jackske-Belgium