Need some ideas (1 Viewer)

[earlsinclair]

I need some ideas on this new database that I am creating. I'm creating a database that will store employees clock in and clock out times on a weekly basis. The main table has the following fields: ID, employee name, date, clock in time, clock out time. The employee name field source is another table that has the list of all the employees names.

I need to create a form that will allow the user to easily input the clock in an out times during a week for each employee. The user will pick the employee name from a combo box. Then, on this form, I want it to have all the days of the week (sunday thru saturday) in a column format and in front of each day one field for the date and two fields for the user to input the clock in and out times respectively.

The problem that I am having with this right now is that, the form that I created only displays one record at a time, so it only shows the clock in and out times for one record. How can I get the form to diplay several records at a time so the user can add all the days of the week for certain employee on the same page?

Or is there a better idea on how to create this database?

Thanks in advance,

Earl

 

[jeremie_ingram]

How about creating a subform for the actual data entry, and leave it in spreadsheet view. If you base it off of a query, you could allow them to filter it down to just the dates you need. Put all of the employee info on the main form, and let them use a drop down to select whom they want.

Just an idea.......

 

[earlsinclair]

Well, that's an idea yes, but it would get too confusing like that. There are over 5 hundred employees registered on this database. It would fill up the screen with all those names and it would get too confusing for the person inputing the data. I need to make it as easy and straighforward as possible.

But thanks for the idea anyways,

Earl

 

[jeremie_ingram]

I think you misunderstand. If you create a form that displays a single user at a time, and put a drop down menu on it for quick record selection - you have about what you developed so far. Now, you should have the user data in one table and thier time records in another table - with a 1 to many relationship between them. If this is the case, create a subform on the form that displays the users information, and have the SUBFORM display the timeclock info. It will be linked to the main form via common fileds, and the subform will display only the records related to the single record displayed in the main form. Tweak it a little more and your subform could be filtered down to 1 days worth of records, a weeks, or even a whole month.

 

[jeremie_ingram]

Here is a very small example of what I was stating. You will notice that only the data related to the individual displayed in the main form will show in the subform. With further tweaking we could get the subform to be filtered by date and such.

 

[earlsinclair]

Yes, that's exactly what I ended up creating myself based on your ideas. It's basically what I want yes, there's just something that I'd like to be different and I'm having a little problem with it.

The problem is that I will have more than 500 employees registered on this database, and only like 20% or 30% of them will have data entered every week. So the way the database is built right now you have to scroll thru all the names, one by one until you find the name of the employee you want. This is time consuming given the total of employees on the list. I wanted to be able to get all the names of the employees on a drop down menu instead of just displaying them the way it is right now, so the user can type the name of the employee to search for it. I created a drop down menu and got the names from the employee table, but if you click on the employees names on that list, access attempts to modify the employee list instead of scrolling thru the records, if you can understand what I'm trying to say.

How can I get access to scroll thru records using the drop down menu instead of modifying the current record?

Another thing is, how can I prevent the user from entering data for the same date for given employee? In other words, prevent the user to put duplicate dates for the same employee?

Once again thanks for all the help,

Earl

 

[jeremie_ingram]

Once you add a drop down combo to the example I sent to you are prompted with 3 options. Choose the third option and the drop down combo will allow you to select a name, and then the form will jump to that record.

As for the restriction on one employee record set per day, here is my suggestion. Create 3 tables, 1 for the employee names, 1 for all of the punches associated for a given day, and the final table will play the role of junction table. The junction table will have 1 record from each of the other tables (Primary key from the employee table and DATE from the punches table), with both set as a concatenated primary key. This will restrict how many times an individuals name can be entered on any given day.

If an example is needed let me know, keep me posted on the good work.

 

[seamusnboo]

We're almost there now, lol.

I was able to get the drop down box to jump thru records now instead of changing the record. And I created a form for the user to input the time in/out for each employee, but showing one record at a time. You said with a little tweaking we could get this form to filter 1 week worth of records, for example. I'm having trouble with that, how would you do this tweaking?

Also, when it shows the week of records, it is going to show current records right? Well, the idea is to have like 1 week worth of records shown but with blank records, so the user can add the time in/out date for the week. Can that be done too? What they really wanted me to do is like, to be able to pick a certain week and access would fill the datasheet with the dates already, so all they needed to do is put the time in/out records. I'm not sure how this can be done, or if it can be done at all, but maybe you might know. If it cannot be done they will just have to deal with that.

Once again thanks,

Earl

 

[jeremie_ingram]

Ok, what i was thinking is that your table should hold at least one entire days worth of punches.

tblPunches
PunchPKey - Autonum
PunDate - Date/Time ~ Date punches occured
PunIFD - Date/Time ~In for Day
PunOB1 - Date/Time ~ Out First Break
PunBB1 - Date/time ~ Back from first break

etc untill you have listed a days worth of punches - thus allowing you to group/filter by date alot easier.
Now, You can also keep a Pay Period or Pay Week table which can keep track of the start and end dates. This is sooooo mcuh simpler than trying to figure out the calendar calcuations with holiday/weekends. When the user goes to open the data entry form, it can check the last day in the pay period again the current date and make them either start a new period or open a previous one for modification. Doing this would simplify being able to sort out the items by week, because they would already have an association with a period entry.
Now the other item you mentioned, were you looking to have the users access the system to input the data, or were you wanting to print something that they would fill out for someone else to input the data from?

 

[earlsinclair]

Now, You can also keep a Pay Period or Pay Week table which can keep track of the start and end dates. This is sooooo mcuh simpler than trying to figure out the calendar calcuations with holiday/weekends. When the user goes to open the data entry form, it can check the last day in the pay period again the current date and make them either start a new period or open a previous one for modification. Doing this would simplify being able to sort out the items by week, because they would already have an association with a period entry.

Could you post an example of how this can be done?

Now the other item you mentioned, were you looking to have the users access the system to input the data, or were you wanting to print something that they would fill out for someone else to input the data from?

I'm not really sure what you're asking. Basically what happens is, the person who's gonna be inputing the puches to this database is not very computer literate, so I have to make this as simple as possible. So my boss asked me to make this in a way that it will have a form showing a week worth of records, so she can scroll thru the weeks. But the thing is, he wants me to make the new week of records have the dates automatically filled so the user only needs to put the time in/out. I don't need to worry about holidays or weekends because some people work on weekeds and holidays. Is this clearer now? Can it be done or would it be too complicated?

 

[jeremie_ingram]

So only a single user will be inputing the data. That does simplify things a little.
How many punches are you planning on recording for a single day? 2? 8?
You state you have many users, but need to address 1 at a time, which we have figured out. Now you want to address 1 user for the current week only, showing the dates and punches for each. If no punches are in, then you want it blank so they can enter it or modify the record. Right?
Will a weeks worth be entered at once, or will it be a progressive daily task?
As for the table i mentioned, it is simply 1 primary key and a start date field (PerSt) and an end date field (PerEnd). You would link it to the punches table via the primary key and then use it to group the datas of the punches. I will try to work out an example for you.