I am developing a database to track employees. Basically to see if they are at work or at home. We have approx.190 employees. we work two shifts of 7 days at a time. Lets call them A and B shift.
What I would like to do is have an area that todays date is entered. Once the date is entered, a list of employees that are working will pop up along with a list of employees that are home with there respective phone numbers.
Is this possible and if it is, how the heck do I do it ????
Thanks for all the help in advance, ya'll guys and gals have been great !!!!
Well, damn, cher - you just down the road from me, I gawrontee!
OK, there are many ways to skin this cat. But you probably need a little VBA to do it. Are you OK with that?
As to your problem, the issue of today's date is nothing. The EASY way (if it is ALWAYS today's date) is to define the text box as an unbound item. Then call up its properties page. In the control source line, right-click once in the right-hand data area. You should get a pop-up that includes the Build option. Click that.
Now you get a "Build" dialog box. One of the things in the little tree diagram in the lower left corner is "functions" - click that to get it to expand. Then click the "Built-in" functions. Then find the category for date/time. Then click the option for "Date".
And finally you are done. (Or you could have just typed in "=Date()" (without the quotes).
The harder part is how you are going to tell who is ON and who is OFF shift. This requires some logic you haven't mentioned. Before we get too deep in THIS swamp, I would prefer to ask if you have already address the issue of knowing which shift is "ON" for a given date?
I have found a fairly simple way round this, I think!
Firstly I used MS Excel to sort out who's on and who's off. I created a list of all dates for this year 1st Jan to 31st Dec. Then you just need to find out which day your A shift started on and type in A beside the first 7 dates, and B for the next 7. After that you can use an Autofill to paste in the shift pattern for the rest of the year.
In a third column you can create an 'inverse' shift pattern, ie the shift which is at home on that day.
Export this table of dates and shifts into a new MS Access table. Then you just have to put a 2nd table into your database with each employee, their details ie phone no. etc. and which shift they are on.
2 queries then complete the task - one shows the people who are on shift, the other shows those who aren't and their phone numbers (other details could be included if you want). You need to have the date and shift fields in one query and the date and inverse shift in the other. By using The_Doc_Man's suggestion of DATE() in the criteria for date all you have to do each day is run both your queries side by side.
As I'm sad I've actually built a sample of this for you and attached. Hope it's what you wanted.
Actually, after thinking about it, there is a nearly trivial way to do this. To understand it, look up the following topics in the help files: DateDiff, MOD operator, IIf function, SELECT clause, FROM clause, WHERE clause, SQL query.
Pick a date (any date) on which the "A" shift begins. It does not matter as to the day of the year, day of the week, day of the month, etc. It must be the date on which an "A" shift starts their week. It must be a day that, when 7 days have elapsed, "B" shift will start.
In your personnel table, mark folks as "A" shift or "B" shift. If they change shifts, you mark the changes in your personnel table.
Build a query that selects folks from your personnel table based on their shift, showing whatever you wanted in the list. Like, name, phone, cellphone, whatever.
In the query, you will have exactly ONE ugly expression. I'll break it down for you because it is just flat-out messy. Everything else is VERY straight-forward. I'll do it in SQL because you can type that in rather arbitrarily. And I'll add some spacing and toss in some line delimiters to make sure it cleans up correctly.
Code:
SELECT [FName],[LName],[HomePhone],[CellPhone], ...
FROM [Persons]
WHERE [ShiftCode] = IIf( ( ( ( DateDiff( "d", "{reference date}", Date()) / 7 ) mod 2 ) = 0 ) ,"A", "B" ) ;
Of course, you will put in the correct reference date that is the starting date for "A" shift, in place of the {reference date} - but you will still enclose the date in quotes. You will add or correct the names of any fields you need to see in the SELECT clause. Put the correct table name in the FROM clause.
Now, let's look at the parts. DateDiff is a function that computes the difference between the two dates in units that you specify. Units of "d" specify a difference in days. So this part says ... "Compute the difference between the reference date and today's date in units of days." The result of the computation is a LONG integer. So this will be integer days starting from 0 on the reference date and going on for more years than you need to consider.
OK, now the next part divides by 7 because the shifts are 7 days long. (If you had 6-day shifts, this would be divided by 6.) It is an integer divide, so the first 6 days this answer will be 0. The next 7 days, the answer will be 1. The third week, the answer will be 2. And so on.
Next, decide the current shift code. Since you have 2 shifts (A and B), use MOD 2 to get the modulus (remainder). This will be either 0 or 1, and since the date was chosen for "A" shift's start, the even numbers will belong to "A" shift. If you had three shifts, this would be MOD 3 and I wouldn't have suggested IIf as the outermost encapsulation.
Finally, you are comparing it to 0 because you want a True/False value for the IIf function's expression (first argument). And the two other arguments for IIf are the values to use for the expression when it returns True or False, respectively.
So the WHERE clause will get either "A" or "B" as the value of the IIf depending on how many days
There, that's how you would do it, and this doesn't even require you to build a form. Just open the query. Adjust your rows to the right width and save the updated format. That's your list. You can export it to a spreadsheet or print it whenever you like, because you can perform those actions on queries.
If you also need a query for who is OFF shift today, you would type in the same exact query, but with the "A" and "B" in the opposite order representing the second and third arguments of the IIf function. Or you would use <> instead of = in the WHERE clause,
In this one I tired for a long time to get this down
to two forms but when trying to put todays date
on working people forms ....date() in a text box would not
show...so this one would have three forms and two buttons
thanks to everyone who responded and sent samples !
