Multiple positions at two locations

[tstowe]

I have five positions with the same title and other information and the positions are split into two at Location1 and three at Location2. What I am needing the dbase to do is:

First: Understand that there are only Five positions (e.g. Five Clerks)

Second: How can the dbase know when there are no more positions left to fill?


One method that I have tried is to create all five positions and then issue them respectfully; however this causes my dbase to be enormous as I continue to create all of the available positions.

Thanks ahead of time.

Tony

 

[randy700]

How about something like this in the table....

Field = Title DataType = Text
Field = Location DataType = Number
Field = Available DataType = Number
Field = Filled DataType = Number

Sample data...
Title Location Available Filled
Manager 1 1 1
Manager 2 1 1
AsstMgr 1 1 0
AsstMgr 2 1 1
Clerk 1 10 8
Clerk 2 8 7
Cashier 1 6 6
Cashier 2 10 7

Here, a comparison between available and filled would indicate if any positions remain open.

Randy

 

[tstowe]

Thanks for the suggestions, however I am still doing the tracking and not the dbase - if I am understanding you.

To elaborate, I have the following to track

Senior Tm Ldr 1 position
Tm Ldr 3 positions
Asst Tm Ldr 6 positions
Laborer A 15 positions
Laborer B 6 positions

From the Above one complete team is represented by:

1x Tm Ldr, 2x Asst Tm Ldr, 5x Laborer A and 2x Laborer B

I need to track all 31 positions, while allocating the teams into the two locations "A" and "B". TBL_Position lists each of the above positions only once regardless of the total number of positions (which may be incorrect for what I am trying to do). In TBL_Location I will input the persons name and their position (Tm Ldr, Laborer A, etc). What I am wanting to have happen is for Access to double check me on the number of available positions.

As a poor example, Access will allow you to Index, No Duplicates. I would like to do the same but in terms of me saying, "dbase, there a three Tm Ldr positions tell me when I have used them all and don't allow any further allocation of this position, but do allow me to transfer the position from one location to another."

Thanks.

Tony

 

[EBGreen]

I don't think you can do this in Access. I think you would need to use stored procedures which access does not support AFAIK. You could use macros maybe, but that still isn't really in the DB.

 

[tstowe]

That is disappointing news; however, what I have decided to do for the moment is to add another field to my table effectively named positions. With each Position listed I now have the quantity available for assignment.

I am first going to try to use a query to see if that will at least pop up a message (any suggestions from the masses on how to do this?) saying no more slots are available.

Another issue that I will have to tackle is having a message appear indicating that I am in excess of the number of available positions (which happens more than I care to).

Thanks,

Tony