Query in Access

[ksrinivas]

I have been struggling with a unique problem. Was wondering if anyone
can advise on this.
I have a table that has 5 columns that have the dates (due dates from
each step in the workflow). The table has other columns, but those
are not of interest here. I want to know... on any given day, what
status should a particular row (person) should be at... expected
status. This is calculated by the upcoming status date from the 5.

How can I do this? I have tried various things... so my mind is in a
muddle right now!
Here is the table:
Code Step1DueDate Step2DueDate
Step3DueDate Step4DueDate Step5DueDate
A 1/1/2009 1/9/2009
1/20/2009 1/30/2009 2/10/2009
B 1/5/2009 1/15/2009
1/22/2009 2/1/2009 2/12/2009
C 1/10/2009 1/20/2009
1/28/2009 2/5/2009 2/20/2009
D 1/1/2009 1/15/2009
1/25/2009 2/7/2009 2/14/2009

So on the 1/1 the Expected status report shoudl be:
A Step 1
B Step 1
C Step 1
D Step 1

on 1/10 the Expected status report should be:
A Step 3
B Step 2
C Step 2
D Step 2
on 1/22 the Expected status report should be:
A Step 4
B Step 4
C Step 3
D Step 3

and so on....

 

[Jeff Boyce]

When you have 'repeating fields" ([Step1DueDate], [Step2DueDate], ...
[StepNDueDate]), you have a spreadsheet, not a relational database table.

Access is optimized to work with well-normalized data, not 'sheet data. If
"normalization" and "relational database" are unfamiliar, plan on spending
some time working your way up this learning curve.

Or, continue trying to come up with work-arounds (i.e., kludges) to
compensate for a data design that doesn't match what Access works best with.

Pay now or pay later ...! (and I'd suggest now -- turn off the PC and take
up paper & pencil and revisit your data design)

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP