This topic has been archived. It cannot be replied.
-
工作学习 / IT技术讨论 / 请问ACESS中如何添加一个field, 表示排序号. .谢谢.请问ACESS中如何添加一个field, 表示排序号. table如下, 已经按num group,并按daten排序,现想得到field rank with their order number如下.谢谢.
-------------------------------------------------------
num daten rank
33 2/26/1811 1
33 9/8/1813 2
44 3/4/1812 1
44 5/7/1819 2
-webbook88(ddd);
2004-8-18
{390}
(#1847155@0)
-
如果键入数据是按时间顺序就容易。如果是随机键入就难，因为要全部重新排序。按时间顺序的： me!rank=dcount("rank","tablename","num=" & me!num)
-23456789(大白呼);
2004-8-18
(#1847256@0)
-
谢谢,但不是我想要的结果.rank field are all 2. 我想得到1,2 and 1,2.
num daten rank
33 2/26/1811 1
33 9/8/1813 2
44 3/4/1812 1
44 5/7/1819 2
-webbook88(ddd);
2004-8-18
(#1847982@0)
-
You must use form, not table or query, to calculate the rank. The code above shall be in either BeforeUpdate or AfterUpdate.
-23456789(大白呼);
2004-8-19
(#1848144@0)
-
谢谢,我是初学者,新建了form,put table as recordsource, put the code u suggest to form_beforeupdate(), still not work.Do I need to put num as input, I just display num field in the form? Thank you again!
-webbook88(ddd);
2004-8-19
(#1848513@0)
-
上面的方法仅适用于按时间顺序键入记录的情况，每键入一个记录给一个排序号。如果不是按时间顺序键入，或者已经键入了所有的记录然后全部重新排序就不能用此方法。如果是前一种情况，键入的顺序是num, daten,可把程序安放在daten 的afterupdate event里
Private Sub daten_AfterUpdate()
If IsNull(Me!rank) Then
Me!rank=dcount("rank","tablename","num=" & Me!num)+1
End if
End Sub
在每加一个新记录的daten后，一敲回车，就会自动生成一个排序号。（我已试过，这个码可以，跟前面不同的是要加１，因为当前键入的记录尚未存入）。
-23456789(大白呼);
2004-8-19
{342}
(#1848670@0)
-
Thank you very much.不过已经键入了所有的记录,你是否知道其他方法?Can I use dcount("daten","tablename","num="&num&"AND daten<="daten" in query ?
-webbook88(ddd);
2004-8-19
(#1848870@0)
-
很麻烦啊。在form上 设一个button，在button 的click event里写这段码：
Private Sub <buttonname>_Click()
dim r as recordset
dim strSQL as string
dim n as integer
dim i as integer
strSQL="SELECT * FROM [tablename] ORDER BY num, daten"
set r=currentdb().openrecordset(strSQL,dbopendynaset)
n=0
r.movefirst
DO UNTIL r.EOF
r.edit
If r!num<>n then
n=r!num
i=1
else
i=i+1
end if
r!rank=i
r.update
r.movenext
LOOP
set r=nothing
msgbox "Done."
End Sub
你按一下这个钮就全部排序一遍。试一试。
-23456789(大白呼);
2004-8-19
{507}
(#1848940@0)
-
现在就去试,谢谢!
-webbook88(ddd);
2004-8-19
(#1848979@0)
-
试了,compile error: method or data member not found.debug,发现不认识r.Edit.在网上查了,改了大小写的错误.不知为何不认r.Edit, any idea?Thank.
-webbook88(ddd);
2004-8-19
(#1849183@0)
-
version of your access? it is written in DAO object language. if you are using later version of access, the code shall be either written in ADO language or convert file from old to new. you may email your file to me.
-23456789(大白呼);
2004-8-20
(#1849494@0)
-
Version is MS Access 2000 (9.0.2720), is it OK?But I can not use MS Access help to see help with DAO. It ask me for installation CD to install some feature?Do I miss some feature ??Thanks.
-webbook88(ddd);
2004-8-20
(#1849922@0)
-
A simple easy solution is to export the table to Excel file, using spreadsheet to rank it. You'd better sort the table in proper order before exporting.In Excel, put 1 in the first cell, from 2nd cell , use formula
=if(A1=A2,C1+1,1)
copy the cell to the rest of the column.
Column A is [num], column B is [daten], column C is [rank]. If same num as above, increment rank by 1, else, rank=1.
-23456789(大白呼);
2004-8-20
{247}
(#1850023@0)
-
Thanks,but after I export it in excel,the field of DATEN is empty.And the formular need to change everytime for A1,A2,A3,C1,C2,C3 and so on. Do I need to use macro in excel.Thanks!
-webbook88(ddd);
2004-8-20
(#1850119@0)
-
I don't know why daten is empty. As to the formula, if you copy the cell, it copies the formula and reference. Because it is relative reference, it automatically points to the right places.
-23456789(大白呼);
2004-8-20
(#1850140@0)
-
谢谢 大白呼! It works in Excel and I import it to Acess. It solve the problem right now. I will try to find why I got that compile error.
-webbook88(ddd);
2004-8-23
(#1853182@0)