Max not working well with Dates..... (1 Viewer)

[TH1]

Dear All,
I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)


I have a table "Assessment tracker" with the following structure
Name Type
Candidate short text
Unit short text
EV1 Date Date
EV2 Date Date

My Data:
Candidate Unit EV1Date EV2 Date
TH1 10 07/05/2015 25/05/15
TH1 10 07/05/2015 07/06/15

I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.
Query:
PARAMETERS [Candidate Name] Value;
SELECT [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date], Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) AS Achdate
FROM UnitData INNER JOIN [Assessment Tracker] ON UnitData.Unit = [Assessment Tracker].Unit
WHERE ((([Assessment Tracker].Candidate) Like "*" & [Candidate Name] & "*"))
GROUP BY [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date]
ORDER BY [Assessment Tracker].Candidate, Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) DESC;


Output:
Candidate Unit EV1 Date EV2 Date Achdate
TH1 10 07/05/2015 25/05/2015 25/05/2015
TH1 10 07/05/2015 07/06/2015 07/06/2015

It does this by using a function shamelessly copied from the web somewhere...

Function Maxdate(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Date


' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)

' Cycle through each value from the row to find the largest.

For I = 0 To UBound(FieldArray)
If FieldArray(I) > currentVal Then
currentVal = FieldArray(I)
End If
Next I

' Return the maximum value found.
Maxdate = currentVal

End Function

This is working well (I think)

I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.

Query:
SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate
FROM [Candidate AC Dates]
GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit
ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;

But this is returning
Candidate Unit MaxOfAchdate
TH1 10 25/05/2015


I expect it to return
Candidate Unit MaxOfAchdate
TH1 10 07/06/2015


It looks to me like MAX is considering only the day value rather than the whole date.
I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)

Any thoughts and suggestions are welcome.

Regards
Tony (5 days into Access.....)

 

[vbaInet]

If the two date columns will never be Null (i.e. empty) in any record, then you can add this part to the code:

Function Maxdate(ParamArray FieldArray() As Variant)[B][COLOR="Blue"] As Date[/COLOR][/B]

 

[TH1]

Thanks for the swift response.

That has sorted it for records where there are dates in both fields.
SO it implies that it is type related.
Unfortunately it is possible that EV2 Date will be blank...

I've changed the code in the function to populate values where it is null.

Function Maxdate(ParamArray FieldArray() As Variant) As Date
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Date


' Cycle through each value from the row to find the largest.

For I = 0 To UBound(FieldArray)
If IsNull(FieldArray(I)) Then
FieldArray(I) = "1900-01-01 00:00:00"
End If

If FieldArray(I) > currentVal Then
currentVal = FieldArray(I)

End If

Next I

' Return the maximum value found.
Maxdate = currentVal

End Function

Works now.

Thanks again.

 

[Galaxiom]

Everything you are doing stems from incorrect structure and becomes evident as soon as you start comparing those two fields.

In a relational structure the two fields should be one in a related table with another field to indicate whether they are EV1 or EV2.

 

[vbaInet]

Good to hear you've got it sorted.

(there are business reasons why there are 2 dates per row they represent different but comparable activities)

And I agree with Galaxiom, I did notice the structural problem but didn't feel it was necessary to highlight it because of your comment above. So perhaps you could enlighten us as to why it's setup this way?

 

[Solo712]

Good to hear you've got it sorted.

And I agree with Galaxiom, I did notice the structural problem but didn't feel it was necessary to highlight it because of your comment above. So perhaps you could enlighten us as to why it's setup this way?

This is one of the more baffling threads in AWF. First, noone points out that Max is not what is called for here, as simple IIf(Nz([EV1 Date]) >= Nz([EV2 Date]), Nz([EV1 Date]), Nz([EV2 Date])) would have done the job way more effectively. Second, two experts claim they detect a structural problem of a structure they have not seen. The OP stated there is a business reason for having two dates as they track two different activities that are related. On the information given there is no reason to quibble. Galaxiom's "design" misses the mark because both of these dates may (and will likely) have values and are bona fide fifferent attributes of the same table entity. So they cannot be in one field.

