Email report when equipment service is due

[Stephenson]

Hello all,



I'm working on a DB that will include the service for our excavation equipment. There is a table, tblEquipment that has a field for current equipment use. This is either the current miles or hours depending on the equipment. There is also a tblEquipmentServiceInterval that has fields for the service to be done, service intervals, parts required, etc. I need to have a way to check the equipment use at the last service to the current use and the service interval. I have no idea on how to go about this.
I would like to email a report of service due/nearing due to the mechanic. Although I've done a little bit of VBA I'm not even sure where to start on this one.

I would appreciate any pointers in the right direction for my DB.

Thank you for having a look,

John

[CJ_London]

start with the basic query that gives you the data required.

you haven't provided enough information to suggest what this query would look like but assuming your table contains a next service date field (or you can work it out from say last service date + frequency)and you want to provide say 5 days notice then it might look something like

SELECT *
FROM tblEquipment
WHERE nextservicedate<=date()+5

[Stephenson]

Thank you for the reply Ajax.

Depending on the equipment the service interval could be either based on hours or date. The units of measure the equipment goes by (hours/miles) is determined in table tblEquipment. The unit for service interval is defined in table tblEquipmentServiceRequirements as well as the service to be done.
I'm using a right join query in forms that record the equipments hours/miles to update filed CurrentUse in tblEquipment. This field would be the most current record available for checking to see when service is due based on hours/miles. Table tblEquipmentServiceLog would record when service is done and at what hours/Miles. Any equipment service based on time elapsed could use field ServiceDate against the actual date.

You've presented a great idea. It would be great to be able to send the email when the equipment is nearing its required service. Thank you for that.

Bellow are the tables I've previously mentioned.


If I create a query as you've suggested what the do I do with it? I'm uncertain of what the next step would be.

I hope I'm not asking too much. I'm having a very difficult time wrapping my head around how to do this.

Thank you for the help,

John

[Micron]

I say this with many years of experience in equipment reliability and maintenance work order management.
You should split the miles and hours into separate fields and let the form decide which one to allow input into based on either equipment type or a separate flag field (checkbox) otherwise you are making calculations more difficult. Not only that, it will prevent errors such as entering 12589 miles for an hours value.

No doubt you are inputting usage values against the equipment on a regular basis, so all you need to do is subtract the last reading from the last recorded service reading to get the current elapsed value. So if on Monday the last reading was Friday at 1030 hours and the last service reading was 850, the usage is 180 hours. However, I can't figure out if you have the necessary fields since some of the names can mean more than one thing to me. So it is not clear if you have a place for the requirement AND the last service usage amount AND the current usage, but I think not.

IMO, your design needs some work. I say this because your names are way too long, you appear to be using multi-value fields (bad, but that's just mine and a few thousand other designer's opinion) and you have your link from parts to service log backwards. Your way will require a repeat of all the service log data for each part ordered. I'm not sure if you ought to be joining servicelog PK or equipment ID to the parts log. I suggest you look here http://www.databaseanswers.org/data_models/
and do a CTL-Find for "vehicle" and take a look at some of the data models. You probably won't find one that exactly matches your needs, but it should get you thinking. Maybe even do more research elsewhere. Another option would be to create a complete narrative about what you have, what is required, and what the components are but strictly from a business standpoint. Include nothing about any db you've designed thus far as it's not relevant to the narrative. You have to take the approach that you're explaining things to a new employee - i.e. assume we know nothing.

[Stephenson]

Hello Micron,

I appreciate your input. Thank you for the great link. I think I may just spend my day trying to absorb the information listed there.

Splitting miles and hours into their own fields is a good idea, I will work on that as well as my field names. I agree they are a bit unclear. As far as multi-value fields there are none in this database. Perhaps there is an error in my table layout that appears as though there are multi-value fields? You're 100% correct on the relationship I have between tblService log and tblEquipmentPartsPurchaseLog. My intention was to use tblEquipmentPartsPurchaseLog to log all the parts purchased for services, update any vendor pricing as well as log and track warranties.

I do not want to be presumptuous here. Would you like me to post the narrative you suggest I create here? I feel you're correct that that is a major step I've leaped right over. I will put this together as it will help me solidify my needs and help address any concerns.

Thank you again for taking a look at my post and helping out.

John

[Micron]

Sure, post your narrative here. Maybe search this site using that as a keyword and see what you get - I have seen some pretty good ones here, just don't know exactly where they are.

The indented fields at the bottom of your PartDetails table image made me think they were multi-value. I've never used them, nor will I, so I confess that I've never seen a relationships view of them. If you're not using them, then why are 3 field names indented? Also, I see that they refer to images. If you're storing images in your db, that's not a great idea either. They take up too much space to say the least.
Maybe look at naming conventions here http://allenbrowne.com/AppIssueBadWord.html
here http://www.access-programmers.co.uk/...d.php?t=225837
and here http://access.mvps.org/access/general/gen0012.htm
I agree yours are a bit unclear, but more importantly, they are far too long. There is a 64 character limit on field and object names, which you're staying under. The problem is, they will become quite unwieldy when you have to work with code or macros. Imagine having to work with a sql statement like

SELECT tblEquipment.EquipmentNumber, tblEquipment.EquipmentUnitOfMeasure, 
tblServiceEquipmentRequirements.EquipmentServiceRequirementsID, tblServiceEquipmentRequirements.EquipmentID, 
tblServiceEquipmentRequirements.EquipmentServiceID INNER JOIN tblEquipment.EquipmentID ON 
tblEquipmentID = tblServiceEquipmentRequirements.EquipmentID...

Crikey! That's only 2 tables, 5 fields and 1 join (don't scrutinize the statement too much - it's only "air code").
This could be more like
SELECT tblEquip.EquipNo, tblEquip.UOM, tblServReqmnt.ReqmntID, tblServReqmnt.EquipID, tblServReqmnt.ServiceID...
I would not use tbl as part of a field name either, nor do I use part of a table name to distinguish where a foreign key comes from, but that's just my way. The relationship diagram tells me what's connected to what if I can't remember. If you ever evolve to the point where you have to write sql in code, you will discover the benefits of brevity.

If you're interested in specifications, look here https://support.office.com/en-us/art...9-ea9dff1fa854