Welcome to SPN

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

Sign Up Now!

Complex IIF Statement

Discussion in 'Information Technology' started by Scottie, Jul 28, 2006.

  1. Scottie

    Scottie
    Expand Collapse
    Guest

    I need to be able to write a complex iif statement that will allow me
    to query the data below.

    Date Type Code31 Code43 Code42
    23-May-06 P 122 39425 3493
    23-May-06 T 0 10256 324
    23-May-06 N 1074 3995 1507
    24-May-06 P 61 40749 3363
    24-May-06 N 992 3404 1416
    24-May-06 T 29 10537 440

    These are the results that I want to be returned. The "P" event that
    occurs on 24-May-06 needs to be associated with the "T" and "N" events
    that occur on 23-May-06. I can't figure out how to properly write the
    statement.

    Date Type Code31 Code43 Code42
    23-May-06 T 0 10256 324
    23-May-06 N 1074 3995 1507
    24-May-06 P 61 40749 3363


    Any help would be greatly appreciated.
    Thanks. Scott
     
  2. Loading...

    Similar Threads Forum Date
    God, Truth, Love, Goodness, Beauty & Human complexes Blogs Oct 22, 2015
    India Pro-Khalistan slogans raised in Golden Temple complex Breaking News Jun 6, 2012
    India Final Deadline Set for Khalsa Complex Breaking News Jul 16, 2011
    Heritage Khalsa heritage complex to be dedicated to nation in Aug 2011 History of Sikhism Dec 2, 2010
    India India cannot help but be complex Breaking News Oct 2, 2010

  3. John W. Vinson/MVP

    John W. Vinson/MVP
    Expand Collapse
    Guest

    "Scottie" <sstrachan@ups.com> wrote in message
    news:1149200219.283701.279050@y43g2000cwc.googlegroups.com...
    >I need to be able to write a complex iif statement that will allow me
    > to query the data below.
    >
    > Date Type Code31 Code43 Code42


    Well, for starters, storing data - Code43 frex - in fieldnames is
    emphatically NOT proper normalization, and may be part of your problem! If
    you have a one (date) to many (codes) relationship, you may want to consider
    a datefield (not named Date, which is a reserved word), Type, Code (with
    values 31, 42, 43), and some other fieldname for the value corresponding to
    that code.

    > 23-May-06 P 122 39425 3493
    > 23-May-06 T 0 10256 324
    > 23-May-06 N 1074 3995 1507
    > 24-May-06 P 61 40749 3363
    > 24-May-06 N 992 3404 1416
    > 24-May-06 T 29 10537 440
    >
    > These are the results that I want to be returned. The "P" event that
    > occurs on 24-May-06 needs to be associated with the "T" and "N" events
    > that occur on 23-May-06. I can't figure out how to properly write the
    > statement.


    what's the logic? How can Access unambiguously determine which T and N
    events should be linked? Is it the previous date? What if there IS no record
    for the previous date (say the P event is on a Monday, do you want the T and
    N from the previous Friday, or the most recent prior date)? Or what if there
    are multiple T's or N's?

    I fear you're "committing spreadsheet upon a database" here!


    --
    John W. Vinson/MVP

    > Date Type Code31 Code43 Code42
    > 23-May-06 T 0 10256 324
    > 23-May-06 N 1074 3995 1507
    > 24-May-06 P 61 40749 3363
    >
    >
    > Any help would be greatly appreciated.
    > Thanks. Scott
    >
     

Share This Page