find lowest date value from multiple fields & WORK DAYS Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

1)have six dates in a table to facilitate tracking of time it takes to get
from one step to another. Sometimes event does not always progress in a
specific order. Hence, I would like to add an expression to a query that
gives me the date difference between the minimum and maximum date values.
ALSO
2) Is there a "Work Days" function in Access (like Excel) that excludes
holidays and weekends?
 
Thanks Doug - I'll look further into link you provided about Workdays.

I'm aware of the Datediff function. I currently use it to calculate the
difference between [To_contracting] and [Approved_contracting]. Problem is
searching across fields to find the minimum and maximum values from all date
fields to calculate the maximum time for processing.

Based on similar postings to this group, most folks will say this is a
normalization problem. But I'm not sure how I would construct the tables
differently.
 
Doug,

Pardon me for jumping in - but I don't see a followup response on this.

DonD,

You should probably have a table that is linked to your current table. The
table would be something like

ContractActionDates
ContractID
ActionDate
ActionType - Values such as (Sent To; Approved; etc) or better, in my opinion)
an additional table that would contain the Types of contracting actions.

ContractActionTypes
ActionID
ActionDescription

If you must live with your current design, one method that would work would be
to use a normalizing union query.

SELECT ContractID, To_Contracting as AllDates
FROM YourTable
UNION ALL
SELECT ContractID, Approved_Contracting
FROM YourTable
SELECT ContractID, ...

Save that as your source query - qNormalledDates

Then you can do the following totals query

SELECT q.ContractID,
Max(q.AllDates) as Latest,
Min(q.AllDates) as Earliest,
DateDiff("D",Min(q.AllDates),Max(q.AllDates)) as Elapsed
FROM qNormalledDates
GROUP BY q.ContractID


Another method is to use a function to get the Max and Min dates from the fields
in the row. Something like this old one I threw together. You should be able
to modify this to get a minimum across your rows.

'------------- Code Starts --------------
Public Function fGetMaxNumber(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the Maximum Number of a group of values passed to it.
'Sample call: myMax = GetMaxNumber("-21","TEST","2", "3",4,5,6,"7",0) returns 7
'Ignores values that cannot be treated as numbers.

Dim i As Integer, vMax As Variant, tfFound As Boolean, dblCompare As Double

vMax = -1E+308 'very large negative number
For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then
dblCompare = CDbl(Values(i))
If dblCompare > vMax Then
vMax = dblCompare
tfFound = True
End If
End If
Next

If tfFound Then
fGetMaxNumber = vMax
Else
fGetMaxNumber = Null
End If

End Function


Thanks Doug - I'll look further into link you provided about Workdays.

I'm aware of the Datediff function. I currently use it to calculate the
difference between [To_contracting] and [Approved_contracting]. Problem is
searching across fields to find the minimum and maximum values from all date
fields to calculate the maximum time for processing.

Based on similar postings to this group, most folks will say this is a
normalization problem. But I'm not sure how I would construct the tables
differently.

Douglas J. Steele said:
1) There's a DateDiff function. Check the help file.

2) Not built into Access, but check
http://www.mvps.org/access/datetime/date0012.htm at "The Access Web" for one
approach.
Click to expand...
Click to expand...
 
John - thanks a bunch. I was out for a couple of days, but finally saw your
reply. I'll add the table as you suggest. I got myself into a logic box and
couldn't envision that table for some reason.

Don

John Spencer (MVP) said:
Doug,

Pardon me for jumping in - but I don't see a followup response on this.

DonD,

You should probably have a table that is linked to your current table. The
table would be something like

ContractActionDates
ContractID
ActionDate
ActionType - Values such as (Sent To; Approved; etc) or better, in my opinion)
an additional table that would contain the Types of contracting actions.

ContractActionTypes
ActionID
ActionDescription

If you must live with your current design, one method that would work would be
to use a normalizing union query.

SELECT ContractID, To_Contracting as AllDates
FROM YourTable
UNION ALL
SELECT ContractID, Approved_Contracting
FROM YourTable
SELECT ContractID, ...

Save that as your source query - qNormalledDates

Then you can do the following totals query

SELECT q.ContractID,
Max(q.AllDates) as Latest,
Min(q.AllDates) as Earliest,
DateDiff("D",Min(q.AllDates),Max(q.AllDates)) as Elapsed
FROM qNormalledDates
GROUP BY q.ContractID


Another method is to use a function to get the Max and Min dates from the fields
in the row. Something like this old one I threw together. You should be able
to modify this to get a minimum across your rows.

'------------- Code Starts --------------
Public Function fGetMaxNumber(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the Maximum Number of a group of values passed to it.
'Sample call: myMax = GetMaxNumber("-21","TEST","2", "3",4,5,6,"7",0) returns 7
'Ignores values that cannot be treated as numbers.

Dim i As Integer, vMax As Variant, tfFound As Boolean, dblCompare As Double

vMax = -1E+308 'very large negative number
For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then
dblCompare = CDbl(Values(i))
If dblCompare > vMax Then
vMax = dblCompare
tfFound = True
End If
End If
Next

If tfFound Then
fGetMaxNumber = vMax
Else
fGetMaxNumber = Null
End If

End Function


Thanks Doug - I'll look further into link you provided about Workdays.

I'm aware of the Datediff function. I currently use it to calculate the
difference between [To_contracting] and [Approved_contracting]. Problem is
searching across fields to find the minimum and maximum values from all date
fields to calculate the maximum time for processing.

Based on similar postings to this group, most folks will say this is a
normalization problem. But I'm not sure how I would construct the tables
differently.

Douglas J. Steele said:
1) There's a DateDiff function. Check the help file.

2) Not built into Access, but check
http://www.mvps.org/access/datetime/date0012.htm at "The Access Web" for one
approach.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



1)have six dates in a table to facilitate tracking of time it takes to get
from one step to another. Sometimes event does not always progress in a
specific order. Hence, I would like to add an expression to a query that
gives me the date difference between the minimum and maximum date values.
ALSO
2) Is there a "Work Days" function in Access (like Excel) that excludes
holidays and weekends?
Click to expand...
Click to expand...
Click to expand...
 
John - I'm back working on my database after a short hiatus.

I added the two tables as you suggested in your posting.

Question is: now that each action is seperate record how do I calculate date
differences within a contract. For example the time (in days) elapsed between
when action actionID=1 vs actionID=2 2vs3 etc? I tried a grouping query to
bring each date into a single row for the contract but could not geet it to
work.

John Spencer (MVP) said:
Doug,

Pardon me for jumping in - but I don't see a followup response on this.

DonD,

You should probably have a table that is linked to your current table. The
table would be something like

ContractActionDates
ContractID
ActionDate
ActionType - Values such as (Sent To; Approved; etc) or better, in my opinion)
an additional table that would contain the Types of contracting actions.

ContractActionTypes
ActionID
ActionDescription

If you must live with your current design, one method that would work would be
to use a normalizing union query.

SELECT ContractID, To_Contracting as AllDates
FROM YourTable
UNION ALL
SELECT ContractID, Approved_Contracting
FROM YourTable
SELECT ContractID, ...

Save that as your source query - qNormalledDates

Then you can do the following totals query

SELECT q.ContractID,
Max(q.AllDates) as Latest,
Min(q.AllDates) as Earliest,
DateDiff("D",Min(q.AllDates),Max(q.AllDates)) as Elapsed
FROM qNormalledDates
GROUP BY q.ContractID


Another method is to use a function to get the Max and Min dates from the fields
in the row. Something like this old one I threw together. You should be able
to modify this to get a minimum across your rows.

'------------- Code Starts --------------
Public Function fGetMaxNumber(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the Maximum Number of a group of values passed to it.
'Sample call: myMax = GetMaxNumber("-21","TEST","2", "3",4,5,6,"7",0) returns 7
'Ignores values that cannot be treated as numbers.

Dim i As Integer, vMax As Variant, tfFound As Boolean, dblCompare As Double

vMax = -1E+308 'very large negative number
For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then
dblCompare = CDbl(Values(i))
If dblCompare > vMax Then
vMax = dblCompare
tfFound = True
End If
End If
Next

If tfFound Then
fGetMaxNumber = vMax
Else
fGetMaxNumber = Null
End If

End Function


Thanks Doug - I'll look further into link you provided about Workdays.

I'm aware of the Datediff function. I currently use it to calculate the
difference between [To_contracting] and [Approved_contracting]. Problem is
searching across fields to find the minimum and maximum values from all date
fields to calculate the maximum time for processing.

Based on similar postings to this group, most folks will say this is a
normalization problem. But I'm not sure how I would construct the tables
differently.

Douglas J. Steele said:
1) There's a DateDiff function. Check the help file.

2) Not built into Access, but check
http://www.mvps.org/access/datetime/date0012.htm at "The Access Web" for one
approach.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



1)have six dates in a table to facilitate tracking of time it takes to get
from one step to another. Sometimes event does not always progress in a
specific order. Hence, I would like to add an expression to a query that
gives me the date difference between the minimum and maximum date values.
ALSO
2) Is there a "Work Days" function in Access (like Excel) that excludes
holidays and weekends?
Click to expand...
Click to expand...
Click to expand...
 
WARNING: UNTESTED SQL statements follow.

To get the total elapsed time from start to finish
SELECT ContractID,
Max(ActionDate) as MaxDate,
Min(ActionDate) As MinDate,
DateDiff("d",MinDate,MaxDate)
FROM ContractActionDates
GROUP BY ContractID

If you need to limit this to specific Actions you can add a where clause.
SELECT ContractID,
Max(ActionDate) as MaxDate,
Min(ActionDate) As MinDate,
DateDiff("d",MinDate,MaxDate)
FROM ContractActionDates
WHERE ActionType In (1,2)
GROUP BY ContractID

If you need to get the differences between consecutive actions that can also be done.
ActionA - ActionB 2 Days
ActionB - ActionC 3 Days
ActionC (Last Action - therefore No Days)

It is a bit more complex, but it can be done.
John - I'm back working on my database after a short hiatus.

I added the two tables as you suggested in your posting.

Question is: now that each action is seperate record how do I calculate date
differences within a contract. For example the time (in days) elapsed between
when action actionID=1 vs actionID=2 2vs3 etc? I tried a grouping query to
bring each date into a single row for the contract but could not geet it to
work.

John Spencer (MVP) said:
Doug,

Pardon me for jumping in - but I don't see a followup response on this.

DonD,

You should probably have a table that is linked to your current table. The
table would be something like

ContractActionDates
ContractID
ActionDate
ActionType - Values such as (Sent To; Approved; etc) or better, in my opinion)
an additional table that would contain the Types of contracting actions.

ContractActionTypes
ActionID
ActionDescription

If you must live with your current design, one method that would work would be
to use a normalizing union query.

SELECT ContractID, To_Contracting as AllDates
FROM YourTable
UNION ALL
SELECT ContractID, Approved_Contracting
FROM YourTable
SELECT ContractID, ...

Save that as your source query - qNormalledDates

Then you can do the following totals query

SELECT q.ContractID,
Max(q.AllDates) as Latest,
Min(q.AllDates) as Earliest,
DateDiff("D",Min(q.AllDates),Max(q.AllDates)) as Elapsed
FROM qNormalledDates
GROUP BY q.ContractID


Another method is to use a function to get the Max and Min dates from the fields
in the row. Something like this old one I threw together. You should be able
to modify this to get a minimum across your rows.

'------------- Code Starts --------------
Public Function fGetMaxNumber(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the Maximum Number of a group of values passed to it.
'Sample call: myMax = GetMaxNumber("-21","TEST","2", "3",4,5,6,"7",0) returns 7
'Ignores values that cannot be treated as numbers.

Dim i As Integer, vMax As Variant, tfFound As Boolean, dblCompare As Double

vMax = -1E+308 'very large negative number
For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then
dblCompare = CDbl(Values(i))
If dblCompare > vMax Then
vMax = dblCompare
tfFound = True
End If
End If
Next

If tfFound Then
fGetMaxNumber = vMax
Else
fGetMaxNumber = Null
End If

End Function


Thanks Doug - I'll look further into link you provided about Workdays.

I'm aware of the Datediff function. I currently use it to calculate the
difference between [To_contracting] and [Approved_contracting]. Problem is
searching across fields to find the minimum and maximum values from all date
fields to calculate the maximum time for processing.

Based on similar postings to this group, most folks will say this is a
normalization problem. But I'm not sure how I would construct the tables
differently.

:

1) There's a DateDiff function. Check the help file.

2) Not built into Access, but check
http://www.mvps.org/access/datetime/date0012.htm at "The Access Web" for one
approach.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



1)have six dates in a table to facilitate tracking of time it takes to get
from one step to another. Sometimes event does not always progress in a
specific order. Hence, I would like to add an expression to a query that
gives me the date difference between the minimum and maximum date values.
ALSO
2) Is there a "Work Days" function in Access (like Excel) that excludes
holidays and weekends?
Click to expand...
Click to expand...
Click to expand...
 
