Complex IIF Statement

  • Thread starter Thread starter Scottie
  • Start date Start date
S

Scottie

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
 
Scottie said:
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
Click to expand...

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.
Click to expand...

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!
 
Back
Top