Automated message in Access (1 Viewer)

[Marziya]

Hi
I have an access database that keeps the employees status. in one of forms i enter the leave that they go. i want to know how can i show on main form that based on that data that enter, how many person are on leave? and also when their leave is over. access alert me by a message box that these employees should be on work. thanks. i am new to access actually.
thanks in advance

 

[jdraw]

It would be helpful if you would show us your tables and relationships to provide context. An image of your relationships window with all tables extended to show all fields is preferred.

 

[Marziya]

here this is my database relationship.
please take a look.

 

[Pat Hartman]

For starters, it looks like you have table level lookups. They are a crutch and once you get into building an actual application, you will run into nothing but trouble from them. No user will ever open a query and look at it so having the users table show the fullName rather than the ID is not necessary. If you want a query to show both the ID and the name, you would join the two tables and bring data from each. As for forms and reports, they would use combos so the bount field would be the ID but the visible field would be the name.

Another really bad attribute (but which I'm sure you currently think is good) is that table level lookups obfuscate the data so when you open a table or a query, you don't really know what you are looking at. For this same reason, experts recommend that you do not use any formatting on table columns. For example, if you have a date field and you format it as short date but you mistakenly use Now() (which includes time as well as date) to populate the field, it will contain a time component that you don't see. But time will interfere with selection and sorting and grouping.

Rather than storing full name which can be very hard to parse, store the parts. At a minimum use FirstName and LastName. You can also use Middle, Prefix, and Suffix. That gives you the most flexibility. It is easy to concatenate the parts but hard to split them.

I'm also curious why you have a failure to fingerprint table. Why would you not log fingerprinting as a positive event?

 

[Marziya]

For starters, it looks like you have table level lookups. They are a crutch and once you get into building an actual application, you will run into nothing but trouble from them. No user will ever open a query and look at it so having the users table show the fullName rather than the ID is not necessary. If you want a query to show both the ID and the name, you would join the two tables and bring data from each. As for forms and reports, they would use combos so the bount field would be the ID but the visible field would be the name.

Another really bad attribute (but which I'm sure you currently think is good) is that table level lookups obfuscate the data so when you open a table or a query, you don't really know what you are looking at. For this same reason, experts recommend that you do not use any formatting on table columns. For example, if you have a date field and you format it as short date but you mistakenly use Now() (which includes time as well as date) to populate the field, it will contain a time component that you don't see. But time will interfere with selection and sorting and grouping.

Rather than storing full name which can be very hard to parse, store the parts. At a minimum use FirstName and LastName. You can also use Middle, Prefix, and Suffix. That gives you the most flexibility. It is easy to concatenate the parts but hard to split them.

I'm also curious why you have a failure to fingerprint table. Why would you not log fingerprinting as a positive event?

First of all , thanks for your attention.

Let me explain , what is my database logic.
For defining new user , the current operator should know , who is the id that she choosed, thats why i bound id and name but the visible field is the name.

Honestly the reason that i gather first name and last name in same field is I didn't know how should make auto fill that when I choose first name for example in combo box in mission form , the last name field automatically based on the first name filled. Thats why i entered them in one field.

My mean by failuretofingerprint is, some days some employees forget to record their fingerprint at the begining of the worktime and at the end that they leave office , their records should be save for their timesheet.

 

[Pat Hartman]

For defining new user , the current operator should know , who is the id that she choosed, thats why i bound id and name but the visible field is the name.

I didn't say to not use lookups on forms/reports. I said to not use them on tables. There is a difference. On a form - remember, your user - NEVER interacts with anything except forms and reports - you use a combo, the user picks the user name and the ID is what gets stored.

When you want the combo to show more than one field, you can concatenate them in the RowSource of the combo. With names, you can concatenate them as First then Last or as Last comma First.

Use a query like this as the RowSource for the combo. This way even when the combo is closed both first and last names will be visible. You use the same technique whenever you want to show the names together. If you want to send a letter, you can use Dear "lastName" or Dear "FirstName" which is much more correct than using Dear "firstname lastname".

Select UserID, FirstName & " " & LastName As FullName From tblUsers
Order by FirstName & " " & LastName;

The fingerprint should be a positive table rather than a negative one. When they open the app with their fingerprint, you can log it (assuming you have a fingerprint reader attached to the computer). Otherwise, how do you know if the user entered using his fingerprint? If you don't have a fingerprint reader, you can still log when the user opens and closes the app.

 

[Marziya]

I didn't say to not use lookups on forms/reports. I said to not use them on tables. There is a difference. On a form - remember, your user - NEVER interacts with anything except forms and reports - you use a combo, the user picks the user name and the ID is what gets stored.

When you want the combo to show more than one field, you can concatenate them in the RowSource of the combo. With names, you can concatenate them as First then Last or as Last comma First.

Use a query like this as the RowSource for the combo. This way even when the combo is closed both first and last names will be visible. You use the same technique whenever you want to show the names together. If you want to send a letter, you can use Dear "lastName" or Dear "FirstName" which is much more correct than using Dear "firstname lastname".

Select UserID, FirstName & " " & LastName As FullName From tblUsers
Order by FirstName & " " & LastName;

The fingerprint should be a positive table rather than a negative one. When they open the app with their fingerprint, you can log it (assuming you have a fingerprint reader attached to the computer). Otherwise, how do you know if the user entered using his fingerprint? If you don't have a fingerprint reader, you can still log when the user opens and closes the app.

I think I got your meaning.

we have a fingerprint reader that works independently from this database.
at the end of the month we see the logs that this fingerprint reader has, and for employees that don't have the entry fingerprint or fingerprint to exit ,based on this database that we save the days that they couldn't, we fill their time sheet.

 

[Marziya]

Hi
I have an access database that keeps the employees status. in one of forms i enter the leave that they go. i want to know how can i show on main form that based on that data that enter, how many person are on leave? and also when their leave is over. access alert me by a message box that these employees should be on work. thanks. i am new to access actually.
thanks in advance

Is there anybody that elaborate on how can I do this?

 

[arnelgp]

you can download a demo database here:
Calendar | Access World Forums (access-programmers.co.uk)

it is really intented for holiday/vacation monitoring.

 

[Marziya]

you can download a demo database here:
Calendar | Access World Forums (access-programmers.co.uk)

it is really intented for holiday/vacation monitoring.

Actually it is hard to customize my database according it.

 

[arnelgp]

using query, to count how many are on Leave:

SELECT
    Count("1") As CountOfPersonOnLeave
FROM tblLeave
    WHERE LeaveDateEnd > Date();

you can only know who are those that ended their leave and has not reported yet.
you will be needing another table, the timesheet.
if a person has ended his Leave (say today), he should have a Timein for today:

SELECT
    tblPersonel.PersonelCode,
    tblPersonel.PersonelFullName
FROM tblPersonel INNER JOIN tblLeave
    ON tblPersonel.PersonelCode = tblLeave.PersonelCode
WHERE (((tblLeave.LeaveDateEnd)<=Date())
AND ((DCount("1","tblTimeSheet","PersonelCode = " & [tblPersonel].[PersonelCode] & " And DateIn >=" & [tblLeave].[LeaveDateEnd]))=0));

 

[Jason Lee Hayes]

I think I got your meaning.

we have a fingerprint reader that works independently from this database.
at the end of the month we see the logs that this fingerprint reader has, and for employees that don't have the entry fingerprint or fingerprint to exit ,based on this database that we save the days that they couldn't, we fill their time sheet.

What finder print reader are you using out of curiosity; been working heavily on this recently supporting alternative manufacturers and won't be surprised if we have a solution already for the fingerprint reader to communicate directly with your database.

 

[Marziya]

using query, to count how many are on Leave:

SELECT
    Count("1") As CountOfPersonOnLeave
FROM tblLeave
    WHERE LeaveDateEnd > Date();

I use this code and it works. thanks

SELECT
tblPersonel.PersonelCode,
tblPersonel.PersonelFullName
FROM tblPersonel INNER JOIN tblLeave
ON tblPersonel.PersonelCode = tblLeave.PersonelCode
WHERE (((tblLeave.LeaveDateEnd)<=Date())
AND ((DCount("1","tblTimeSheet","PersonelCode = " & [tblPersonel].[PersonelCode] & " And DateIn >=" & [tblLeave].[LeaveDateEnd]))=0));

for this part, Isn't there any way than to have a time sheet table?

 

[arnelgp]

what you can do is add another field to your tblLeave, a Yes/No field (say ReportedAtWork) confirming that
the person, indeed, already reported to work.

then you modify the Query:

SELECT
tblPersonel.PersonelCode,
tblPersonel.PersonelFullName
FROM tblPersonel INNER JOIN tblLeave
ON tblPersonel.PersonelCode = tblLeave.PersonelCode
WHERE ((tblLeave.LeaveDateEnd)<=Date()) And tblLeave.ReportedToWork = False;

now it is your responsibility to check if those on the list did reported to work and
Update ReportedToWork field to True (Yes) if indeed.