My $.02

Best,
Jiri

 

[Galaxiom]

This is one of the more baffling threads in AWF. First, noone points out that Max is not what is called for here, as simple IIf(Nz([EV1 Date]) >= Nz([EV2 Date]), Nz([EV1 Date]), Nz([EV2 Date])) would have done the job way more effectively.

I didn't mention it because the real issue is the data structure. Using anything to determine which field to return is a kludge.

The next thing they will be looking for is a way to show which EV field the maximum date came from. That kludge will be even more of a problem.

Second, two experts claim they detect a structural problem of a structure they have not seen. The OP stated there is a business reason for having two dates as they track two different activities that are related. On the information given there is no reason to quibble. Galaxiom's "design" misses the mark because both of these dates may (and will likely) have values and are bona fide fifferent attributes of the same table entity. So they cannot be in one field.

Jiri, it is time for you to review your understanding of relational data structures. There was more than enough information to make the call without seeing the whole structure. Repeating field names is a hint but comparing the data in two fields to get the maximum is a sure sign of a structural anomaly.

The related table should have fields for the FK to the main table record, EV to indicate which EV field it would have been in in the main table and a field for the date.

There would be a composite index on the FK and EV fields to ensure that on only entry was possible for each EV and main record combination. Personally I would use that as the table's PK but others would add an Autonumber field.

This structure can hold not just two dates for separate EV values but any number of dates so long as they were assigned to a separate EV for a main record. It not only returns the maximum date without any fuss but it can just as easily return the corresponding EV it came from.

 

[Solo712]

Jiri, it is time for you to review your understanding of relational data structures. There was more than enough information to make the call without seeing the whole structure. Repeating field names is a hint but comparing the data in two fields to get the maximum is a sure sign of a structural anomaly.

What a load of crap ! An example from the real world: in the Fed government of Canada, we have a process (under "Section 34" of Finanicial Accountability Act) of authorizations on capital spending. This process requires (up to) three independent signoffs (by different officers) within the same fiscal quarter for the request to be processed. If they are not all in the same quarter as the request itself, the request has to be re-submitted. Now obviously I would want to know which of the signatures was the last one if I were to decide whether to process or reject the submission. Wouldn't I ?

And what do you think: would I give a rat's ass whether this authorizations table design exceeds the limits of your grasp of relational data structures ?

Best,
Jiri

 

[Galaxiom]

An example from the real world: in the Fed government of Canada, we have a process (under "Section 34" of Finanicial Accountability Act) of authorizations on capital spending. This process requires (up to) three independent signoffs (by different officers) within the same fiscal quarter for the request to be processed. If they are not all in the same quarter as the request itself, the request has to be re-submitted. Now obviously I would want to know which of the signatures was the last one if I were to decide whether to process or reject the submission. Wouldn't I ?

Of course. And the task of returning that information would be considerably easier and far more efficient if the signature records were held in a related table especially when you want the count of signatures.

In a relational structure, comparing all signature dates with submission date would be simple as would returning the required count. All this directly from a simple query that could give the answer based entirely on the tables' indexes.

Your kludge would require checking each signature date field individually then logically determining if they are all in the same period as the submission. The process would also have to account for varying numbers of required signatures.

And what do you think: would I give a rat's ass whether this authorizations table design exceeds the limits of your grasp of relational data structures ?

It isn't me with the limited grasp of relational structure. Clearly you don't care or you would have taken the time to learn how to build efficient relational structures instead of kludging to get the results.

 

[TH1]

All,
Thanks for your thoughts
The data is like this because, it's a view over an existing SharePoint list that has a lot of live data in and isn't going to be changed to optimise a data model in a reporting solution. I'm aware there are ways around this but happy with my fast and dirty solution.

I went to bed happy with a compromised but working solution, I wake up to find 2 clever people arguing over somebody else's problem.....


I will try the IF statement above as it looks a much cleaner solution.

Thanks again.

 

[vbaInet]

This is one of the more baffling threads in AWF. First, noone points out that Max is not what is called for here, as simple IIf(Nz([EV1 Date]) >= Nz([EV2 Date]), Nz([EV1 Date]), Nz([EV2 Date])) would have done the job way more effectively.

Jiri, that's why you're here. If one of us misses a more simpler solution, you can simply point it out. There's no point making a fuss out of it as we're not perfect. If TH1 had more than the two stated EV fields then his original solution is absolutely valid hence my not questioning any further.

I went to bed happy with a compromised but working solution, I wake up to find 2 clever people arguing over somebody else's problem.....

This is how we have fun TH1

 

[Solo712]

Of course. And the task of returning that information would be considerably easier and far more efficient if the signature records were held in a related table especially when you want the count of signatures.

You want both: count of signatures and the date of the latest one.

In a relational structure, comparing all signature dates with submission date would be simple as would returning the required count. All this directly from a simple query that could give the answer based entirely on the tables' indexes.

So what you are saying is that the authorization table would have to be three authorization tables, because you think it is simpler to query that way. Right ?

Your kludge would require checking each signature date field individually then logically determining if they are all in the same period as the submission. The process would also have to account for varying numbers of required signatures.

My "kludge" would visit each submission's authorization table once, determine if all required signatures are there, and if so, test the date of the latest against the submission date and process accordingly.

What would your kludge do ? Join three authorization tables (assume compliance ~95%) and... then do a "simple query" query based "entirely on the on the tables' indexes" ! Oh, shucks: any of the fields shown can potentially have a Null value. Talking "simple query" now, hmmmm.....

It isn't me with the limited grasp of relational structure. Clearly you don't care or you would have taken the time to learn how to build efficient relational structures instead of kludging to get the results.

Best,
Jiri

 

[Solo712]

There's no point making a fuss out of it as we're not perfect.

The only reason I made a fuss out of it is because Galaxiom started to talk down to TH1 for no good reason.

Best,
Jiri

 

[vbaInet]

The only reason I made a fuss out of it is because Galaxiom started to talk down to TH1 for no good reason.

Best,
Jiri

I understand where you're coming from. To me it didn't sound too harsh, I just think he was pointing out a 'possible' flaw with the structure, but I guess it's down to how one interprets it.

I don't think TH1 minded much anyway. Or did s/he?

 

[Galaxiom]

So what you are saying is that the authorization table would have to be three authorization tables, because you think it is simpler to query that way. Right ?

Not at all. You seem to have got hold of entirely the wrong end off the stick here. Do you see any mention whatsoever of multiple tables for authorisation I my posts?

My "kludge" would visit each submission's authorization table once, determine if all required signatures are there, and if so, test the date of the latest against the submission date and process accordingly.

So you have an authorisations table now? Isn't that what I was saying? That there should be a related table.

What I was critiquing was the use of EV1 and EV2 as fields in the main table then comparing their values. To put it into your signatures example that would mean repeating the fields relating to signature for each signature. Signature1, SignatureDate1, Signature2, SignatureDate2 etc then comparing the SignatureDate# fields to find the latest one.

If you are talking about an Authorisations table with a separate record for each signature then you are describing exactly what I was talking about.

 

[Solo712]

Not at all. You seem to have got hold of entirely the wrong end off the stick here. Do you see any mention whatsoever of multiple tables for authorisation I my posts?

So you have an authorisations table now? Isn't that what I was saying? That there should be a related table.

No you weren't saying that: you were saying :

In a relational structure the two fields should be one in a related table with another field to indicate whether they are EV1 or EV2.

To which I replied that you cannot make such determination without knowing the business case. And I brought up an example of the GoC authorizations table where two or more fields, independent but related in a process would be perfectly ok to compare for values.

What I was critiquing was the use of EV1 and EV2 as fields in the main table then comparing their values. To put it into your signatures example that would mean repeating the fields relating to signature for each signature. Signature1, SignatureDate1, Signature2, SignatureDate2 etc then comparing the SignatureDate# fields to find the latest one.