John -
Many thanks. Your "untested SQL" works perfectly. I even have it using the
WorkingDays function (rather than datediff).

One last question (I think). I can the create numerous individual SQL
queries to get the differences I need (by changing the Where clause).
However, this seems somewhat cumbersome from a query management standpoint. I
already have more queries than I can keep track of. Do most developers use
"groups" to keep track of their queries? For example, Would you create a
"Dates" group and move all of the date difference calculation queries to that
group? Just curious. I've looked into Groups a little but not too much.

Thank you very much for your help and patience. Don

John Spencer (MVP) said:
WARNING: UNTESTED SQL statements follow.

To get the total elapsed time from start to finish
SELECT ContractID,
Max(ActionDate) as MaxDate,
Min(ActionDate) As MinDate,
DateDiff("d",MinDate,MaxDate)
FROM ContractActionDates
GROUP BY ContractID

If you need to limit this to specific Actions you can add a where clause.
SELECT ContractID,
Max(ActionDate) as MaxDate,
Min(ActionDate) As MinDate,
DateDiff("d",MinDate,MaxDate)
FROM ContractActionDates
WHERE ActionType In (1,2)
GROUP BY ContractID

If you need to get the differences between consecutive actions that can also be done.
ActionA - ActionB 2 Days
ActionB - ActionC 3 Days
ActionC (Last Action - therefore No Days)

It is a bit more complex, but it can be done.
John - I'm back working on my database after a short hiatus.

I added the two tables as you suggested in your posting.

Question is: now that each action is seperate record how do I calculate date
differences within a contract. For example the time (in days) elapsed between
when action actionID=1 vs actionID=2 2vs3 etc? I tried a grouping query to
bring each date into a single row for the contract but could not geet it to
work.

John Spencer (MVP) said:
Doug,

Pardon me for jumping in - but I don't see a followup response on this.

DonD,

You should probably have a table that is linked to your current table. The
table would be something like

ContractActionDates
ContractID
ActionDate
ActionType - Values such as (Sent To; Approved; etc) or better, in my opinion)
an additional table that would contain the Types of contracting actions.

ContractActionTypes
ActionID
ActionDescription

If you must live with your current design, one method that would work would be
to use a normalizing union query.

SELECT ContractID, To_Contracting as AllDates
FROM YourTable
UNION ALL
SELECT ContractID, Approved_Contracting
FROM YourTable
SELECT ContractID, ...

Save that as your source query - qNormalledDates

Then you can do the following totals query

SELECT q.ContractID,
Max(q.AllDates) as Latest,
Min(q.AllDates) as Earliest,
DateDiff("D",Min(q.AllDates),Max(q.AllDates)) as Elapsed
FROM qNormalledDates
GROUP BY q.ContractID


Another method is to use a function to get the Max and Min dates from the fields
in the row. Something like this old one I threw together. You should be able
to modify this to get a minimum across your rows.

'------------- Code Starts --------------
Public Function fGetMaxNumber(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the Maximum Number of a group of values passed to it.
'Sample call: myMax = GetMaxNumber("-21","TEST","2", "3",4,5,6,"7",0) returns 7
'Ignores values that cannot be treated as numbers.

Dim i As Integer, vMax As Variant, tfFound As Boolean, dblCompare As Double

vMax = -1E+308 'very large negative number
For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then
dblCompare = CDbl(Values(i))
If dblCompare > vMax Then
vMax = dblCompare
tfFound = True
End If
End If
Next

If tfFound Then
fGetMaxNumber = vMax
Else
fGetMaxNumber = Null
End If

End Function



DonD wrote:

Thanks Doug - I'll look further into link you provided about Workdays.

I'm aware of the Datediff function. I currently use it to calculate the
difference between [To_contracting] and [Approved_contracting]. Problem is
searching across fields to find the minimum and maximum values from all date
fields to calculate the maximum time for processing.

Based on similar postings to this group, most folks will say this is a
normalization problem. But I'm not sure how I would construct the tables
differently.

:

1) There's a DateDiff function. Check the help file.

2) Not built into Access, but check
http://www.mvps.org/access/datetime/date0012.htm at "The Access Web" for one
approach.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



1)have six dates in a table to facilitate tracking of time it takes to get
from one step to another. Sometimes event does not always progress in a
specific order. Hence, I would like to add an expression to a query that
gives me the date difference between the minimum and maximum date values.
ALSO
2) Is there a "Work Days" function in Access (like Excel) that excludes
holidays and weekends?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top