Append or Update Query with Date Add?

[Guest]

Hey there,
I am currently using an append query based on an equipment table to add
orders to a Service log based on date- I basically enter the date that I am
searching for in the query, and it adds all of the information (ServiceDate,
Serial No., etc) into my Service Log. My dilemma is that I need to be able to
update the dates in the equipment table based on a frequency ( which is a
field in the table) automatically after the equipment has been serviced. I'm
currently doing this manually- if the frequency is annual, then I type in the
date a year from now into the NextServiceDate field. I know I can use a
DateAdd function, but I don't know how to accomodate for different
frequencies (annual, semi-annual, bimonthly, etc. ) in the query criterion.
Am I even using the right kind of query for this purpose? And, what kind of
expression can I use to change the dates on specific frequencies? I don't
know what to put in the Frequency field or the NextServiceDate field, or if
it will automatically update the LastServiceDate field. Any help is greatly
appreciated.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Generally, it is not acceptable practice to put a calculated value in a
table. The calculated value is shown only during a SELECT query (i.e.,
an info query - "what's going on"). The reason is that the cost in
maintenance is usually too high. E.g.: what if your frequencies changed
to something new? You would have to update the whole table to get the
correct NextServiceDate. If you just had one view/query you just have
to change the calculation formula in the view/query. I've made
applications where I had a table set up to hold calculation parameters.
When the parameters changed all the users had to do was change the
parameters table & the queries would work correctly.

Your calculation would use the DateAdd.

Instead of using Yearly, Semi-Yearly, Monthly, etc. as the frequency you
should use the number of days between servicing. It makes it easier.

NextServiceDate: DateAdd("d", Frequency, ServiceDate)
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkSAnIechKqOuFEgEQL8owCgtSZkqDyDqEeVlDK2OyICNuihWl4AnAhs
Qq4oBnLe4lHntynfpOdRXP2B
=EXJR
-----END PGP SIGNATURE-----

 

[Guest]

Let me see if I got this:
Remove the NextServiceDate field from my equipment table, then make a table
basically with only calculations for each frequency(in days) and when a
change is needed, change it in the table? Thank you very much for your help-
I will try some rearranging- I didn't really think of NextServiceDate as a
calculated field, but I guess it would be. One more thing, Is the way I'm
approaching the service log appropriate? I mean, by using an append query
there. Thank you again!

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm not sure what you mean by "using an append query" on the service
log. It would depend on your db design. If it were me I'd have an
equipment table related to a service log table. Whenever a service was
done to a piece of equipment I'd update the service log. That usually
takes an append query, or someone entering the data into a form/subform,
which appends the data to the service log (the subform).

If you want to use Annual, Semi-annual, Monthly, etc. for your service
frequency you could use a VBA function to calculate the next service
date. I'd have a table that had Frequency types:

FrequencyTypes
FreqID - AutoNumber
Type - Text - "Annual", "Semi_Annual", etc.

Then the equipment table would have the Freq Type:

Equipment
EquipmentID - AutoNumber
EquipmentName - Text
FreqID - link to table FrequencyTypes
... other columns ...

Then a service log table:

EqpSvcLog
EquipmentID - link to Equipment table
LastServiceDate - last time the equip was serviced
... other columns ...

Then the function to figure out the next service date. The disadvantage
of this function is every time you have a change in the frequencies
you'd have to change this function & deploy the changed user interface
to all the users. A table can be created that does the same thing, but
I don't have the time to demonstrate its design.

Public Function NextSvcDate(lngEquipID As Long, _
dteLastServiced As Date) As Date

Dim lngFreqID As Long
Dim strFreqType As String

' Faster search would use a query & a recordset to get the FreqID
lngFreqID = DLookup("FreqID", "Equipment", "EquipmentID=" & lngEquip)

' Since the FrequencyTypes table is so small DLookup is OK
strFreqType = DLookup("Type","FrequencyTypes","FreqID=" & lngFreqID)

