Counting and comparing in a table

[AJ IT Student]

Ok, sorry to post another question - i'm sure you're sick of me.
Anyhow I'm gonna try my luck.

I haven't been able to find something relevant in the help files (that I can understand/adapt!).

I have a table called 'Location' in my database.

This has a list of locations (defined by a primary key) where employees can work. Basically different offices.

Each employee is assigned to an office. Each office has a maximum capacity...

So if you see what I'm getting at, I need some way of searching to find how many Employees (employeeid) there are for each LocationID.

So it might be best to search within the 'Location' table.
I can't work out how to do it (i've been staying up and am very tired - running on red bull ).

Basically I think I gotta Select EmployeeID and count how many records match for each different location. i.e. i may find 21 employees are assigned to LocationID - 2, and the limit is 30.

I'd then somehow sort out a message box if there were over 30 employees assigned to this particular office - saying that it is overpopulated.

Can anyone help me with this?
Many thanks.

aj

 

[Mile-O]

The DCount() aggregate function should do what you want, or you can write a query to count for each location.

 

[AJ IT Student]

Mile -

thanks!

I have read about the function and yes it is the appropriate one.

However I'm an error

My code is:
=DCount("[ManagerID]","Location","[LocationID] = '1'")

it says: #error in the box

ManagerID is the field I want to count. Location is the table name.. and I LocationID is what it needs to count it from.

So basically, in english:
say the Location is London - count the employees working in london.

But I don't want it to be: LocationID=1 . In place of the 1 I'd like the current value of the Location ID on the page! Is this LocationID.Value?

thank you very much
aj

 

[Mile-O]

My code is:
=DCount("[ManagerID]","Location","[LocationID] = '1'")

it says: #error in the box

If your LocationID is a number then you will get an error as you are treating it as text.

=DCount("[ManagerID]","Location","[LocationID] = 1")

But as you want to total dynamically, use this:

=DCount("[ManagerID]","Location","[LocationID] = " & [txtYourTextBox])

 

[AJ IT Student]

ahhhhhhhh!

I'm still getting an error.
I've tried everything.

=DCount("[ManagerID]","Location","[LocationID] = " & [LocationIDtext])

LocationIDtext is my textbox which contains the LocationID.
Its quite strange cos the query I actually want is LocationID = LocationID.

But the first one is a the 'name' of the field but the second one is the 'value'!

I tried LocationID.value but it didn't work..

screenshot: