Counting Items (VBA 2000)

Topic

New Reply

I have an access Database table that has multiple status’s that can exist for a client. i.e. A01 = Current, A02 = Recert, P00 = Active and so on. I need to be able to grab the last status to pull the information but I have not been able to figure it out. The client can at first be current then changede to active then to current again. I want to pull the last status and use it to filter.

If [status] = A01 then [UserID]

But I keep pulling the first status line only. I need the last line.

Any suggestions??

Dan

Reply | Quote

Replies

How do you know the sequence? Can your query be modified to ORDER BY that field (e.g., DESCending)?

Reply | Quote

Unfortunately no. A patient can be discharged then be re-admited then sent for evaluations all in the same day.

Dan

Reply | Quote

is there some kind of timestamp (DateTime field) you can order by? What fields do you have available? I think going by the physical order in the file might be an option, but I wouldn’t know how to do it. Maybe I should slide this on over to Access…

Reply | Quote

is there some kind of timestamp (DateTime field) you can order by? What fields do you have available? I think going by the physical order in the file might be an option, but I wouldn’t know how to do it. Maybe I should slide this on over to Access…

Reply | Quote

Unfortunately no. A patient can be discharged then be re-admited then sent for evaluations all in the same day.

Dan

Reply | Quote

How do you know the sequence? Can your query be modified to ORDER BY that field (e.g., DESCending)?

Reply | Quote

You would be better to ask this question on the Access Forum but I would be looking at the DMax function.

Reply | Quote

A clumsy way that may work would be, in summary:
1. dim a recordset which retrieves the status’s for the client
2. Count the number of records using recordset.count
3. Move to the first record
4. Move.Next the number of records returned
5. Voila, grab the field you require and place it into the query

Reply | Quote

A clumsy way that may work would be, in summary:
1. dim a recordset which retrieves the status’s for the client
2. Count the number of records using recordset.count
3. Move to the first record
4. Move.Next the number of records returned
5. Voila, grab the field you require and place it into the query

Reply | Quote

As Andrew pointed out, you would have been better advised to post this in the Access forum, since Access VBA is somewhat different from that in the other Office apps and it’s nearly impossible to build a decent Access database without using VBA code. .

When you mention multiple statuses and “Lines”, it makes me wonder what exactly you’re describing. Do you mean that there are multiple records in the table for a client, each with a different status, and some with possible repeating status values (I.e., more than one current status record for the same client)? How can you determine the “last” status? Do you have a date stamp in the record? I hope you aren’t talking about multiple status indications in the same record, which is what the reference to “lines” makes me suspect. Give us more information on the table structure.

Reply | Quote

As Andrew pointed out, you would have been better advised to post this in the Access forum, since Access VBA is somewhat different from that in the other Office apps and it’s nearly impossible to build a decent Access database without using VBA code. .

When you mention multiple statuses and “Lines”, it makes me wonder what exactly you’re describing. Do you mean that there are multiple records in the table for a client, each with a different status, and some with possible repeating status values (I.e., more than one current status record for the same client)? How can you determine the “last” status? Do you have a date stamp in the record? I hope you aren’t talking about multiple status indications in the same record, which is what the reference to “lines” makes me suspect. Give us more information on the table structure.

Reply | Quote