How to change the fields value as per new sort?? 2

[Fekri]

Hi,

I have a table with two fields [Doc No] & [date]
and the table name is master

I need to made the query or some command or code to sort the [date] on ascending then delete all the value on [doc no] and replace the records with the number which start from 1 to ...

is there any way to do??

 

[PHV]

You may try this update query:
UPDATE master SET [Doc No] = DCount("*", "master", "[date]<=#" & Format(master.Date,"yyyy-mm-dd") & "#")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

The final answer is here: thread702-1079064
UPDATE master SET [Doc No] = DCount("*", "master", "[date]<#" & Format([Date],"yyyy-mm-dd") & "# OR ([date]=#" & Format([Date],"yyyy-mm-dd") & "# AND ID<=" & [ID] & ")")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Fekri]

That is perfect and working nice but

I have to explain a little more to ask more help...

I have 2 tables "master" & "master lineitem"
these 2 tables have relationship with "doc no" (One-to-many and have cascade updating and deleting)

I thought that I can append all the record from master to temporary table and change the "doc no" with your code posted and again return it to the master table but it's necessary to delete all the record before append them.
and delete the record on master table will delete all the record on the "master lineitem".
So, again it's stoped me.

Do you have any idea about it;

How to make change "doc no" in "master" table and also in related record in "master lineitem"????

Thanks
Ali

 

[PHV]

Why the ForeignKey in "master lineitem" is not the ID of "master" (instead of "Doc No") ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Fekri]

OOps, Perfect Idea

You have gold mind

thanks
Ali

 

[lespaul]

That's a standard normalization rule, have you read The Fundamentals of Relational Database Design?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[Fekri]

Thanks Leslie

It was nice reference document and usefull for me.

I send one star for your usefull reference

Fekri