ORDER DATABASE FIELDS by DATE VALUES for EACH RECORD

[Joe User]

Hi all....I have a feeling this is going to be one of those twisted query
questions, but here it goes anyways....

I want to generate a report that shows the chronology of events (represented
by field names). Essentially, I would like to sort the DATE FIELDS for each
record in the table by the order of the DATES in those DATE FIELDS.

For example:

record ID= 354
date1= 10/2/2003
date2= 9/25/2003
date3= 8/13/2003
date4=11/15/2003
date5=05/09/2003
I would like the output to look like:

recordID:354 date5 date3 date2 date1 date4
I'm doing this in ACCESS, so even if it's possible in SQL SERVER only, I'd
like to see the logic used.

Thanks in advance!

joe

[Bob Quintal]

"Joe User" <jo*@user.com> wrote in
news:bp**********@tribune.mayo.edu:

Hi all....I have a feeling this is going to be one of those
twisted query questions, but here it goes anyways....
Twisted question yes, absolutely not a query question.
No query can make up for a bad table structure, because your dates
should be in a linked child table.

Since you are dealing with a report, you could write code to do the
following steps for each record, in the Detail Format Event.

Read the 5 dates and their field names into an 5x2 array.
Sort the array
concatenate the five field names and some spacing into a variable.
set the value of a textbox to the variable value.
end the sub, which will then print the record.

If you insist on doing something in SQL, which would be worse than
twisted, create 5 queries, each dealing with one field name

SELECT RecordID, "Date 1" as Fieldname, date1 from twistedtable
SELECT RecordID, "date 2" as Fieldname, date2 from twistedtable

then create a union query to combine the 5 queries, ordered by
recordID, datevalue
Now create a report that take thes 5 fieldnames and prints them
horizontally.

Add this as a subreport to your main report.

I want to generate a report that shows the chronology of
events (represented by field names). Essentially, I would
like to sort the DATE FIELDS for each record in the table by
the order of the DATES in those DATE FIELDS.

For example:

record ID= 354
date1= 10/2/2003
date2= 9/25/2003
date3= 8/13/2003
date4=11/15/2003
date5=05/09/2003
I would like the output to look like:

recordID:354 date5 date3 date2 date1 date4
I'm doing this in ACCESS, so even if it's possible in SQL
SERVER only, I'd like to see the logic used.

Thanks in advance!

joe

[Pieter Linden]

"Joe User" <jo*@user.com> wrote in message news:<bp**********@tribune.mayo.edu>...

Hi all....I have a feeling this is going to be one of those twisted query
questions, but here it goes anyways....

I want to generate a report that shows the chronology of events (represented
by field names). Essentially, I would like to sort the DATE FIELDS for each
record in the table by the order of the DATES in those DATE FIELDS.

For example:

record ID= 354
date1= 10/2/2003
date2= 9/25/2003
date3= 8/13/2003
date4=11/15/2003
date5=05/09/2003
I would like the output to look like:

recordID:354 date5 date3 date2 date1 date4

SQL Server is not required. You could do something like...

SELECT Table.Field1, "Table.Field1Name" AS MySource
FROM Department
UNION ALL
SELECT Table.Field2, "Table.Field2Name" AS MySource
FROM Department
ORDER BY MySource;

Of course, if you normalize, it's a walk in the park...

[John]

Looks like a pretty standard bubble sort problem to me. Im assuming
that you have a table with the following schema:

tblEvents
-----------------
Rec_ID AutoNumber
Date1 Date/Time
Date2 Date/Time
Date3 Date/Time
Date4 Date/Time
Date5 Date/Time

I can't imagine trying this in sql, possibly with some messy bunch of
nested queries testing each date against the other and allowing them
to percolate into the proper order but i wouldnt want to read it, much
less write it. However, If you're willing to use VBA then it becomes
pretty simple.

There are 2 ways that I can think of, at the point of entry, as a new
date is recorded would be the easiest, because you would only have to
test the date against the previous date once to determine if it is
larger or smaller.

In this case you would always have the earliest date in Date1 and the
latest date in Date5. Since I dont know your intention I can't tell if
that would be acceptable, but if it is then this would be done prior
to storing the new date so that the order can be maintained in the
table rather then calculated on the fly...

dtMyNewDate = Date

For x = 1 to 5
strFieldName = "Date" & x
'If its an empty field just store the date
If Me(strFieldName).Value = "" Then Me(strFieldName).Value =
dtMyNewDate
'Otherwise test to see if its larger then me and if so, switch em
Else If dtMyNewDate < Me(strFieldName).Value Then
dtTmpValue = Me(strFieldName).Value
Me(strFieldName).Value = dtMyNewDate
dtMyNewDate = dtTmpValue
End If
Next

