sql that sorts by most recent of 5 date fields?

[JeffNolan1900]

I have five text fields:

Txt_Date_Edited_1
Txt_Date_Edited_2
Txt_Date_Edited_3
Txt_Date_Edited_4
Txt_Date_Edited_5

On My report, I am trying to sort the values by the most recent txt_Date_, regardless of what field it is in.

For instance...
Record 1
Date Edited 1 (12/20/07)
Record 2
Date Edited 3 (12/10/07)
Record 3
Date Edited 5 (12/05/07)

The problem I have with normal Sorting is that Date Edited 1, even though it is last in time, is showing 1st. I need a sql that will take all 5 into account at the same time, and sort by the most recent, so that Date Edited 5 shows up first.

I looked through previous posts and quite a bit of searching and could not find any help on this.
Hope this makes sense and thanks in advance for any help.
Jeff

 

[lespaul]

create a query that normalizes your data:

SELECT IdField, 'Edit1' As Event, [Date Edited 1] As EventDate From TableName where Not IsNull([Date Edited 1])
UNION
SELECT IdField, 'Edit2' As Event, [Date Edited 2] From TableName where Not IsNull([Date Edited 2])
UNION
SELECT IdField, 'Edit3' As Event, [Date Edited 3] From TableName where Not IsNull([Date Edited 3])
etc.

typed not tested...the IsNull function syntax may not be correct
then use this query as the source to get the MAX date for each IDField.

SELECT IdField, Event, MAX(EventDate) From qryNormalized GROUP BY IdField, Event

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[SgtJarrow]

Not quite normalized...so this is a bit harder than a normalized setup. Will the dates always be filled in order - in other words, will date1 always have a value before date2, which is always filled before date3, etc?

If so, this will find the last filled field for each record, and display and sort based on that found field. You might want to change the #12/31/1899# at the te end...that is my "default date" I use when working with dates. I always include a filter to drop those, because they are records with no value in the date field.

Should at least get you going in the right direction.

ORDER BY Nz([date5],Nz([date4],Nz([date3],Nz([date2],Nz([date1],#12/31/1899#))))) DESC

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[JeffNolan1900]

Thanks.

And yes, mstrmage1768, the fields will be filled in in order from 1 to 5, but in some records, 1 will be the only one active, in some 1, 2, and 3 may be active (And in order by date also), anywhere up to 5.

I tried it with the default date you put in, and then tried changing the date to null, because I don't use a default value. I even tried it with a value that is in the field Edited_Date_1 and is already part of the table.

example1

ORDER BY Nz([Edited_Date_5],Nz([Edited_Date_4],Nz([Edited_Date_3],Nz([Edited_Date_2],Nz([Edited_Date_1],#null#))))) DESC

example2

ORDER BY Nz([Edited_Date_5],Nz([Edited_Date_4],Nz([Edited_Date_3],Nz([Edited_Date_2],Nz([Edited_Date_1],#3/19/2007#))))) DESC

I get the error "Syntax error (comma) in query expression...". And just to be clear, I am putting this info in the report, "Sorting and Grouping Box, Under Field/Expression. I tried it with removing all the commas, rearranging the ()'s, and all sorts of different combinations all with similar or worst error messages....


To explain. I am brand new to this database stuff, and know a bit about vba. I sort of got thrown into not only my normal job requirements, but also...Database admin.

 

[SgtJarrow]

The reason I use that 12/31/1899 default date is because date fields don't like Nulls...

The code I supplied does not actually put the default date anywhere...nor are my tables defaulted to that. So if I confused you there.

The code I supplied looks at date5 first, if no value, it checks date4, if no data it checks date3 and so on....if nothing is found from 5 down to 1 it returns the 12/31/1899. So all you have to do is filter the dataset not to show anything with a 12/31/1899 if you don't want to see them.

I think your example2 should be right. I would suggest the following. Create a new Query off the table the report is based on. Add all the fields from the table. Then open the query in SQL view and paste your example2 in at the bottom, just before the semicolon. You should end up with something like:

Select *
FROM Table1
ORDER BY Nz([Edited_Date_5],Nz([Edited_Date_4],Nz([Edited_Date_3],Nz([Edited_Date_2],Nz([Edited_Date_1],#3/19/2007#))))) DESC;

Save this with some name. Then, open the report up in design view, remove all the filter and sorting stuff you have been playing with. Set the report's datasource property to be the query you just created instead of the table. If that doesn't work or seem to get you closer let me know.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[JeffNolan1900]

Thanks. That did It.

I am starting to see how all this craziness fits together. It is so kind of you to help me like this...

queries linked to tables to forms, which can't read the reports. arrhhh. It's fun though.

Jeff

 

[MichaelRed]

a different approach may be seen by using search for "basMin" in htese (Tek-Tips) fora. some 'interesting' examples of various aggregate function based on records may be seen. another term to use in the search would be "ParamArray".




MichaelRed

 

[JeffNolan1900]

Ok. I will look in that. Thanks.