Validating the table

[XPeriment]

Hey guys,

I have a table of a certain treatment room filling up with patients. The room has a certain capacity which can't be exceeded.


Now I've created a table which will contain the patients ID's and whether they are sitting or lying down (=like 3 sitting patients). I have a field called weight according to sitting/lying - either 1 or 2.
I have a form that the scheduler will assign ID's and the type of patient (sitting/lying) and I want that he won't be able to exceed the capacity - so when the sum of the weights of the records in the table - it wouldn't let the form to add another patient ?
Any ideas?
I thought about autonumber and doing field validation - but it doesn't always resets, and harder with the weights.

What do you think?
thanks
Amir

[orange]

I think you are mixing analysis with design. Get a clear statement of WHAT you are trying to do-in simple English (or native language). Get your tables and relationships identified based on your business rules/facts. Test the model with test data and scenarios (pencil and paper). Adjust the model/data/facts as necessary until scenarios reflect your rules.

Then work out the interface (forms and reports) for implementation in Access.

Major entities:

Patients
treatment room(s)
PatientPosition
RoomCapacity

[XPeriment]

I think you are mixing analysis with design. Get a clear statement of WHAT you are trying to do-in simple English (or native language). Get your tables and relationships identified based on your business rules/facts. Test the model with test data and scenarios (pencil and paper). Adjust the model/data/facts as necessary until scenarios reflect your rules.

Then work out the interface (forms and reports) for implementation in Access.

Major entities:

Patients
treatment room(s)
PatientPosition
RoomCapacity

thanks!
I don't think I'm mixing.
Will try again in simple English:
The user will use a form that will assign patients (rows in the table) into the room (each patient takes different size of the room).
When he will try to assign a new patient that will exceed the capacity I want the form to alert it can't add any more

Amir

[orange]

Please show us your tables and relationships. Forms don't store data --tables store data.
How many Rooms are involved? What is capacity of each? How do you monitor sitting/lying?
How do you determine which room a patient goes to/is assigned to?

each patient takes different size of the room

Number of people per room or something else? cubic feet?

What is the weighting factor for?

Assuming a long line of Patients.

-get the next Patient
-check Rooms for available capacity (review assignment/discharge transactions to get current available capacity)
---assign Patient to Room with Capacity (determine assignment of sitting/lying somehow)
--if not capacity available --Alert supervisor/monitor.

Good luck with your project.

[XPeriment]

Oh jesus, now you've complicated it more than simple English

The table has: ID (the user input), Lying (1/0) - user will tick, Sitting (1/0)- user will tick, and a calculated weight =2*sitting + 1*sitting + 1
the capacity is a known number to me. not in the table or the form. let's say it's 12 (which can be a different combination).
when the sum of the previous weights gets to 12 - the user won't be able to enter a new ID.

I just don't know how to do a limitation of sum of this weight field

[orange]

??
Get a clear description of the problem/opportunity you are trying to solve.
Do some research (Google) on database and normalization.