Debug.Print Me!Rec_ID.Value, Me!Date1.Value, Me!Date2.Value,
Me!Date3.Value, Me!Date4.Value, Me!Date5.Value

If you use this logic then the first time you add a date Date1 would
be null and the date would be stored there. The second time Date1
would not be null and dtMyNewDate would be tested against it, If its
not less then the loop would repeat and since Date2 is null it would
be stored there. If dtMyNewDate IS less then the current date stored
in Date1 is copied to a temporary variable and replaced by
dtMyNewDate. Then the dtTmpValue is placed in dtMyNewDate for further
comparison through the loop, in this way the largest value will always
end up in Date5 and you wont have to figure out what order to display
them in.

If you have to use the dates the way they are for other reasons, then
you can modify this logic so that instead of switching the values
around you are switching the fieldnames around until they are ordered
as necessary and then display them on your report.

DISCLAIMER: This is untested code and should be considered psuedocode,
solely to demonstrate the sorting logic. I code in a coupla languages
and am always having to correct my syntax for VBA, especially with
access.

If you want to post back with exactly how you manage your data entry
and if you can't change the fields around I'll be glad to work it out
for you some more, and heck, I'll even test it first :)

HTH,

John

jobrien AT acscience DOT com

"Joe User" <jo*@user.com> wrote in message news:<bp**********@tribune.mayo.edu>...

Hi all....I have a feeling this is going to be one of those twisted query
questions, but here it goes anyways....

I want to generate a report that shows the chronology of events (represented
by field names). Essentially, I would like to sort the DATE FIELDS for each
record in the table by the order of the DATES in those DATE FIELDS.

For example:

record ID= 354
date1= 10/2/2003
date2= 9/25/2003
date3= 8/13/2003
date4=11/15/2003
date5=05/09/2003
I would like the output to look like:

recordID:354 date5 date3 date2 date1 date4
I'm doing this in ACCESS, so even if it's possible in SQL SERVER only, I'd
like to see the logic used.

Thanks in advance!

joe

[Joe User]

OK - well, I see I have a normalization issue going on. In part of my
defense I will say that I've inherited this DB from a bunch of accountant
types, and I set out on this project as a newbie and didn't realize that
date tracking would even become an issue. However, I do plan on eventually
moving this over to MS-SQL server and the idea of putting date fields into a
child table never even occured to me...In fact, while I'm writing this I
can't right away see how that would help me, but I blindly know that
normalizing things makes DB life better, it's just not apparent to me yet.
When you all set tables up in a DB do you automatically make all your date
fields reside in a child table?

Thanks for the help guys, I am going to see what I can do to with this and
think more about normalizing. I will probably have a few more questions
before it's all said and done.

Thanks again!

Joe
"Bob Quintal" <bq******@generation.net> wrote in message
news:ce******************************@news.teranew s.com...

"Joe User" <jo*@user.com> wrote in
news:bp**********@tribune.mayo.edu:

Hi all....I have a feeling this is going to be one of those
twisted query questions, but here it goes anyways....

Twisted question yes, absolutely not a query question.
No query can make up for a bad table structure, because your dates
should be in a linked child table.

Since you are dealing with a report, you could write code to do the
following steps for each record, in the Detail Format Event.

Read the 5 dates and their field names into an 5x2 array.
Sort the array
concatenate the five field names and some spacing into a variable.
set the value of a textbox to the variable value.
end the sub, which will then print the record.

If you insist on doing something in SQL, which would be worse than
twisted, create 5 queries, each dealing with one field name

SELECT RecordID, "Date 1" as Fieldname, date1 from twistedtable
SELECT RecordID, "date 2" as Fieldname, date2 from twistedtable

then create a union query to combine the 5 queries, ordered by
recordID, datevalue
Now create a report that take thes 5 fieldnames and prints them
horizontally.

Add this as a subreport to your main report.

I want to generate a report that shows the chronology of
events (represented by field names). Essentially, I would
like to sort the DATE FIELDS for each record in the table by
the order of the DATES in those DATE FIELDS.

For example:

record ID= 354
date1= 10/2/2003
date2= 9/25/2003
date3= 8/13/2003
date4=11/15/2003
date5=05/09/2003
I would like the output to look like:

recordID:354 date5 date3 date2 date1 date4
I'm doing this in ACCESS, so even if it's possible in SQL
SERVER only, I'd like to see the logic used.

Thanks in advance!

joe