Calculate a forward date from a given period

I have created a similar program that tells me when a case is due a follow up. I created a query and put this in the criteria fields:

<Now()-30
<Now()-60
<Now()-90

You could use something similar but use 180 and 360.

It sounds like what you need is a CASE statement. The exact syntax depends on database type.

SELECT      CASE WHEN ServiceType = 'Short Service'
            THEN Add LSPeriod # of Months to Service Date
           ELSE
            THEN Add SSPeriod # of Months to Service Date
      END  AS NextServiceDate

Is that what you're talking about?

Do you hve any machinery where there is are two or more ShortSvcInterval's before the LongSvcInterval ie. a SSPeriod of 2 months and a LSPeriod of 6 months - meaning two SSPeriod inspections before the LSPeriod inspection?

No there is only one SSPeriod which could be every 6  months and one long service say every 12 months

Can you tell us the form name, the controls, the subform names and their controls?  There is more than one table?  

Hi Gary

I am going to work theough the form for you as it contains three nested subforms so i thought I would add the parent child relationships and explain the function of the form so you have as clear a picture as possible

The first subform the user can use to navigate to previous service records because of a hidden txtlink on the mainform or select a new record. when entering a new record the user chooses the nature of the service from the Service Type Combo "Breakdoen","Long Service","Short Service","Stock Adjustment"

FormName: subServiceHistory
 
Controls: UnitID                  Number           txtBox
                ServiceID            AutoNumber    txtBox
                ServiceDate        Date/Time       txtBox
                ServiceType         Txt                 ComboBox

Child:      UnitID
Parent:   UnitID

With the following events:

Private Sub Form_Current()
       On Error Resume Next
         Me.Parent("txtLink") = [ServiceID]
End Sub

Private Sub ServiceType_AfterUpdate()

 Dim lngServiceID As String

lngServiceID = Me.ServiceID
Me.Requery   'scrolls to the left
DoCmd.FindRecord lngServiceID, acEntire, True, acSearchAll, True, acCurrent, True

End Sub


This second sub form takes it's information from the same table as the previous one above.


FormName: subServiceDetails

Controls: UnitID                  Number           txtBox
               ServiceID             AutoNumber    txtBox
               ServiceBy             txt                   ComboBox
               ServiceType         txt                    ComboBox
               CompletionType   txt                   ComboBox
               Progress              txt                    Combobox
               Status                  txt                    Combobox
               NextService         txt                     txtBox

Child:     ServiceID
Parent:   txtlink

The third subform is a small form that shows the long service period and the short service period

FormName:    subServiceIntervals

Controls:        UnitID
                       ServiceID
                       LongServInterval
                       ShortServInterval

Child:          UnitID
Parent:       UnitID



I hope that I haven't over complicated things by giving you to much but hope you have a clear understanding of what I am trying to achieve

What I am hoping is to be able to calculate on the form the next service due based on the last service date and place the calculated date in the control NextService. I would also like to be able to use this dates to flash up a reminders splash form with all the overdue machine servicing.

Hi

Sugestions seem to have dried up. Not much in the way of solutions so I am assuming that I am approaching this question in too complex a way.

Perhapes If I break down what I am trying to do into a reminders page with three sub forms.

I think that if a solution can be found for one then it will form the basis of the others. So starting with:-

Short service dues

'This doesn't work but I'm sure could be made to

SELECT tbl_Machinery_Servicing.*
FROM tbl_Machinery_Servicing
WHERE (((DateSerial(Year([ServiceDate]),Month([ServiceDate])+[ServiceInterval],Day([ServiceDate])))<=Date())) And Status="Completed" And ServiceType="Short Service";

This at least only calculated the date intervals on a "completed" service and only if it was a "Short Service" but as there could be several historical short service records for each UnitID then I need to add a criteria so that it only filters and shows the results from the latest short service date per UnitID.

Hope this sounds more level headed and I much appreciate any help given.

Thanks

MGardner,

I can't help you out with the form code. But as far as the query, it seems like you just need to add an aggregate.

--- If column contains ServiceDate only
SELECT UnitId, MAX(ServiceDate) AS MaxServiceDate
FROM   tbl_Machinery_Servicing
WHERE  ServiceDate <= Date()
AND    Status = 'Completed'
AND    ServiceType = 'Short Service'
GROUP BY UnitId

--- If column contains ServiceDate and appointment time
SELECT UnitId, MAX(ServiceDate) AS MaxServiceDate
FROM   tbl_Machinery_Servicing
WHERE  ServiceDate < DateAdd('d', 1, Date())
AND    Status = 'Completed'
AND    ServiceType = 'Short Service'
GROUP BY UnitId

