Employee Lateness

[Prometheus48]

Hi everyone, hope you can help I have a database that tracks sickness, absence, conduct etc using queries. But I want to produce something that will tell me how many times someone is late. This is logged by site, date, first name, last name and (lateness) as part of a drop selection the managers use.

I would like to some how have something pop up listing all those that have been late more than three times, as this forms part of the HR process for my company.

thanks again.

 

[Isaac]

Describe exactly how you capture site, date, first name, lastname and (lateness) already - in detail. Tables especially, but anything that might be relevant. Then we might have a chance to answer your question.

 

[Prometheus48]

Describe exactly how you capture site, date, first name, lastname and (lateness) already - in detail. Tables especially, but anything that might be relevant. Then we might have a chance to answer your question.

Hi Isaac, these are added into a table and forms are used for the data input. The lateness is selected from a drop down list within that form.

 

[Prometheus48]

Over what period?

Hi ya, this would be over a month to month basis.

 

[CarlettoFed]

Have you already developed anything? if yes then attach a sample file.

 

[The_Doc_Man]

Just remember that Access is as dumb as a box of rocks about anything except building database structures. It won't tell you anything you didn't tell it first. If you want to count "late/tardy/absent/whatever" then you need to record that in some way that a query can easily determine. Then it is a matter of a COUNT query. Look up this topic: "SQL Aggregates" - because counting something in an SQL statement is member of the set of SQL aggregate functions.

You need to make a formal little document for your own use so that you can determine what you intend to do about whatever it is that you are counting. You will find that with Access it pays to plan twice and code once. (My father-in-law was a carpenter whose rule was "measure twice, cut once.")

 

[Prometheus48]

Have you already developed anything? if yes then attach a sample file.

Sorry but I have sensitive information on here so won’t be able to post any sample on here.

 

[Prometheus48]

Just remember that Access is as dumb as a box of rocks about anything except building database structures. It won't tell you anything you didn't tell it first. If you want to count "late/tardy/absent/whatever" then you need to record that in some way that a query can easily determine. Then it is a matter of a COUNT query. Look up this topic: "SQL Aggregates" - because counting something in an SQL statement is member of the set of SQL aggregate functions.

You need to make a formal little document for your own use so that you can determine what you intend to do about whatever it is that you are counting. You will find that with Access it pays to plan twice and code once. (My father-in-law was a carpenter whose rule was "measure twice, cut once.")

Hiya I’ll look into the SQL statements for a count query.

 

[CarlettoFed]

A few records are enough, replacing sensitive data with random ones, especially to see the structure of the tables and relationships.

 

[Prometheus48]

A few records are enough, replacing sensitive data with random ones, especially to see the structure of the tables and relationships.

Hiya I’ll post some tomorrow when I am back at work, I don't have any related tables.

 

[Prometheus48]

That's a good idea. All you need post is the form or forms relevant to your problem and tables relevant to your problem.

If I can see your form(s) and your table(s) it makes it easier for me to analyse your question and formulate a response.

If you (and I'm not referring to you personally I'm referring to anyone that asks a question) don't post anything, then that means I will need to create something to help me guess what the problem is.

I have several sample databases which I can quickly fire up to test different scenarios. But this means there's a high probability I will miss the essence of the problem.

If I do solve the problem and present you with a sample database, then you will have to convert everything in the sample to suit your database.

However if I start out with your database, it's going to be easier for you to implement the solution.

When I say "your database" I mean a mockup of your problem in the simplest form possible with the least amount of forms and the least amount of tables, and data in the tables.

If you provide a sample database I can fire up and inspect, then it's much more likely that I will do that. If I need to create a sample database myself then I might not have the time, and I will skip the question, finding one that is quicker and easier to answer.

My guess is most other people here are the same, too busy to get deeply involved in a complicated question, they will pick off the simple ones first.

Hiya makes perfect sense, I’ll put together a sample table and form that goes with it. Thanks again.

 

[Prometheus48]

Hiya makes perfect sense, I’ll put together a sample table and form that goes with it. Thanks again.

Hiya, i have attached a test DB with a table and a form, any help would be greatly appreciated.

 

[Prometheus48]

Hiya, i have attached a test DB with a table and a form, any help would be greatly appreciated.

Sorry doesnt seem to have attached, file is too large? i have shortned this down. If you have an email address i can place this in my Office 365 account and share the folder with you.

 

[bob fitz]

Sorry doesnt seem to have attached, file is too large? i have shortned this down. If you have an email address i can place this in my Office 365 account and share the folder with you.

Compact and Repair the db and then Zip it. Post the zipped file

 

[arnelgp]

Sorry doesnt seem to have attached, file is too large?

share in dropbox?

 

[Prometheus48]

Compact and Repair the db and then Zip it. Post the zipped file

Hi Ya, i have included 20 test records along with a form i designed for it.

 

[arnelgp]

check and test.
i made a function in Module1 called in your form.

 

[Prometheus48]

check and test.
i made a function in Module1 called in your form.

Hiya, brilliant thank you. Just tried it, how could i modify it to count the number of lateness in one month, for each employee please.

 

[arnelgp]

for the current month and year?

 

[mike60smart]

Hi Ya, i have included 20 test records along with a form i designed for it.

Hi
I would suggest that you should have a number of tables vice just 1 table.

See that attached example of an ER Diagram