UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> An Alarm For Engine Maintaining And Services, Access 2010    
 
   
Sajad
post Oct 16 2017, 08:46 AM
Post#1



Posts: 13
Joined: 23-September 17



Hello everybody

I want to set a function for my app when a specific amount of number add to engines hours time it going to alarm with both sounds and colors differential , for more details, I input every daily working hours of engines and when it going to reach to a critical hours for changing the oil and other services I want an alarm to let me know it.
I'm awaiting for your warm suggestions and helps.

thanks before
Go to the top of the page
 
GroverParkGeorge
post Oct 16 2017, 09:13 AM
Post#2


UA Admin
Posts: 31,197
Joined: 20-June 02
From: Newcastle, WA


There are probably several different ways to do this.

One would be to run a query after you update or append a new record to the maintenance table. In that query, you can sum the hours of service for the vehicle, or whatever other criteria you use. This would need to be filtered so that it only sums service hours since the last previous service was performed.

If the sum equals or exceeds your target number of hours, use conditional formatting on the form to change the background and/or foreground color of one or more controls on the form.

Such a query might look like this: "SELECT VehicleID, Sum(ServiceHours) AS CurrentServiceHours FROM tblYourVehicleServiceRecordTableNameGoesHere WHERE Date >= Max(ServiceDate)"

Of course, that's just air code to illustrate the concept and would need to be set up based on what your table(s) actually contain.

--------------------
Go to the top of the page
 
doctor9
post Oct 16 2017, 09:21 AM
Post#3


UtterAccess Editor
Posts: 17,917
Joined: 29-March 05
From: Wisconsin


Sajad,

Here's a basic table structure to get you started. I've made a few assumptions, like that you probably have several engines that are all the same type and all have the same maintenance schedule based on the type of engine, rather than having (for example) one Type AA1 engine being serviced every 3000 hours and a different Type AA1 engine being serviced every 1000 hours. Instead, I assume that all Type AA1 engines are serviced every 3000 hours.

tblEquipmentTypes
EquipmentTypeID [Autonumber, Primary Key]
strEquipmentTypeDescription
intHoursBetweenService (This is where you store how many hours should pass before the next service for this equipment type)

tblEquipment
EquipmentID [Autonumber, Primary Key]
strEquipmentName
strSerialNumber
lngEquipmentTypeID [Foreign Key to tblEquipmentTypes.EquipmentTypeID]

tblEquipmentUsageLog
EquipmentUsageLogID [Autonumber, Primary Key]
lngEquipmentID [Foreign Key to tblEquipment.EquipmentID]
dteLogDate
intWorkingHours

tblEquipmentService
EquipmentServiceID [Autonumber, Primary Key]
dteServiceDate
lngEquipmentID [Foreign Key to tblEquipment.EquipmentID]


With this setup, when you are entering the daily working hours into a form that's bound to the tblEquipmentUsageLog table, you can use the AfterUpdate event of the textbox bound to the intWorkingHours field to trigger VBA code which:

1. Looks up the most recent date that this piece of equipment was serviced (dteServiceDate), using a DMax() function.
2. Use the date found in step #1 to look up the dteLogDate for this piece of equipment that is the highest date value that is equal to or less than the date found in step 1. This is the most recent Working Hours record right before it's last service.
3. Use the date found in step #2 to look up the intWorkingHours value for this piece of equipment on that date.
4. Subtract the just-entered intWorkingHours value from the hours found in step #3 to see how many working hours have transpired since the last service.
5. Look up the intHoursBetweenService value for this piece of equipment's equipment type.
6. If the number of working hours since the last service is higher than the intHoursBetweenService value from step #5, display a messagebox/play an alarm.

If you've already got your tables set up and you're just interested in the color and sound portions of the "alarm" from your question, please let me know.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
Sajad
post Oct 16 2017, 11:39 PM
Post#4



Posts: 13
Joined: 23-September 17