--------------------------
test data
--------------------------
ServicingId      Status      ServiceType      UnitID      ServiceDate
1      Open      Short Service      UnitA      5/10/2007
2      Completed      Short Service      UnitA      5/9/2007
3      Completed      Short Service      UnitA      12/6/2006
4      Completed      Short Service      UnitA      7/6/2006
5      Completed      Short Service      UnitA      1/15/2006
6      Completed      Short Service      UnitB      5/10/2007
7      Completed      Short Service      UnitB      12/9/2007
8      Completed      Short Service      UnitB      7/24/2006
9      Completed      Short Service      UnitB      1/5/2006
10      Completed      Short Service      UnitB      6/15/2005

--------------------------
results
--------------------------
UnitId      MaxServiceDate
UnitA      5/9/2007
UnitB      5/10/2007

I'm trying to work for  a solution..   I thought  I was finished..  but there's  something  I forgot..  he he he.. so please  wait

wow  so there's  really  an easy way... he hehe..   my  solution  involves about 5 new querries..   should I post  it?

hi I'll try to propose  a solution  if I  understand  correctly.. and this  may be  a long  explanation.. cause I usually used the hard way . so  forgive me

but I have a problem with your  info.. I seem to have  a problem with  the field serviceID
I have 2 thery  behind it
1. it's the primary key for the table tbl_Machinery_Servicing.  - if it is then I think it's really  a bad  idea. so I won't be writing any solution  for it.
2. it's the primary key for a table something like "tblServicing" which  may or  may not be connected to main items  table. maybe  there's a couple of services for all or  each machine type (eg. maintanance, cleaning, calibration, etc..) having  different time intervals..   if it's like this then.. the solution  below  may apply

- I tried recreating your table in my end..  and just copy paste the  querries from SQL view

first off I think you  should  create   a query based on the   first  subform (to get the latest  record)
--------------------------------------------------------------------------------------------------------------------
 - I think  a query like


SELECT [Unit ID],ServiceID, [Service Type], Max([Service Date]) AS Latest
FROM tbl_Machinery_Servicing
WHERE (((StatusType)="Complete"))
GROUP BY [Unit ID], ServiceID, [Service Type];


-  and named it qryLatestServices
- this way only the latest date for all Status Types  will be returned


next is to calculate for the  next service date for both short and long  service type.  
-----------------------------------------------------------------------------------------------------------
- what I did  is I created 2 separate querries for both

-create a query for "Long Service" and named it qryNextLong

SELECT tbl_Machinery_Servicing_Intervals.UnitID, tbl_Machinery_Servicing_Intervals.ServiceID, tbl_Machinery_Servicing_Intervals.LongServInterval, qryLatestServices.[Service Type], qryLatestServices.Latest, DateSerial(Year([Latest]),Month([Latest])+[LongServInterval],Day([Latest])) AS NextLong
FROM tbl_Machinery_Servicing_Intervals INNER JOIN qryLatestServices ON (tbl_Machinery_Servicing_Intervals.UnitID = qryLatestServices.[Unit ID]) AND (tbl_Machinery_Servicing_Intervals.ServiceID = qryLatestServices.ServiceID)
WHERE (((qryLatestServices.[Service Type])="Long Service"));


- and another  query for "Short Service" named qryNextShort

SELECT tbl_Machinery_Servicing_Intervals.UnitID, tbl_Machinery_Servicing_Intervals.ServiceID, tbl_Machinery_Servicing_Intervals.ShortServInterval, qryLatestServices.[Service Type], qryLatestServices.Latest, DateSerial(Year([Latest]),Month([Latest])+[ShortServInterval],Day([Latest])) AS NextShort
FROM tbl_Machinery_Servicing_Intervals INNER JOIN qryLatestServices ON (tbl_Machinery_Servicing_Intervals.ServiceID = qryLatestServices.ServiceID) AND (tbl_Machinery_Servicing_Intervals.UnitID = qryLatestServices.[Unit ID])
WHERE (((qryLatestServices.[Service Type])="Short Service"));


Third is  to create a query for comparing calculated values...  
-----------------------------------------------------------------------------------------------------------
- I created 1 named   qryNextService