Select Case strFreqType
Case "Annual"
NextSvcDate = DateAdd("yyyy", 1, dteLastServiced)
Case "Semi-Annual"
NextSvcDate = DateAdd("m", 6, dteLastServiced)
Case "Bimonthly" ' every 2 months?
NextSvcDate = DateAdd("m", 2, dteLastServiced)
' ... etc. ...
End Select

End Function

Then a query to find the next service date:

PARAMETERS [After what date? (m/d/yy)] Date;
SELECT E.EquipmentName, NextSvcDate(E.EquipmentID, S.LastServiceDate)
FROM Equipment As E INNER JOIN EqpSvcLog As S
ON E.EquipmentID = S.EquipmentID
WHERE S.LastServiceDate >= [After what date? (m/d/yy)]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkhYzIechKqOuFEgEQJAMQCePznO1qV2TkIyjinMsfm505db1coAnRAj
UqqRhWWQqleGMfDIT7VTHRDP
=QaI3
-----END PGP SIGNATURE-----

 

[Guest]

Oh wow, you are really generous. Thank you so much for your help. I didn't
realize that this kind of operation would be so complicated. You rock. I'll
send you some free mp-3's of Dissent bands for all your help.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm not sure what you mean by "using an append query" on the service
log. It would depend on your db design. If it were me I'd have an
equipment table related to a service log table. Whenever a service was
done to a piece of equipment I'd update the service log. That usually
takes an append query, or someone entering the data into a form/subform,
which appends the data to the service log (the subform).

If you want to use Annual, Semi-annual, Monthly, etc. for your service
frequency you could use a VBA function to calculate the next service
date. I'd have a table that had Frequency types:

FrequencyTypes
FreqID - AutoNumber
Type - Text - "Annual", "Semi_Annual", etc.

Then the equipment table would have the Freq Type:

Equipment
EquipmentID - AutoNumber
EquipmentName - Text
FreqID - link to table FrequencyTypes
... other columns ...

Then a service log table:

EqpSvcLog
EquipmentID - link to Equipment table
LastServiceDate - last time the equip was serviced
... other columns ...

Then the function to figure out the next service date. The disadvantage
of this function is every time you have a change in the frequencies
you'd have to change this function & deploy the changed user interface
to all the users. A table can be created that does the same thing, but
I don't have the time to demonstrate its design.

Public Function NextSvcDate(lngEquipID As Long, _
dteLastServiced As Date) As Date

Dim lngFreqID As Long
Dim strFreqType As String

' Faster search would use a query & a recordset to get the FreqID
lngFreqID = DLookup("FreqID", "Equipment", "EquipmentID=" & lngEquip)

' Since the FrequencyTypes table is so small DLookup is OK
strFreqType = DLookup("Type","FrequencyTypes","FreqID=" & lngFreqID)

Select Case strFreqType
Case "Annual"
NextSvcDate = DateAdd("yyyy", 1, dteLastServiced)
Case "Semi-Annual"
NextSvcDate = DateAdd("m", 6, dteLastServiced)
Case "Bimonthly" ' every 2 months?
NextSvcDate = DateAdd("m", 2, dteLastServiced)
' ... etc. ...
End Select

End Function

Then a query to find the next service date:

PARAMETERS [After what date? (m/d/yy)] Date;
SELECT E.EquipmentName, NextSvcDate(E.EquipmentID, S.LastServiceDate)
FROM Equipment As E INNER JOIN EqpSvcLog As S
ON E.EquipmentID = S.EquipmentID
WHERE S.LastServiceDate >= [After what date? (m/d/yy)]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkhYzIechKqOuFEgEQJAMQCePznO1qV2TkIyjinMsfm505db1coAnRAj
UqqRhWWQqleGMfDIT7VTHRDP
=QaI3
-----END PGP SIGNATURE-----

Let me see if I got this:
Remove the NextServiceDate field from my equipment table, then make a table
basically with only calculations for each frequency(in days) and when a
change is needed, change it in the table? Thank you very much for your help-
I will try some rearranging- I didn't really think of NextServiceDate as a
calculated field, but I guess it would be. One more thing, Is the way I'm
approaching the service log appropriate? I mean, by using an append query
there. Thank you again!

: