using the results from a count query

[katiechoy]

Hello everyone

Im new to this so im just going to explain my problem.

I have created a query that counts the number of times a supervisors
name appears in the client table. (Each client is assigned a
supervisor)

I then need to use that number generated to decrease the value in a
form. (Because a supervisor can only supervise a certain number
clients; this number can vary depending upon the amount of time each
supervisor has).

If anyone has any ideas of how i could do this, i would be very
grateful!

Thanks in advance

Katie Choy

[pietlinden]

If your goal is merely to ensure that a single supervisor cannot have
any more than some fixed number of clients, you could just add code to
the BeforeInsert event of your (sub)form, and had it do a DCount of
records where the supervisor ID is the same as the supervisor ID in the
main form. If that's the case, then show a message (MsgBox) and reject
the insert (Cancel = True).

[katie]

Thanks for replying so quickly.

I don't think i phrased the question very well.

I have a Client table with a field called "Supervisor" where i enter a
supvisor number.
I then have a Supervisor table where there is a field called "Number
to clients that can be monitored"

Every time a new client is entered into the table and a supervisor is
assigned, i need to decrement the value in the supervisor table.

I then need to create a report that shows the number od clients each
Supervisor has and the number clients that they can still take on.

Thanks again for the other reply, i feel it will be useful to prevent
allocating supervisors that are full.

K

[pietlinden]

> I don't think i phrased the question very well.

I have a Client table with a field called "Supervisor" where i enter a supvisor number.
I then have a Supervisor table where there is a field called "Number
to clients that can be monitored" Every time a new client is entered into the table and a supervisor is
assigned, i need to decrement the value in the supervisor table.

No you don't. You just put a maximum value of clients that this
supervisor *can* monitor in the supervisor table. then if you do a
DCOUNT() on the Clients table with that supervisor's ID as the
argument, you'll get the number of clients that supervisor is *already*
supervising. And then that's where the BeforeInsert comes in. Okay,
so you don't have a Main/Sub going on. You can do it all from the
Clients form. Here's the code I used to do it....
Private Sub SuperID_BeforeUpdate(Cancel As Integer)
If DCount("[SuperID]", "tblClient", "[SuperID] =" & Me.SuperID) =
DLookup("[MaxClients]", "tblSupervisor", "[SuperID] =" & Me.SuperID)
Then
MsgBox "This supervisor is already at max." & vbCrLf & "Please
assign to another supervisor."
Cancel = True
End If
End Sub

the DCOUNT gets the number of clients the given supervisor is already
supervising.
the DLOOKUP get the MAX number of clients the given supervisor can
supervise.
If the two numbers are already equal, the Supervisor is already working
at capacity and cannot have any more cases added. So I reject the
update to the chosen value (the supervisor(ID)) and prompt the user to
assign another supervisor. No need for a main/subform.

For the report, you can either do a summary for each supervisor and
show just the counts (MaxCaseload vs. CurrentCaseload) where
CurrentCaseload would just be the number of child records (Cases) the
supervisor has. Remaining slots or whatever is just Max-Current.

[pietlinden]

Oh, forgot one thing. If you want to show the supervisor's current
caseload on the form, you can just add an unbound control (just drop a
textbox on the form) and then set it's control source to
DCount("[SuperID]", "tblClient", "[SuperID] =" & Me.SuperID)

where "SuperID" is the SupervisorID field on your open form. One thing
you should be aware of - running update/insert queries will bypass this
check. (One place where Oracle is MUCH better than Access - it has
real table-level constraints and checks, whereas Access really has
little more than integrity checking).

[katiechoy]

Thanks for that, works brilliantly!

K