SELECT qryNextLong.UnitID, qryNextLong.ServiceID, qryNextLong.NextLong, qryNextShort.NextShort, IIf(Abs(DateDiff("d",[Nextlong],Date()))<Abs(DateDiff("d",[NextShort],Date())),"Long Service","Short Service") AS [Next Service Type], IIf(Abs(DateDiff("d",[Nextlong],Date()))<Abs(DateDiff("d",[NextShort],Date())),[Nextlong],[NextShort]) AS Expr1
FROM qryNextLong INNER JOIN qryNextShort ON (qryNextLong.ServiceID = qryNextShort.ServiceID) AND (qryNextLong.UnitID = qryNextShort.UnitID);


fourth  you  can either  add another subform  based on qryNextService to get the values based on Unit ID
---------------------------------------------------




-------------------------------------------------------
Actually this works  but there is a problem.. when a machine  don't have any records for short and long service it won't be included in a query..  I'm trying  to find a workarround   for this  and it would need more querries to perform those checking

Hi

Many thanks for the responses

agx I have managed to cobble something togeteher with what agx set out as I have included a join to the main machinery table in order to bet the machine description. and I now have the following for the Long Service

SELECT tbl_Machinery_Servicing.UnitID, Last(tbl_Machinery_Servicing.ServiceID) AS [Service ID], Last(tbl_Machinery_Servicing.ServiceDate) AS [Last Service], tbl_Machinery.Make, tbl_Machinery.Model, tbl_Machinery.Description, Last(DateSerial(Year([ServiceDate]),Month([ServiceDate])+[LongServiceInterval],Day([ServiceDate]))) AS [Next Service]
FROM tbl_Machinery_Servicing INNER JOIN tbl_Machinery ON tbl_Machinery_Servicing.UnitID = tbl_Machinery.UnitID
WHERE (((tbl_Machinery_Servicing.ServiceDate)<DateAdd('d',1,Date())) AND ((tbl_Machinery_Servicing.Status)='Completed') AND ((tbl_Machinery_Servicing.ServiceType)='Long Service'))
GROUP BY tbl_Machinery_Servicing.UnitID, tbl_Machinery.Make, tbl_Machinery.Model, tbl_Machinery.Description;

And for the short sevice dues

SELECT tbl_Machinery_Servicing.UnitID, Last(tbl_Machinery_Servicing.ServiceID) AS [Service ID], Last(tbl_Machinery_Servicing.ServiceDate) AS [Last Service], tbl_Machinery.Make, tbl_Machinery.Model, tbl_Machinery.Description, Last(DateSerial(Year([ServiceDate]),Month([ServiceDate])+[ShortServiceInterval],Day([ServiceDate]))) AS [Next Service]
FROM tbl_Machinery_Servicing INNER JOIN tbl_Machinery ON tbl_Machinery_Servicing.UnitID = tbl_Machinery.UnitID
WHERE (((tbl_Machinery_Servicing.ServiceDate)<DateAdd('d',1,Date())) AND ((tbl_Machinery_Servicing.Status)='Completed') AND ((tbl_Machinery_Servicing.ServiceType)='Short Service'))
GROUP BY tbl_Machinery_Servicing.UnitID, tbl_Machinery.Make, tbl_Machinery.Model, tbl_Machinery.Description;


Manthanien I have created the query "qryLatestServices" which pulls the data together ok but cannot get the second or third query to work. I have tried several ways etcetra, Consulted my books, and racked my brains to work it out. after all I am very keen to learn and feel already I have picked somethings up that I didn't know before. But enjoying the learning experiance.

I am interested to know why you think that ServiceID as the primary key for the table tbl_Machinery_Servicing.

I assume that I have wandered into an access naming convention No No?

Hi MGardner,

Could you post the actual structure of the relevant tables, relationships, brief description of the columns, (and possibly a few sample data rows)?  At least these tables:

tbl_Machinery    
tbl_Machinery_Servicing
tbl_Machinery_Servicing_Intervals

I think this would speed the suggestion process along.

well... that's  my first  assumption  actually..   but ehile  working on the  next queries I suddenly thought that I don't have the correct  interpretation of your schemas..   he he he.  so it's  my  fault..
yup  the  succeding queries  are quite  tricky.. It wouldn't  work unless we got the same schema..  maybe  I should  email  my  sample to  you.. I mean  the database  that I did..   but please  be warned  I haven't  perfected  it yet..  it only shows the nearest next  service  date for items with data for  both  short and long type..  but anyways..   I want to send the file to you.. so you can   see the queries  graphically..  the  only  question is how?  

well  I  uploaded it to  sendspace..  
http://www.sendspace.com/file/83vvj8

The mdb contains only two tables and five queries - no form(s)?  tbl_Machinery is missing.  BTW EE has an upload area:    I would reload your zipped mdb there to keep everything under one roof - and note the URL of the uploaded file and paste it back in this thread.

