Counting Users as 1, 2, 3, and 4?

[CHOO]

Hi,

Is it possible to count the amount of users within a table. For example if a user appears 4 times I want to show it as User1,User2,User3 and User4.

Thanks.

[ItsMe]

Users should be in forms, not tables. You can check to see if a form is open within a given Access file. When a user has a bound form open, they are not continuously "inside a table" or "have a table" open. As they edit, add, navigate, etc. the form updates the table as needed. The following will tell you if a table object is open, not if another object is accessing the table.

Dim intCheck As Integer

intCheck = SysCmd(acSysCmdGetObjectState, acTable, "TableName")

    If intCheck = 0 Then
        MsgBox "The table is not open"
    Else
        MsgBox "The table is open"
    End If

[CHOO]

Thanks for the reply. Bit confused. I have a table with 45000 user IDs. If that user appears more then once I want to show this as User1, User2 etc in another column within the table. I thought some sort of Count query would do this. Thanks.

[ItsMe]

Yah, I was a bit confused too. If you want to count records that have fields that = something, try creating a Totals query and select the "Count", in the totals field to replace "Group By".

As for adding a columns that have User1 User2 etc., perhaps your best bet is to use a Crosstab Query. I do not use crosstab queries but maybe you can get a Totals/Crosstab query hybrid to get the results you are looking for.

[CHOO]

Hi, Thanks for the help. Managed to it with a bit of SQL.