Filtering dates problem

[queezy]

I have a table that looks like so -

Contract # | Stage 1 | Stage 2 | Stage 3 | Stage 4| ...............
----------------|-------------|--------------|-------------|------------|

45234255 | 8/8/06 | 8/24/06 | 8/31/06 | 9/7/06 | ................
24256211 | 9/2/06 | 9/14/06 | 9/21/06 | 9/30/06 | ................

Except there about 20 stages. These stages are goal dates basically of
when that stage of the contract should be completed. I want to create
a form that a user can enter in a date range in a form and it outputs
all of the upcoming goals within that time period.
I currently have a form where you enter a start date & an end date and
hit ok and the form opens a report showing Stage 1 dates that are
within that time frame, but I'm not sure how to show all stages
upcoming.

Say the time period entered was 8/24/06 - 9/3/06, then I would like it
to show something like this in the report -

Date |Contract # | Stage
------------|----------------|-------------|
8/24/06 |45234255 | Stage 2 |
8/31/06 |45234255 | Stage 3 |
9/2/06 |24256211 | Stage 1 |

Any help is greatly appreciated.

 

[Jeff Boyce]

You did say "all help..."<g>

Your data is particularly well suited ... to be a spreadsheet! Whenever you
find "repeating fields" ("stage1, stage2, ...), your table is not
well-normalized.

"Why should I care?", you ask?

As a relational database, Access can do some things that Excel cannot. But
the data has to be in a structure that Access can work with (i.e.,
well-normalized). Otherwise, both you and Access will have to work extra
hard to get simple things done ... it can be a little like trying to drive
nails with a chainsaw. While possible, it is awkward and potentially
dangerous.

Consider spending some time away from the database, looking into
normalization and relational databases. Jeff Conrad has some excellent
resources at:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

In the mean while, if your process is set up with a known (or know-able)
number of days for each stage, you could use a query to "calculate" when
each stage should be completed, based on a table of "StageDaysToComplete".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[queezy]

Thanks Jeff,
I have read normalization articles before & even took a class on the
stuff in college, but I'm not very good at it. I even thought the
table was wrong from the get go, but I didn't put enoght thought into
it, and tried to go on with creating forms anyway. I went through the
normalization steps in the one microsoft article on your site, which
put the steps in a simple format and had a good example DB with it.
After going through these steps I came up with what I believe to be the
"correct" format.

Contract # | Stage | Target Date |
-----------------|------------|-------------------|
45234255 | 1 | 8/8/06 |
45234255 | 2 | 8/15/06 |
...................................................
99999999 | 1 | 8/4/06 |


This will complicate my add/edit form a bit, but atleast I'm normalized
& I can actually make the report I want. Thanks again.

 

[Jeff Boyce]

I think there may still be something missing.

From the table structure you provided, it looks like you are "hard-coding"
target dates.

If each stage has an associated "duration" (e.g., stage 1 allows 2 weeks,
stage 2 allows 5 days, ...), then you don't need to store the target date,
provided you have a "contract start date".

Now I'll be fussy and point out that "target" and "actual" are not the same.
If you want to track compliance, you WILL need to store a date on which the
stage was actually completed.

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

 

[queezy]

Jeff,


I do have dates that are calculated and are represented on the forms as
original target date. But these target dates that I am storing in my
table are ones that are changed & thus need to be stored. Orig. Targ.
Date as shown below is all calculated based on the first stages' date &
is all read only in the form. But Updated Targ Date can be
re-negotiated and must be able to be changed. When the contract is
first entered in the form Updated Targ Date is calculated out for the
user based on the first stage, but is stored in the table, for when the
field is changed.
This is something what my main form looks like -
---------------------------------------------------------------------------------------
Contract# 999999999

---------------------------------------------------------------------------------------
Stage | Orig. Targ. Date | Updated Targ Date | Actual Date |
---------|--------------------------|-----------------------------|-------------------|

1 | 8/8/06 | 8/8/06 |
8/10/06 |
2 | 8/12/06 | 8/12/06 |
8/12/06 |
3 | 8/20/06 | 8/25/06 |
8/25/06 |
4 | 8/31/06 | 9/5/06 |
9/8/06 |

Thanks for the continued support.
- Justin

 

[queezy]

Argh, those dates under the stage numbers are supposed to be in the
actual date column one line up.