Upload at:  http://www.ee-stuff.com/login.php

Thanks Gary

I'll get onto that this evening when I get in and up load the relevent forms table and queries that I have

I have sort of made use of 'OR in a SQL I tried and managed to get it to include both the Completed, Long and Short Services though it doesn't give the next service due specifically if the next service due would be a short service or a long service.

I'm certainly learning a bit about the use of sql so thanks for stimulation.

Hi GAry, I have uploaded a short version of my Db but now I can locate it on the area you gave the url for. Perhapes I'm being a bit dense or I'm using it wrongly.

1. Zip the mdb
2. Copy the URL from this question
3. Open the upload URL
4. Sign In
5. Select Expert Area
6. Question:  Paste in the URL of this question
7.          File:  Browse to the zipped file
8. Enter a comment - this you must do!
9. Click Upload
10.  Copy the URL of the uploaded file back here.

Finally got it to work. The Db I posted has the Tables and Queries but not the form as the forms are a little heavy and it would have been above the file tranfer limit.

https://filedb.experts-exchange.com/incoming/ee-stuff/3436-New-Microsoft-Access-Applicatio.zip

Zipped your file came to 36k.  You are allowed 4 Meg.  I've seen heavy forms, but that heavy?  Remember you can expect about a 10 to 1 compression of an mdb after zipping.  It is also good to compact and repair before zipping.  Wanna try again?

Here you go. Can you believe I forgot to remove the reference to a picture in one of the forms. The inventory form is navigatable by clicking on the machine make.

https://filedb.experts-exchange.com/incoming/ee-stuff/3452-New-Microsoft-Access-Applicatio.zip

Some observations.  Get rid of the underscores in your table names.  tblMachineryServicing is just as readable as tbl_Machinery_Servicing, and much easier to type.  tbl_Machinery should contain the SSI and LSI values.  It is an atribute of the machine  There is nothing wrong with using abbreviations Like SSI in place of ShortServiceInterval, and again much easier to type and manage on forms and reports.

Maintenance is normally scheduled and unscheduled. So SvcType could be S6 or S12 or U.  I would change the CompletionType field name to Contracted and make it Boolean, which defaults to False. Similarly Status should be renamed Completed, type Boolean, defaulting to False and clicked on a form to make it True or Yes.
 
Progress looks like it is really Parts - In Hand, In Stock. or On Order.  Don't know the difference between In Hand and In Stock and what Returned adds.  Lookup fields are to be avoided in my book.  Go the route of Boolean or at most a simple one or two character ID for the table and use comboboxes on the form to expand the description where necessary.

Assuming SSI and LSI are in tblMachinery, you are looking for a query to update the fields NextService (a Date) and SvcType?  I have to run now but I know how you give you the update query you need for those two fields - provided you change the datatype of NextService to Date.

Remember, no one should be editing data in a table directly.  That should be done only thru forms. Therefore the form is the tool to make user sense of the 'cryptic' data in the table.  Comments?

Thanks for the observations Gary. I take your point re the underscores  and will sort that out. It's late for me now but I'll get back to you tommorrow re the combo boxes tc.

GRayL, not Gary.  I've had a good look at your form and I wonder why you did not choose to use a Tab control with several pages rather than Labels to control your pages - a lot more maintenance prone.  In addition, you did not include tblCurrentUsers, so the first four label presses are not available.  No matter, based on what is currently there, and assuming a breakdown does not include either a Short or Long Service does this query give you what you want?

SELECT UnitID,Max(ServiceDate) AS LastSvcDate, ServiceType AS LastSvcType, DateAdd("m",6,Max(ServiceDate)) AS NextSvcDate, IIF(ServiceType="Short Service","LongService","Short Service") AS NextSvcType  
FROM tbl_Machinery_Servicing WHERE ServiceType IN ("Short Service","Long Service") GROUP BY UnitID, ServiceType;

This assumes things as you uploaded.  Now after the changes I recommended, you will need a join to the tbl_Machinery.  You may also decided to include a Long or a Short Service on a Breakdown as the breakdown may occur within a short time of the next due date.  My rationale covers this.  It would mean having two records in the servicing table, one for the breakdown, and one for the scheduled maintenance activity.

Note, you would not be storing the Next Servicing activities in a table.  They would always be derrrived from the query.  

Hi GRayL

Sorry I havent been on this but pressures of work etc. I tried your query and it works fine apart from it generating two entries per machine if the machine has recieved a long service and a short service. is there a way to get it to only show the duplicate machine with the date nearest to the current date?

I have added the tbl_Machinery to the query so that I could include the make and model of the machine and thus identify the machine that needs the service.