Hello Mr Dennis
thanks for your guidance but as I'm a new in access I couldn't get you, so I want to share my sample work and if you mind please guide me on this form that I share,
as you see I have a daily working hours which must be added to total working hours and when the daily hours summed reached to the 300 hours (for example, because I have 4 different devices with different servicing hours) I need those alarms that I said before.
I'm so sorry that I can't understand and get your tips and helps , as I'm new here.
and thank you again.
Attached File(s)
Attached File  sample.zip ( 34.75K )Number of downloads: 9
 
Go to the top of the page
 
doctor9
post Oct 17 2017, 10:06 AM
Post#5


UtterAccess Editor
Posts: 17,917
Joined: 29-March 05
From: Wisconsin


Sajad,

Ah, I see. Based on your attachment it looks like you're new to Access, so I'll try to simplify this a bit. If you need to track the service performed on equipment, and you also need to track the daily hours the equipment is used, you need more than two tables to track it properly. Remember, Access is NOT the same as Excel, where you store everything together in a "grid". In a relational database you need to break down your data into logical groups, and each group should get it's own table. In this case, the groups are:

Engine types (Main, Auxiliary, Diesel Generator)
Engines (One record for each physical engine you're tracking)
Daily Log Entries (Daily track of how many hours an engine is used)
Oil Changes/Maintenance performed (Periodic track of when engines were serviced)

I see you have report numbers in your big table Table2, but those just seem to be a sort of calendar, with one report per "cycle". But I'm just guessing here. Also, you seem to be using "cod" to uniquely identify the engines in both tables. But in your "Table2" table, you are using both the cod value AND the engine name to identify the engine; this is the sort of "Excel" thinking you need to move away from. You just need the "cod" value, and you can link to the cod_motor table with that number to get the engine name. This is known as using a "foreign key" to link back to a "primary key" in a one-to-many relationship.

Take a look at my suggested tables again to see how this works. You store the name of the equipment in strEquipmentName, and in the daily log, the "cod" is the Long Integer lngEquipmentID field, which is the foreign key that links back to the primary key of the tblEquipment table. This way you store the name of the equipment only once in your database. Any other table that is related to that equipment would just use a Long Integer number to link to the tblEquipment table's primary key (Autonumbered, which is also Long Integer).

See how my tables have only a few fields? That's kind of the goal you have when creating tables. Rather than have one table with a bunch of fields, you want your tables to be thin and tall instead, i.e. with few fields and a lot of records. If you notice your table is getting "fat" with fields, maybe consider that you have more than one logical group of fields in one table and try to break them down.

Also note that I don't have separate "daily" working hours and "total time working" fields. You can calculate the total with the daily values. I'm kind of puzzled that you are using "report number"s instead of dates - I would think you'd want to know which days the engines are working and which days they may be down for maintenance and such. You also didn't really have any indication of WHEN an oil change has to happen, which I believe was the point of your question, so that's why I added the intHoursBetweenService field, just in case (for example) a diesel generator gets it's oil changed every 24 hours of use but a main engine gets it's oil changed every 2 hours.

By using dates in your daily log AND your maintenance tables you can more easily see the history of oil changes on an engine and figure out your maintenance costs. Plus, if you want, it would be easy to expand this design to accomodate more than just oil changes if you like.

I've tried to translate your existing data into my design so you can see the difference. Your "daily working hours" seems to be a running sum of how many hours the engine was used since the last oil change. In my design, it's how many hours the engine was used on a specific date. To see how many hours it's been used since the last oil change, you use the date from the tblEquipmentUsageLog to find the most recent oil change for the equipment, then sum the intWorkingHours values for the equipment since that date.

Note that you do NOT store this calculated total. You calculate it "on the fly" as you need it. 99% of the time, storing calculated values is not the way to go, as it can cause conflicting data. For example, if you have records that say you used the main engine 5 hours on monday, 6 hours on tuesday and 77 hours on wednesday, the total hours would be 88 hours. But then the user realizes that the 77 should have been just 7, so they go in and change it, but forget to update the total hours as well. That's what you can avoid by calculating values but not storing them.

Besides, when it comes to data entry for the usage log, I figure it's easier for someone to say "The main engine was used for 9 hours today" rather than looking at the previous usage total, and adding 9 to that and entering the result. Unless the main engine just got an oil change. With my design, you just enter how many hours the main engine was used that day, and Access handles the rest.

Since I assumed that the report numbers were individual days and you have a field called "oil daily add" I was surprised to see there was a value in only one record. If it's a "daily add" wouldn't there be a number for each day? I decided to just ignore this for now, since I clearly don't understand what it is.

The attachment includes my translation of your data as well as a few simple forms. The usage log includes everything you need to trigger an alarm. In this case, the alarm is a textbox that says "Oil change due!" after you enter that day's hours used for a piece of equipment. How it works:

1. The combobox where you select the equipment includes a hidden column that stores the number of hours between oil changes. I added a textbox that shows this number on the form. Remember, this number is drawn from the tblEquipmentTypes table.
2. A textbox that totals the hours used since the last oil change. To avoid using a long, complicated expression in the textbox, I created a simple VBA user defined function, which can be found in the code module, "modUserDefinedFunctions". It's got comments that explain how it works, but it's basically the same technique I suggested in my original reply in this thread.
3. A textbox that subtracts the total hours used since the last oil change from the number of hours that this equipment type should have before it's next oil change. If the "Tot. since last oil change" is greater than "Hours between oil changes", you need to change the oil.

This form technically can work without the "Tot. since last oil change" and "Hours between oil changes" textboxes; those are just there to show you how things work (and help me make sure I'm getting things right). But they could be useful, so I left them in there.

Hope this helps,

Dennis

EDIT: RJD helped me fix a tiny issue, so the attached includes his fix.
Attached File(s)
Attached File  Engine_Usage_Log_demo_Rev1.zip ( 43.93K )Number of downloads: 18
 

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
Sajad
post Oct 18 2017, 02:09 AM
Post#6



Posts: 13
Joined: 23-September 17



Hello again Mr Dennis


I really appreciate your warm favor and your patient to answer my question and clear explanation, I just said wow for that. thanks a lot.

your sample helped a lot and that was awesome but I need to ask some more, first I need to say that the daily oil added that you mentioned is a value that every few days our engineers add some little oil to replace of burned oil by engine, its not daily of course but for an engine just it is daily cause of malfunction and we want to know its value as a database . it can be ignored and you did well , i just told for clarification .

now I have some another question , the text "oil change due" get red after 24 hours but how it going to be reset after changing the oil.

Now I added some hours, it reached t 24 hours and got red it worked well till now but for further values ( after the first 24 hours) it still remains red, please let me know how I fix it, if I have an option to reset it to zero after changing the oil it'll be perfect.

another thing that I want was sound alarm because its a part of a comprehensive maintenance app , so I need an sounds alarm as it reached to critical value ,so the user can be better aware of it.
Go to the top of the page
 
doctor9
post Oct 20 2017, 10:24 AM
Post#7


UtterAccess Editor
Posts: 17,917
Joined: 29-March 05
From: Wisconsin


Sajad,

Sorry for the lateness of this reply.

Honestly, at this point I think you should focus more on your table structure and less on making audible alarms. If you want to add daily oil to the service log, you should then create a new table of service types, and then in the service log table you should have a long integer field to store the type of service. You will probably also want to add a quantity field to the service log if you want to track how much oil was used each time (this could be useful for oil changes as well).

Once you have the tables set up and the data entry forms working the way you want them to, THEN you can focus on the alarm expression. You'll want to only search for the most recent oil change, and ignore all other types of service. If you want the alarm to disappear, you can check to see if an oil change happened AFTER the date that the alarm was triggered. If it did, don't display the alarm.

The reason that I encourage you to focus on tables right now is because that is like the foundation to your house. Everything else is built on the foundation. If the foundation isn't stable, everything else becomes more difficult to maintain.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 04:42 PM