That's precisely what I am advocating.

If you are talking about an Authorisations table with a separate record for each signature then you are describing exactly what I was talking about.

Again, you are making large assumptions on little information. You have a tunnel vision believing that understanding relational theory equips you to understand every problem instantly. It doesn't !

It makes much more sense to have the authorization sheet as one table. The signatures are not repeating any fields !!!! The fields are specific attributes of the authorization process. One signature would be what's called a 'delegate' (responsible for the requisition process, and delegated to perform the financial signoff on lesser stuff), then a TA (technical authority, approving the technical content of the bid), and for larger expenditures related to items of the Federal Budget, a Section 34 legal signing authority - usually the ADM of the department is required. Some fields other than the electronic signatures and dates are different for the three approvers. So how can you even begin 'designing' an authorizations table before knowing that ? How can you say, going back to our friend TH1, that the two fields EV1 and EV2 should be one, without actually knowing anothing about the business case ?

My point to you is - you can't ! Besides, showing a little respect for the intelligence and experience of others TMK never hurt anyone.

Best,
Jiri

 

[Galaxiom]

I can see the pros and cons of doing it either way. Can you?

Besides, showing a little respect for the intelligence and experience of others TMK never hurt anyone.

Perhaps you could contemplate that notion yourself while reading your own posts in this very thread.

This forum is meant for users to share their knowledge and experience. That is what I was doing when I offered my perspective. You are more than welcome to disagree with those perspectives but rather than deal with the issues you preferred to attack the credibility of both myself and vbaInet.

 

[Solo712]

I can see the pros and cons of doing it either way. Can you?

Well that's a bit different tune, isn't it ? BTW, the table is called "authorizations" in the plural where in fact it is one authorization consisting of three parts. And yes, I can see it would be far better to have one authorization per table IF (a big if) the authorizations were interchangeable.
But again, at the risk of repeating myself, one cannot assume that ahead of time.

Besides, showing a little respect for the intelligence and experience of others TMK never hurt anyone.

Perhaps you could contemplate that notion yourself while reading your own posts in this very thread.

If you go through the thread in sequence you will find my objection was to the method of you and vbaINet arriving at the conclusion that whatever problem the OP writer was having had to do with the structure of data. Your response to that was to question my credentials. I have no idea how you expected me to react, but whatever it was, I am not one to make a silk purse out of a sow's ear.

Best,
Jiri

 

[Galaxiom]

Well that's a bit different tune, isn't it ?

Not really. Personally I would still use the relational structure because has considerable advantages.

BTW, the table is called "authorizations" in the plural where in fact it is one authorization consisting of three parts. And yes, I can see it would be far better to have one authorization per table IF (a big if) the authorizations were interchangeable.

Why such a "big if"? A field for AuthorisationType would cover the need to distinguish the types.

If you go through the thread in sequence you will find my objection was to the method of you and vbaINet arriving at the conclusion that whatever problem the OP writer was having had to do with the structure of data. Your response to that was to question my credentials.

The targets of the remarks were clear:

Second, two experts claim they detect a structural problem of a structure they have not seen.

You also said:

Galaxiom's "design" misses the mark because both of these dates may (and will likely) have values and are bona fide fifferent attributes of the same table entity. So they cannot be in one field.

With this remark and the subsequent ones about "three authorisation tables" and the "big if" you have demonstrated that you have a limited understanding of the use of relational data structures.

 

[Solo712]

]

Galaxiom's "design" misses the mark because both of these dates may (and will likely) have values and are bona fide different attributes of the same table entity. So they cannot be in one field.

With this remark and the subsequent ones about "three authorisation tables" and the "big if" you have demonstrated that you have a limited understanding of the use of relational data structures.

I am sure it appears to you that way, but I tend read that as a way of apologizing for your limited exposure to more complex database design issues, which leads you often to mistake personal idiosyncrasies and obsessive formulas for the gospel of the relational model.

Jiri