Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

difference dates

Discussion in 'Information Technology' started by Sérgio Aires, Jul 28, 2006.

  1. Sérgio Aires

    Sérgio Aires
    Expand Collapse
    Guest

    Hi
    I have a query whith Col-A, Col-B and Col-C. How can I do a difference(dates
    Col-B(2nd row) and Col-C(1st row), …B(3th) and C(2nd),…. etc ) in Col-D?

    Col-A Col-B Col-C Col-D (difference)
    Xpto(1st row) 15-Mar-2006 16-Mar-2006 -
    Abc 18-Mar-2006 20-Mar-2006 2 (diff 18-Mar and
    16-Mar)
    Dfd 23-Mar-2006 25-Mar-2006 3 (diff 23-Mar and
    20-Mar)
    ….

    thanks

    --
    Sérgio Aires
    Lisboa
    Portugal
     
  2. Loading...

    Similar Threads Forum Date
    Difference between us and the Gurus? Sikh Sikhi Sikhism Dec 25, 2014
    Hinduism Difference between Sikhi and Bhakti Hinduism Interfaith Dialogues Mar 5, 2014
    Islam Difference in Bowing head in front of Guru Granth Sahib and Idol Worship Interfaith Dialogues Jun 16, 2013
    Islam What Is Difference Between Sikhism And Islam - Dr. Zakir naik Interfaith Dialogues Jun 16, 2013
    Inspirational Making a Difference: A School for Girls in Kenya Videos Mar 25, 2013

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Use DateDiff() to get the difference between 2 dates.

    Assuming you want the difference in days, type an expression into the Field
    row in query design, like this:
    DateDiff("d", [Col-B], [Col-D])
    and so on for other differences you want to see.

    If you have lots of date fields repeating in the same table, chances are
    that this table should be broken into two. The new table will have a record
    (not a field) for each date, and you can use another field to distinguish
    whatever is the difference between col B, C, and so on.

    So the new table will have fields:
    ForeignID relates to whatever is in our Col-A.
    TheDate Date/Time. The date value.
    DateType Whatever it is that distinguishes B from C from D etc.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Sérgio Aires" <SrgioAires@discussions.microsoft.com> wrote in message
    news:5D375AD0-709D-44B6-AEC0-44431442BC15@microsoft.com...
    > Hi
    > I have a query whith Col-A, Col-B and Col-C. How can I do a
    > difference(dates
    > Col-B(2nd row) and Col-C(1st row), .B(3th) and C(2nd),.. etc ) in Col-D?
    >
    > Col-A Col-B Col-C Col-D
    > (difference)
    > Xpto(1st row) 15-Mar-2006 16-Mar-2006 -
    > Abc 18-Mar-2006 20-Mar-2006 2 (diff 18-Mar and
    > 16-Mar)
    > Dfd 23-Mar-2006 25-Mar-2006 3 (diff 23-Mar and
    > 20-Mar)
    > ..
    >
    > thanks
    >
    > --
    > Sérgio Aires
    > Lisboa
    > Portugal
     
  4. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Not quite sure that's what Sérgio's looking for, Allen.

    It sounds to me as though he wants to compare the value in Col-B of the
    current row to the value in Col-C of the previous row.

    As you know, that's not particularly easy to do in Access. One way is to
    have a computed field that returns the value of the previous row to the
    current row, but that's almost always very inefficient.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:ufj%23EUWiGHA.2208@TK2MSFTNGP05.phx.gbl...
    > Use DateDiff() to get the difference between 2 dates.
    >
    > Assuming you want the difference in days, type an expression into the

    Field
    > row in query design, like this:
    > DateDiff("d", [Col-B], [Col-D])
    > and so on for other differences you want to see.
    >
    > If you have lots of date fields repeating in the same table, chances are
    > that this table should be broken into two. The new table will have a

    record
    > (not a field) for each date, and you can use another field to distinguish
    > whatever is the difference between col B, C, and so on.
    >
    > So the new table will have fields:
    > ForeignID relates to whatever is in our Col-A.
    > TheDate Date/Time. The date value.
    > DateType Whatever it is that distinguishes B from C from D etc.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Sérgio Aires" <SrgioAires@discussions.microsoft.com> wrote in message
    > news:5D375AD0-709D-44B6-AEC0-44431442BC15@microsoft.com...
    > > Hi
    > > I have a query whith Col-A, Col-B and Col-C. How can I do a
    > > difference(dates
    > > Col-B(2nd row) and Col-C(1st row), .B(3th) and C(2nd),.. etc ) in

    Col-D?
    > >
    > > Col-A Col-B Col-C Col-D
    > > (difference)
    > > Xpto(1st row) 15-Mar-2006 16-Mar-2006 -
    > > Abc 18-Mar-2006 20-Mar-2006 2 (diff 18-Mar

    and
    > > 16-Mar)
    > > Dfd 23-Mar-2006 25-Mar-2006 3 (diff 23-Mar

    and
    > > 20-Mar)
    > > ..
    > >
    > > thanks
    > >
    > > --
    > > Sérgio Aires
    > > Lisboa
    > > Portugal

    >
    >
     
  5. Sérgio Aires

    Sérgio Aires
    Expand Collapse
    Guest

    thanks for your help..
    >
    > but in this case the values or dates are in diferent rows in the query. the
    > Function Diff doesn't work in this case.


    --
    Sérgio Aires
    Lisboa
    Portugal


    "Douglas J Steele" escreveu:

    > Not quite sure that's what Sérgio's looking for, Allen.
    >
    > It sounds to me as though he wants to compare the value in Col-B of the
    > current row to the value in Col-C of the previous row.
    >
    > As you know, that's not particularly easy to do in Access. One way is to
    > have a computed field that returns the value of the previous row to the
    > current row, but that's almost always very inefficient.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    > news:ufj%23EUWiGHA.2208@TK2MSFTNGP05.phx.gbl...
    > > Use DateDiff() to get the difference between 2 dates.
    > >
    > > Assuming you want the difference in days, type an expression into the

    > Field
    > > row in query design, like this:
    > > DateDiff("d", [Col-B], [Col-D])
    > > and so on for other differences you want to see.
    > >
    > > If you have lots of date fields repeating in the same table, chances are
    > > that this table should be broken into two. The new table will have a

    > record
    > > (not a field) for each date, and you can use another field to distinguish
    > > whatever is the difference between col B, C, and so on.
    > >
    > > So the new table will have fields:
    > > ForeignID relates to whatever is in our Col-A.
    > > TheDate Date/Time. The date value.
    > > DateType Whatever it is that distinguishes B from C from D etc.
    > >
    > > --
    > > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > > Tips for Access users - http://allenbrowne.com/tips.html
    > > Reply to group, rather than allenbrowne at mvps dot org.
    > >
    > > "Sérgio Aires" <SrgioAires@discussions.microsoft.com> wrote in message
    > > news:5D375AD0-709D-44B6-AEC0-44431442BC15@microsoft.com...
    > > > Hi
    > > > I have a query whith Col-A, Col-B and Col-C. How can I do a
    > > > difference(dates
    > > > Col-B(2nd row) and Col-C(1st row), .B(3th) and C(2nd),.. etc ) in

    > Col-D?
    > > >
    > > > Col-A Col-B Col-C Col-D
    > > > (difference)
    > > > Xpto(1st row) 15-Mar-2006 16-Mar-2006 -
    > > > Abc 18-Mar-2006 20-Mar-2006 2 (diff 18-Mar

    > and
    > > > 16-Mar)
    > > > Dfd 23-Mar-2006 25-Mar-2006 3 (diff 23-Mar

    > and
    > > > 20-Mar)
    > > > ..
    > > >
    > > > thanks
    > > >
    > > > --
    > > > Sérgio Aires
    > > > Lisboa
    > > > Portugal

    > >
    > >

    >
    >
    >
     

Share This Page