Query Problem

[Guest]

I'm trying to update a date field in a table and want the date in descending
order when the update is made since the ssn can have multiple dates. I'm
trying to get the most recent date. Is this possible. I can't figure out
the syntax.

update Table1 inner join table1.ssn on table2.ssn set table1.date=table2.date
order by table1.date desc;

 

[Guest]

Hi Mike

Top tip - Don't.

The order the data is stored in a table will make no difference to the user.
If you want to sort a field do so in a query or on a form.

--
Wayne
Manchester, England.
Enjoy whatever it is you do
Scusate,ma il mio Inglese fa schiffo
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.

 

[Guest]

I'm using a query? I want the sort to happen when the query updates the
table.

 

[Guest]

Hi Mike thats not really the way to do it.

I assume that you want to sort the records each time the query is run/ used ?

If so open the query and from the Records tab select Advance Sort. Set the
sortting order that you need (in the case date) and then save. Each time the
query is run your sortting will take place (on a new query). You can change
it any time by just altering the advanced sort.

Note that you are NOT sorting your query as the advance sorting will create
a new query and it is this will sort and which you use as the basis for
viewing/forms/reports etc.


Hope this helps

--
Wayne
Manchester, England.
Enjoy whatever it is you do
Scusate,ma il mio Inglese fa schiffo
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.

 

[Guest]

Hi Wayne,

That's a good idea but it won't work on update queries.

Mike

 

[John Vinson]

I'm trying to update a date field in a table and want the date in descending
order when the update is made since the ssn can have multiple dates. I'm
trying to get the most recent date. Is this possible. I can't figure out
the syntax.

If you want the Table to end up sorted by date order... GIVE UP.

Tables *HAVE NO ORDER*. It's like talking about the first and second
potatoes in a sack of potatoes.

If you want to see the data in this table in date order, use a Select
Query sorting in that order, and use the Query as the source of a form
or report to view the data.

If you already have such a form, and change the value of a sorting
field, simply requery the Form.

John W. Vinson[MVP]