Question Help - How Can I Tackle This Problem? (1 Viewer)

collen

Registered User
Joined
Jan 24, 2019
Messages
10
[FONT=&quot]Maintenance Database [/FONT]

[FONT=&quot]Important Variable – Time[/FONT]

[FONT=&quot]Queries:[/FONT]
· [FONT=&quot]Calculate and flag maintenance of machines after a 250-hour cycle.[/FONT]


· [FONT=&quot]Create colored flags for each period, Green (between 0-100), Yellow (between 101-200) and Red (between 201-250).[/FONT]


· [FONT=&quot]Auto generate 2 report cards to be printed (for each machine), Yellow meaning machine “may need to be maintained soon” (once hours reach 200), then Red meaning machine “needs to be maintained now” (once hours reach 250), allow Access to autosave reports as PDF for print out.[/FONT]


· [FONT=&quot]Calculate Fuel usage (not very important) for all machines[/FONT]


· [FONT=&quot]Link Employee working hours (as the maintenance depends on how much they have used the machines, all employees work 8 hour shifts daily. There are two shifts; Day shift starts at 6:30am and ends at 15:30pm, Night shift starts at 16:00pm ends at 1:00am, auto deduct 1 hour (mandatory break/lunch) before calculating the machine working hours or cycle. Allow an extra hour for overtime.[/FONT]

[FONT=&quot]Forms: [/FONT]
· [FONT=&quot]Mainframe/application (which should be the default menu after opening the Access File) must include buttons for entering data such as Name/Last Name, Machine they use - both datafield’s must be in a form of a drop down menu that should restrict picking of machines except to the available one’s, any Employee can pick any machine to use, other datafield’s for input are the working hours or shift periods, that will help queries auto calculate machine working cycles but all this data is for ongoing dates, meaning it should continue and not be set backwards. [/FONT]


· [FONT=&quot]An extra field may the one for fuel usage, where a user can just input how much fuel was bought for said machine for the day or any date when it was done, this field can help the fuel usage be calculated by month (for each then all machines in a report card) then maybe generate a report card (which can only be if requested). This is not very important as it easy to deal with cause user only has to enter total amount of fuel added on said date or time[/FONT]


· [FONT=&quot]Form must restrict users from adding or altering data except said and required data.[/FONT]

[FONT=&quot]Reports Cards: [/FONT]
· [FONT=&quot]Cycle reports that depend on the hours mentioned above ONLY, which can be daily depending on whichever machine is being used. These are the reports that should be auto generated then saved into PDFs for later forwarding[/FONT]


· [FONT=&quot]Monthly report that calculates amount of fuel being used. Reports depend on user request – not important at all.[/FONT]


· [FONT=&quot]Monthly report that calculates hours or employee performance.[/FONT]


· [FONT=&quot]Report card that shows machine usage before maintenance by all users, also good in determining which employee uses said machine or machines a lot.[/FONT]


[FONT=&quot]Important Variable – Machines Available (10) and Employee/Workers usually work 180 hours per month, up to 45 hours a week (including overtime – not mandatory), only 8 hours a day (excluding the deductible lunch/break time), 9 or 10 hours depending on overtime.[/FONT]
 

Attachments

Frothingslosh

Premier Pale Stale Ale
Joined
Oct 17, 2012
Messages
3,257
This stinks of homework.

Please tell us what you have tried so far, and we'll be happy to help point you in a viable direction. As a general rule, however, we will not do the work for you.

Even if it isn't homework, this is entirely too open-ended a question for a realistic response, and 'tell us what you've tried so far' is still your best option.
 

pbaldy

Wino Moderator
Staff member
Joined
Aug 30, 2003
Messages
33,636
What problem exactly are you looking for help with? Or should we do the entire homework assignment?
 

collen

Registered User
Joined
Jan 24, 2019
Messages
10
I've attached something I actually tried to do, there's a lot I wish I could understand - not good at queries and functions or even laying out how this can be structured. I can bet I will do better at the design (layout) part of this.



My basic steps here was determining that Machine's and Employees should just go to separate table data, I created a shift data where I actually even tried working out how time can be calculated but with no real direction forward. If this didn't have time as the main variable, I think I would have faired better for a layout designer.:banghead:
 

collen

Registered User
Joined
Jan 24, 2019
Messages
10
What problem exactly are you looking for help with? Or should we do the entire homework assignment?

How do I create a query (say I have assigned all my required tables) that will help me make calculations based on any of important data I have to input on an application part of my form, how do I come up with the functions or formulas for those calculations. How do I set my relationships when I'm on the Query builder, I mean for any of the variables I need to use.


I do have study material which actually doesn't focus much on time queries except for the usual PRICE and MAX COUNT variables.
 

Frothingslosh

Premier Pale Stale Ale
Joined
Oct 17, 2012
Messages
3,257
Okay, standard stops in database design.

First, determine what data is going to need to be generated in reports.
Then determine all the data you're going to need to store and track to meet requirements.
After you've done that, build your tables.

Don't worry about the query until you have those steps complete.

As long as you're trying, people will be certainly answer any questions you might have regarding how-to, especially normalization.
 

collen

Registered User
Joined
Jan 24, 2019
Messages
10
Okay, standard stops in database design.

First, determine what data is going to need to be generated in reports.
Then determine all the data you're going to need to store and track to meet requirements.
After you've done that, build your tables.

Don't worry about the query until you have those steps complete.

As long as you're trying, people will be certainly answer any questions you might have regarding how-to, especially normalization.

I'm on it, I guess I was only making this hard for myself by focusing on the end part of what I have to do.
 

collen

Registered User
Joined
Jan 24, 2019
Messages
10
That's a good path to follow and I agree your tables aren't complete yet. In this thread I showed tables I have in a vehicle maintenance program in post 11 and jdraw posted some data models in post 10. Either of those might give you some ideas on how to go forward

Thank You Paul
 

collen

Registered User
Joined
Jan 24, 2019
Messages
10
"Data to be generated in Reports:

*Maintenance of Machines/Equipment after (200 hours) *each vehicle
*Maintenance of Machines/Equipment after (250 hours) *each vehicle
*Fuel consumption/usage *all vehicles (Makes data set much easier to work with
*Employee Performance
*Day Shift (must help calculate hours for Maintenance) /for each employee
*Night Shift (must help calculate hours for Maintenance) /for each employee



Data Needed:

*Employee working hours
*Machine working hours (depended on Employees)
*Fuel usage"




now then, I am able to make tables that I think I know and understand, but how do I come with a structure for the tables that will help me calculate the shift working hours which I have to link to the main query (currently not at this stage) that solves my problem?


I did look at the database for examples, but its much like a hay stack if you don't know what to exactly look for. I see examples for Maintenance of Parts in vehicles, which isn't exactly what my problem is and is not so much a good reference for someone that doesn't fully understand the functionality.



Can you guys help with the tables, please?
 

mike60smart

Registered User
Joined
Aug 6, 2017
Messages
497
Hi

Can you upload a zipped copy of the Db showing the tables that you currently have?
 

jdraw

Super Moderator
Staff member
Joined
Jan 23, 2006
Messages
12,549
Collen,

Others have hinted, but I'll ask directly-- Is this a homework assignment?

A few observations:

In your Employee table there is a column for Qualifications. Your table design only allows for 1 qualification per employee. The implication is that an employee who is qualified to operate a front end loader, may also be qualified to operate a grader or dump truck. Seems your structure may be limiting your "requirements" but we don't know the details.

You have machine types for which no Employee is qualified to operate (Excavator).

It would seem different Employees could operate different equipment from one time (hour/day/week) frame to another. Your shift hours table is incomplete and may not allow a mix of employee and equipment. Again we don't know the details.

Table2 and Field1 are poor choices for database tables/fields. They convey no meaning nor intent to the reader/user/maintainer. Make your names more meaningful.

What exactly does Employee Performance entail? Where are the details/fields to capture the info?

You show fuel tank capacity for vehicles/machines, but I see no info re fuel usage/consumption?

It is always helpful to you and readers if you provide a description of WHAT the business is that you are attempting to support with a database. The description should be in simple terms (avoid jargon and quasi-database lingo). I think this would be a good place to focus to make sure you understand the problem to be solved. Designing as you go is not a great strategy in most cases.

If this is a homework assignment ( or even if it is a task given to you for resolving) can you show us the homework or task assignment requirements?

Good luck with your project.
 
Last edited:

collen

Registered User
Joined
Jan 24, 2019
Messages
10
Collen,

Others have hinted, but I'll ask directly-- Is this a homework assignment?

A few observations:

In your Employee table there is a column for Qualifications. Your table design only allows for 1 qualification per employee. The implication is that an employee who is qualified to operate a front end loader, may also be qualified to operate a grader or dump truck. Seems your structure may be limiting your "requirements" but we don't know the details.

You have machine types for which no Employee is qualified to operate (Excavator).

It would seem different Employees could operate different equipment from one time (hour/day/week) frame to another. Your shift hours table is incomplete and may not allow a mix of employee and equipment. Again we don't know the details.

Table2 and Field1 are poor choices for database tables/fields. They convey no meaning nor intent to the reader/user/maintainer. Make your names more meaningful.

What exactly does Employee Performance entail? Where are the details/fields to capture the info?

You show fuel tank capacity for vehicles/machines, but I see no info re fuel usage/consumption?

It is always helpful to you and readers if you provide a description of WHAT the business is that you are attempting to support with a database. The description should be in simple terms (avoid jargon and quasi-database lingo). I think this would be a good place to focus to make sure you understand the problem to be solved. Designing as you go is not a great strategy in most cases.

If this is a homework assignment ( or even if it is a task given to you for resolving) can you show us the homework or task assignment requirements?

Good luck with your project.



I did post the rest of question, it only said "You are an administrator in a plant hire business, create maintenance reports..." then there was all that detailed info I pasted in the 1st text/question I posted.
 

mike60smart

Registered User
Joined
Aug 6, 2017
Messages
497
Hi Collen

You are not answering the question being asked.

What is your daily process.

Is it something along the lines of :-

An employee reports for a given shift and has a schedule of machines to maintain.

How are the machines allocated to an employee?

We need an explanation of what happens on a daily basis in your workplace.
 

collen

Registered User
Joined
Jan 24, 2019
Messages
10
Hi Collen

You are not answering the question being asked.

What is your daily process.

Is it something along the lines of :-

An employee reports for a given shift and has a schedule of machines to maintain.

How are the machines allocated to an employee?

We need an explanation of what happens on a daily basis in your workplace.



I'm kind of confused as well.


But I think the requirement here is to log any shifts an employee has to do, while assigning a machine they will operate, there after, while you log the shifts for employees, a table or query might just be able to determine how much a machine has worked before it has to go for maintenance (after the 250 hours).


In the table I did, I thought it might be good to pick a machine from a drop down menu and assign to said user for a day, which might help determine the hours the machine has worked, but I don't know how to make any calculations about time nor do I even understand Access enough to know how to do the actual tables for that or even queries.
 

jdraw

Super Moderator
Staff member
Joined
Jan 23, 2006
Messages
12,549
Collen,
If that is all the info you were given, then you really do not have a requirement or facts sufficient to get the info identified. You shouldn't have to say " I think the requirement here is ...". If the requirement is not clear, you have an obilgation to ask questions; and if questions aren't allowed, then I suggest you make and state your assumptions and design from your stated requirements. You should lay out the business facts as you understand or concoct them.

Here is an example of a problem statement that is used on one of the tutorials from RogersAccessLibrary.. You might make some assumptions in a similar manner to help with your design.

Sample narrative from RogersAccessLibrary tutorial

ZYX Laboratories requires an employee tracking database. They want to track information about employees, the employee's job history, and their certifications. Employee information includes first name, middle initial, last name, social security number, address, city, state, zip, home phone, cell phone, email address. Job history would include job title, job description, pay grade, pay range, salary, and date of promotion. For certifications, they want certification type and date achieved.
An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst, QA Administrator). Employees can also earn certifications necessary for their job.

Layout a business scenario, so you will have a requirement and a target for your database. You were given insufficient facts in my view, so if you don't have them, and can't get them --invent some, record them and design with those in mind.

You might want to spend 30-45 minutes working through that tutorial from RogersAccessLibrary. I think it may answer many of your concerns and remove confusion.

Good luck.
 

Cronk

Registered User
Joined
Jul 4, 2013
Messages
2,232
Jack, IMO it's open season on assuming (making your own rules) where the assignment is not fully specified. Obviously something we could not do in a real situation. Maybe the course instructor is looking for initiative but in any case is open to fair challenge.

Collen, I'd take it that one machine can be operated by one employee in any particular shift. Record that in a relational table any you can derive how much a machine is used between two dates and which employee operated which machines in any period, or all employees who operated a particular machine during any period.

In real life, I'd expect you'd need to provide for machines not being used in a shift, or employees operating a machine for only part of a shift (illness, injury) but if not asked specifically for this, omit it.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom