Using record number and total coount in a calculation

[Wayne Cramer]

I have an Access query that lists completed company forms by days to complete in descending order. I want to use the reccord number and total record count to present the percentage of forms that were completed at each completion date. Ther purpose is to quickly show that 80 percent of forms are completed within 60 days. This is easy to do in Excel using the Row() and Count() funtions such as 1-Row()/Count($A$1:$A$5000).
Is there a way to do this same thing in Access?

 

[rainman89]

use a dcount...

dcount("*", "tablename" , "completeddate=datecompleted") / dcount("*", "tablename")

 

[Wayne Cramer]

OK. The dcount provides the total records in the query for the top half of the calculation. How do I get Access to display the current record number as a filed? I tried setting up the query as a make table with an autonumber which worked fine the first time but this table must be deleted and appended each month. I thought the autonumber would start at 1 each tiem it was appended which would be ideal - but NOOO. It starts where the last set of data left off leaving me wity flukey percentage calculations. It sure would be nice if I ncould just identify the current record number for use in the calculation. any ideas?

 

[rainman89]

a regular count should work that id think

 

[Dennisk]

If you want an autonumber to start at 1 then
1) Delete all the records
2) Compact/Repair

 

[Banana]

I think if you used AbsolutePosition property, you can get record count, as shown on Access.

Making autonumber meaningful is a bad idea, btw.

 

[boblarson]

Making autonumber meaningful is a bad idea, btw.

Amen to that. If you must do something like that, use a function, or update query, to assign the special count number in a separate field for the table and then use it. Then you can delete the existing data, bring new in, and run the function or update query again to reassign the numbers. But, depending on autonumber to do it for you is bad, very bad.