Automated emails based on date

[JeremyR23]

I am creating a database on using serial numbers on inventory. We receive back certain defective inventory and manually record those dates. I want a automatic reminder email to be sent to the repair people when inventory has been in repair depot for 20 days. I would imagine in one field I put in date received, in another put in a function that putsn the current time when the database is opened, and another field that subtracts the two dates and has some kind of criteria for an email to be sent when that field= 20. (that field would be DaysInDepot).Can someone help me figure out how to accomplish this. Thanks in advance.

 

[Minty]

Create a simple query initially that gathers the records you want. Something like (I've assumed a lot of field names here but your should have them in your main data table)

SELECT CustomerID, JobNumber, SerialNumber, ReceivedDate 
FROM YourDetailsTable 
WHERE ReceivedDate < DATE()-20 AND CompletedDate Is Null

You don't need and don't want a field in your table called days in Depot. That can and always should be calculated when you need to see it.

 

[JeremyR23]

Could I still format a new calculated field for total depot days in that Querie? Also I tried running that Querie you instructed but I am new to code and got a error message for having characters at the end of my SQL statement. This is what I had typed(I also tried replacing -20 to =20):

SELECT [AH SI Depot].[S/N], [AH SI Depot].[Rec'd]
FROM [AH SI Depot];
WHERE [Rec'd] < DATE()-20 AND [Date Completed] Is Null

 

[Minty]

You will need to rename your Rec'd date field - the ' character will completely mess you up in about 1 or 2 days time. I'm amazed access let you use it! It will give you no end of problems.

In fact before you get too far and stuck into things, go into your tables and remove all the non alphanumeric characters and spaces form your fields names and table names. Also make sure you don't rename them to reserved names - eg Date , Time , Number , etc etc . There is a list here of things to avoid http://allenbrowne.com/AppIssueBadWord.html - I would get into using a naming convention as well as early as possible - see here for the why and where http://www.access-programmers.co.uk/forums/showthread.php?t=225837

Back to your query - the ; is messing you up

SELECT [AH SI Depot].[S/N], [AH SI Depot].[Rec'd] 
FROM [AH SI Depot] 
WHERE ([Rec'd] < DATE()-20 ) AND [Date Completed] Is Null

 

[JeremyR23]

You are the Man! I also changed all my field names so they follow your advice. That query worked so now I see all my SerialNum values in that field, and the date I received them in my other field. How would I show calculate a third field to find the total days, and even more complex send a automated email when a value in that column reaches 20

 

[Minty]

Baby Steps... Calculated field is pretty straight forward. In the query designer put the following in the top line of the next blank field folder.

DaysOnSite: DateDiff("d",[YourRecievedDate], Date())

The full syntax and explanation of the DateDiff function is here - as is a full reference of other functions http://www.techonthenet.com/access/functions/date/datediff.php

The email is a much more interesting and complex step. At this point I would be tempted to create a simple report based on your query. Once you have that correct we can explore the various ways of doing things with it.

 

[JeremyR23]

Thank you again. I completed all the fields I wanted in my query and am ready to explore the various ways of email

 

[Minty]

There are actually an number of things you could do to think about here, and hopefully they are relevant as I have worked in the repair service industry for 30 + years.
You are effectively making a Overdue WIP (Work in progress) list at the moment that is only showing you units that are over 20 days old. Your intention is probably to email this to various parties or a workshop foreman / team leader, on a daily basis.

Why not give them a form on the database that lists the WIP in reverse order that is oldest (Most outstanding) at the top. They can see immediately the live data. If something is progressed out of WIP it will automatically disappear.

If your work load is broken down into categories your form could be set up to filter down to those categories. So if you had different repair lines within your workshop you could set up a form filtered just to that lines work run.

This has a number of benefits over an email. Firstly it's live, not out of date the moment its run. Secondly it won't get printed off and clog up a desk. Thirdly it will impress your customers when they come round and see your current work listings updating magically in front oh their eyes.

Finally - you don't need to try and automate sending the email. That relies on either having a machine left on with a scheduled task running or if you have SQL Server backend database it can be easily accomplished on the server.

However if you do desperately need to email it we can help.

 

[JeremyR23]

I have duplicated Serial Numbers in a Repairs table along with dates received. I want to only pull info from a certain field( Field Diagnostic) in that repairs table where the received date is the same as the date I have in another AH Depot table. What relationship or joint properties would I have to make so it only pulls the FieldDiagnostic where that date is the same, instead of the query creating two duplicate records with the same data except in the FieldDiagnostic column.

 

[JeremyR23]

Current SQL view. I already have them linked with serial numbers from Depot Contract Report but want to link the dates from AH Supply Depot to dates in Repair table, and only show field diagnostic from those similar dates

SELECT [AH SI Depot].SerialNum, Assets.Unit, [AH SI Depot].Received, Assets.Status, Repairs.FieldDiagnostic
FROM (([Depot Contract Status] LEFT JOIN [AH SI Depot] ON [Depot Contract Status].[SerialNum] = [AH SI Depot].[SerialNum]) LEFT JOIN Assets ON [AH SI Depot].[SerialNum] = Assets.[SerialNum]) LEFT JOIN Repairs ON Assets.[SerialNum] = Repairs.SerialNum
WHERE ((([AH SI Depot].SerialNum) Is Not Null));

 

[Minty]

You can add another join between your two date fields and that would force them to be equal.

 

[JeremyR23]

I get a nambiguous outer joins error. Both data types are the same

 

[Minty]

You may need to force the other joins to be Full Joins rather than left or outer joins. A picture of your query design may help.

 

[JeremyR23]

https://vector.leidos.com/sites/SS/Lists/Announcements/Attachments/4/Q.png

I attached a link to the image.

 

[Minty]

I can't open that link, upload your image to this web site, then post the link it creates as an image.

 

[Minty]

I would remove your email from that last post - you will get spammed beyond belief!

On the reply box press "Go Advanced" and you will see a lot more options

 

[JeremyR23]

Thanks. I attached it

 

[Minty]

Okay One way or another your query joins don't make sense to me.
I don't think you need Depot Contract Status in the query at all as no fields are present, or if you do make the join a full join, and remove the is not null criteria check.
Move the Assets table to the right and remove the joins from it to AH SI Depot, and Repairs.
Add a right join (arrow pointing away from) from Repairs to Assets.

 

[JeremyR23]

My data is really rough because some S/N are the same because the way workers deal with returned parts they just create new records for them with the same S/N. I need to find a better way for the workers to input units that gets returned. I just realized I have no way to really pull what received date is attached to the Depot Contracts anyways. Thanks though because you helped me realize this.

 

[Minty]

The serial number should NOT be your unique record identifier, precisely because of this problem. You need to create a unique ID for each item that you handle (JobNumber, RMANumber, WingWangNumber ), and store all the other information around it. Serial No , Recvd Date, Shipped date etc.

If an item comes back in you can search for the serial number before committing it to the database, and bring up it's previous job number(s) . You then have a repair / maintenance history you can refer to.