Trying to come up with Sign In / Group Distribution idea.

Greetings,

I've been working on trying to figure out a solution to my issue and I know it can be done in either access or excel, but I just can't find the resources to make it work.

Here is my issue...

I work for a Not-For-Profit organization that helps people with intellectual and physical disabilities. Because we have no other funding we are paid by the government a small fee for assisting each client and we have to "bill" each client which comes out of a grant that is provided by the government.

I'm extremely new to my position and one of my jobs is to assign "clients" to groups with 1 staff member.

I have a sign in sheet that we sign in all clients and staff. Once all the clients sign in and the staff sign in I have to sort them into groups of 3,4,6,8 (we try to stick to 4 for billing purposes as we make 8 dollars an hour per client so these are the best billing combinations)

I am trying to figure out an easier method then what I do now. Right now I get a piece of paper and just start throwing people together and the process can take 45 mins to an hour and leaves my staff without groups and leaves clients not being as active as they could be.

I was thinking if I could find something where I can enter the clients HIPAA name and their time in and times out and it would show me possible combinations I could group them into. I thought maybe access would be a good area, but I only know how to import data into access and use the automatic features to create reports etc.

I'm just trying to come up with something that will speed up my process, I'm pretty good at some nifty things in Excel, but I just can't get this ideal to come to realization.

Thank you for your reply.

The HIPAA name is the last four letters of the clients last name and first three of their first name.

When it comes to making groups we assign clients to staff that are available. We try to keep them in groups of a certain number so we can maximize billing. We also have days where certain groups go on community integration outings so they have set groups for that day, which is typically a group of four.

When I speak of maximize billing we have a "cheat sheet" we are given and have to go by.

FHI ( This means Facility Habilitation Individual which is 1 on 1 with a staff member) = a ratio of 1:01 = $23.67 hour

FHG: (Facility Habilitation Group which means it can be anywhere between 1 staff with as many as 8 clients, except we try to avoid ratios where we make negative money, like a group of 5 clients with 1 staff)

The FHG cost ratio is

1:02 - $16.96hr (or $8.48 hr per client)
1:03 - $25.44hr (or $8.48 hr per client)
1:04 - $33.92hr (or $8.48 hr per client) - we find this is the safest group ratio we try to keep so clients and staff are safe; however, sometimes we have to create other group sizes due to limitations.
1:05 - $23.60hr (or $4.72 hr per client) - we try our best never to use this group ratio as we make no money for our service and this ratio actually costs us money.
1:06 - $28.32hr (or $4.72 hr per client) - again another ratio that we try to avoid if we can
1:07 - $33.04hr (or $4.72 hr per client) - again another ratio that we try to avoid if we can
1:08 - $37.76hr (or $4.72 hr per client) - again not the best ratio to work with and we hardly every go this route.

If I notice that I have a group of 5 and there are two staff available, for billing, I might split them up into two groups of 2 and 3 so I can maximize billing. This would make the facility $254.40 vs $141.60 if I would have put them in 1 group of 5 for 6 hours.

For example, in my case, I have a day where I have 33 clients set to come in. This will be a perfect world scenario... I have 33 clients coming in and I know I'll need at least 9 staff. I would assign 7 staff with 4 clients, 1 staff with 3 clients, and 1 staff with 2 clients. This would maximize my billing. I would then sit down during the day and write 4 names with one staff, I would write down the times the staff worked.. (8:30 am to 3:00 pm)... I would then write the sign in of each client .. (typically they arrive at 8:30 am and leave at 3:00 pm )... I would then have staff bill for those clients using a specific billing code (we typically only use 2 different billing codes.. one for group and one for individual.. and I only have 5 clients that use about 45 mins of individual time per day )

The above is an easy scenario that I can do quickly, it's the days like below that take me hours and has me working an 8 to 4:30 shift and getting off at 7:30 PM trying to figure out how to make this simpler.

Let's take today for example.

I have a total of 33 clients that are scheduled to come in, we have a total of 7 staff that are going to come in, and we have 2 clients that are absent.

Name	Time In	Time Out	Time In	Time Out	Out
Client A	8:30 AM	3:00 PM			3:00 PM
Client B	8:45 AM	3:00 PM			3:00 PM
Client C	8:30 AM	9:00 AM	2:00 PM	3:00 PM	3:00 PM
Client D	8:30 AM	2:45 PM			2:45 PM
Staff A	8:00 AM	11:00 AM	2:00 PM	3:00 PM	3:00 PM
Staff B	8:15 AM	3:30 PM			3:30 PM
Staff C	8:30 AM	10:00 AM	2:15 PM	3:00 PM	3:00 PM

I know the above looks very confusing but what I do is I have to see when staff is try to schedule clients with staff that are available. So if I have a staff that is signed out for any amount of time and those clients weren't signed out with them, then they can't have clients who are signed in assigned to them.

And if there are times when I have staff signed in and no clients available I assign them NBT (Non-Billable Time) which is time that the company pays out of pocket that cannot be recouped through billing clients.

hopefully that wasn't too confusing, cause this is my world.. I have so many other things I'm doing within my 6.5 hour shift that this billing aspect seems so hard. I tried to sit and learn access because I wanted to associate an excel file I can update daily that I can download so that I can see live totals of the amount of hours left for each client in their quarter, but that was way out of my league.

Thanks again for replying, I know this is one of those extremely hard tasks that can be very confusing to answer.

Post #3 was a bit too much, but does contain info i'm working with.

I have trying to come up with something. I have been taking a paper sheet and signing people in and out.. typically we have clients in the morning that we sign in, some of those clients might sign out with staff (groups of 4) to do community outings and then return and I sign them back in and then they typically only sign out 1 more time when they go home.

I made an excel that I type in the IN and Out times and then I manually count how many people are there in each time block and then I use SUM to tell me how many people in that time frame. I then divide that by 4 and that tells how many staff..

That's my start so far. I'm googling trying to figure out if there is a way to automate that, but as of right now I do it manually.

I tried to post my examples but it would not let me.