create new record based on date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carrie - 25 May 2007 18:01 GMT

I'm not sure if this is even possible.

I will have Frm_Main (from Tbl_MAIN), Subfrm_D13_Main (Tbl_D13_MAIN;
one-to-many relationship with Tbl_MAIN) and Subfrm_D13_Inspection
(Tbl_D13_Inspect; one-to-many w/ Tbl_D13_MAIN).

In Tbl_D13_MAIN, there is a field Action with 3 options (Abandon,
Reactivate, Suspend). Each action requires an inspection but if Suspend is
selected then the inspection must be repeated on a certain frequency based on
other fields (Risk_Level, Risk_Type and Downhole_Option). Here's some
examples:

If Risk_Level = LOW and Risk_Type = Type 1 then Inspection_Frequency = 5 years
If Risk_Level = LOW and Risk_Type = Type 5 then Inspection_Frequency = 1 year
If Risk_Level = MEDIUM and Downhole_Option = Option 1 then
Inspection_Frequency = 3 years

I am currently trying to figure out how to write this into a nested IF
statement. After I finally figure that out, what I think I need the database
to do is look at the Inspection_Date that has been entered and based on the
Inspection_Frequency create a new record in Tbl_D13_Inspect with a new
Inspection_Due_Date so:

Inspection_Date + Inspection_Frequency = Inspection_Due_Date

Is this even possible or proper? Or would it be better to make the next
Inspection_Due_Date a part of the current Inspection record and just create a
new record when the next Inspection is completed?

Thanks!!!!

Reply to this Message

KARL DEWEY - 25 May 2007 21:50 GMT

If you have a lots of if's then use what I call a translation table. In this
table have two fields - what you are looking for and what the output is to be.
Risk_Level    Risk_Type    Downhole_Option    Inspection_Frequency
Low    1    Abandon     1
Low    2    Reactivate    1
Low    3    Suspend    1
Low    4    Abandon     2
Low    5    Reactivate    2
Medium    1    Suspend    2
Medium    2    Abandon     3
Medium    3    Reactivate    3
Medium    4    Suspend    3
Medium    5    Abandon     4
High    1    Reactivate    4
High    2    Suspend    4
High    3    Abandon     5
High    4    Reactivate    5
High    5    Suspend    5

Put both of your tables in the query design view grid but do not join.
Place translation fields in the output row.

In the criteria row under each put the corresponding field from main table.
It will pull the matching record from the translation for
Inspection_Frequency.

Signature

KARL DEWEY
Build a little - Test a little

> I'm not sure if this is even possible.
>
[quoted text clipped - 26 lines]
>
> Thanks!!!!

Reply to this Message

carrietom - 26 May 2007 16:07 GMT

Thanks a lot Karl, I'm going to give this a go. I'm still wondering though,
once I have my Inspection Frequency working, am I able to automatically
create a new record based on the next due date (which might be in 5 years).
Or is that not a good idea?

> If you have a lots of if's then use what I call a translation table. In this
> table have two fields - what you are looking for and what the output is to be.
[quoted text clipped - 53 lines]
> >
> > Thanks!!!!

Reply to this Message

Larry Linson - 29 May 2007 04:26 GMT

> Thanks a lot Karl, I'm going to give this a go. I'm still wondering
> though,
> once I have my Inspection Frequency working, am I able to automatically
> create a new record based on the next due date (which might be in 5
> years).
> Or is that not a good idea?

I see no reason to calculate and save the "next inspection date" if, as I
understand, you can recalculate it from the data in the Record when you need
to check it. Put the calculation in a Public Function and include it in a
Query that lists properties to be inspected within the next X timeperiods.

What use will you make of the new Record in the 5 (or however many) years
before the next Inspection is due? If you are just checking to see IF an
inspection date is near, it makes sense to use the last inspection date plus
your tables to calculate the date when you need to check it. Then, create
the new Record when you do the Inspection.

You didn't say what is being inspected, but could it possibly be removed
from your list / sold / destroyed before the next inspection is due?

Larry Linson
Microsoft Access MVP

Reply to this Message

carrietom - 30 May 2007 04:28 GMT

Hello Again, I've been trying to get this to work all day and I think I
probably misunderstood something. I set up two tables and then set up my
query (SQL below) without linking the tables. This pulls the anticipated
results in the query. Then on the form I put the row source for my field
Inspection_Frequency as the Query but this does not work. I tried adding
requery's to the After Update Events of the other fields but that didn't help
either.

Where I'm guessing my error is, is in the Criteria on the query. You noted
that I should put the corresponding field which I have done but I must have
it wrong.

Any advice on how to correct this is much appreciated!

SELECT Tbl_Inspection_Frequency.Freq_ID,
Tbl_Inspection_Frequency.RiskLevel_ID, Tbl_Inspection_Frequency.RiskType_ID,
Tbl_Inspection_Frequency.Inspect_Frequency

FROM Tbl_D13_Inspect_Freq, Tbl_Inspection_Frequency

WHERE
(((Tbl_Inspection_Frequency.RiskLevel_ID)=[Tbl_Inspection_Frequency]![RiskLevel_ID])
AND
((Tbl_Inspection_Frequency.RiskType_ID)=[Tbl_Inspection_Frequency]![RiskType_ID])
AND
((Tbl_Inspection_Frequency.Inspect_Frequency)=[Tbl_D13_Inspect_Freq]![Inspection_Frequency])
AND
((Tbl_Inspection_Frequency.Dwnhole_ID)=[Tbl_Inspection_Frequency]![Dwnhole_ID]));

> Thanks a lot Karl, I'm going to give this a go. I'm still wondering though,
> once I have my Inspection Frequency working, am I able to automatically
[quoted text clipped - 58 lines]
> > >
> > > Thanks!!!!

Reply to this Message