I did look at the tab control that comes with access but thought though it was functional it did nothing to the look of the page so decided the extra effort was worth it.

tblCurrentUser is a login table that is refered to filter some of the forms to those of the user.

My point was only to say  use the app functions until you get what you need.  Then go to making it look 'smarter'.  Starting with the most complex is doomed.  I can help you with 'boilerplate', but beyond that, you have stuff that may not work - and we may not be able to help you.

Accept your point about app functions and that is how I started out with the project - that seems to just be growing. As I develop and play around with it I learn new things. elighten me on the term 'Boilerplate' I've heard it a couple of times in the past but attached no significant meaning to it before.

but beyond that, you have stuff that may not work - and we may not be able to help you. (Is not everything that I have passed over standard Access)?

"Boilerplate" in this sense is the 'standard' way of creating a multi-paged form using the 'normal' controls and methods.  It took a while to figure out that clicking a label, (then going to the code behind the onclick event) was opening another form.  So where are we at now?  My last query uses only tbl_Machinery_Servicing and provides the next servicing date and servicing type for each piece of machinery with a service interval.  I think that is what you asked for.  You've included tbl_Machinery in a JOIN to identify the machine more properly.

That is true.... in a way?

"I am trying to find a solution to devising a query that will show the next service date and the type of service is required"?

However in asking the question above I was hoping that for the query to show the next service date and the type of service with the machine shown as a single entry

Thus it should show the following

Dennis G760   08/07/2007 Short Service

but not the second

Dennis G760   08/07/2007 Short Service
Dennis G760   11/07/2007 Long Service


SELECT tbl_Machinery_Servicing.UnitID, tbl_Machinery.Make, tbl_Machinery.Model, Max(tbl_Machinery_Servicing.ServiceDate) AS LastSvcDate, tbl_Machinery_Servicing.ServiceType AS LastSvcType, DateAdd("m",6,Max(ServiceDate)) AS NextSvcDate, IIf(ServiceType="Short Service","LongService","Short Service") AS NextSvcType
FROM tbl_Machinery_Servicing INNER JOIN tbl_Machinery ON tbl_Machinery_Servicing.UnitID = tbl_Machinery.UnitID
WHERE (((tbl_Machinery_Servicing.ServiceType) In ("Short Service","Long Service")))
GROUP BY tbl_Machinery_Servicing.UnitID, tbl_Machinery.Make, tbl_Machinery.Model, tbl_Machinery_Servicing.ServiceType;

is where I am now. Are you sugesting that the question is too complex now for the points or, that you feel that the question is answered and the original question is two questions, or do you think that the question is sufficiently complex to require extra points?

Or that no extra help is devisable?

MGardner,

I had an idea for a query to give you the next service information. So I downloaded the database. I thought the service interval information came from a table named "tbl_Machinery_Servicing_Intervals" but I don't see it in the database.  Can you post the structure of that table?

Thanks agx

yes I had changed it and included it in the service table tbl_Machinery_Servicing but was thinking earlier that GRayL advice on placing the service intervals in with tbl_Machinery was most sound. It is still in the machine service table though

However it this makes a difference to you then the structure of the service interval table id

ServiceIntervalID      Autonumber
UnitID                       Number
ShortServInterval     Number
LongServInterval      Number

with some machines that only require one service per year I would normally entery the value of 12 (Months) in the long service interval and 0 (Months) in the short service interval

> but was thinking earlier that GRayL advice on placing the service intervals in
> with tbl_Machinery was most sound.
Okay,  I missed that. But I agree with GRayL. It does not belong in the tbl_Machinery_Servicing table.  

> with some machines that only require one service per year I would normally entery the value of
> 12 (Months) in the long service interval and 0 (Months) in the short service interval
I'll try an example with the service dates in "tbl_Machinery"

Thanks agx I appreciate your help

Hi GRayL

Your solution work straight away thank you very much. I much appreciate your help and guidance.

agx I'm sure your solution works but I haven't had time to sort out all the changes into my machinery table as yet
I'd like to split the points as I know you both worked hard to but as GRayL has the solution I will use I'd like to split split the points 200 + the 500 to GRayL if you reply to the original question

https://www.experts-exchange.com/questions/22543695/Calculate-a-forward-date-from-a-given-period.html I'll award you the points for that and refer it to here

and 300 to agx if thats ok

regards

Mal

MGardner,

However you want to split the points is fine with me.  I'm glad you've got a working query now :)

Yadda;-)

GRayL

please make a comment on the question link above so I can award you the extra points

With Thanks