ORDER DATABASE FIELDS by DATE VALUES for EACH RECORD

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Joe User

    #1

    ORDER DATABASE FIELDS by DATE VALUES for EACH RECORD

    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

    #2
    Re: ORDER DATABASE FIELDS by DATE VALUES for EACH RECORD

    "Joe User" <joe@user.com > wrote in
    news:bp3pfc$dbi $1@tribune.mayo .edu:
    [color=blue]
    > Hi all....I have a feeling this is going to be one of those
    > twisted query questions, but here it goes anyways....[/color]

    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.
    [color=blue]
    >
    > 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
    >
    >
    >[/color]

    Comment

    • Pieter Linden

      #3
      Re: ORDER DATABASE FIELDS by DATE VALUES for EACH RECORD

      "Joe User" <joe@user.com > wrote in message news:<bp3pfc$db i$1@tribune.may o.edu>...[color=blue]
      > 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[/color]

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

      SELECT Table.Field1, "Table.Field1Na me" AS MySource
      FROM Department
      UNION ALL
      SELECT Table.Field2, "Table.Field2Na me" AS MySource
      FROM Department
      ORDER BY MySource;

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

      Comment

      • John

        #4
        Re: ORDER DATABASE FIELDS by DATE VALUES for EACH RECORD

        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" <joe@user.com > wrote in message news:<bp3pfc$db i$1@tribune.may o.edu>...[color=blue]
        > 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[/color]

        Comment

        • Joe User

          #5
          Re: ORDER DATABASE FIELDS by DATE VALUES for EACH RECORD

          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" <bquintal@gener ation.net> wrote in message
          news:ce1198712f 38adba71ffcdca6 e085b62@news.te ranews.com...[color=blue]
          > "Joe User" <joe@user.com > wrote in
          > news:bp3pfc$dbi $1@tribune.mayo .edu:
          >[color=green]
          > > Hi all....I have a feeling this is going to be one of those
          > > twisted query questions, but here it goes anyways....[/color]
          >
          > 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.
          >[color=green]
          > >
          > > 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
          > >
          > >
          > >[/color]
          >[/color]


          Comment

          Working...