Update Table1 automatically update Table2 (1 Viewer)

[sohailcdc]

Hi there
During my free time I try to learn Access and/or other applications and with the help of ONLINE forums I really learn a lot

Anyways, Now I am trying to learn/create Company's Fiscal Calendar

I am using following approach, but unable to figure-it-out how to handle this
Therefore, need GURUs help

Table1
Hdate (date) = Jan-01-2015
Type of holiday (text) = "NEW YEAR"

Table2
Period(text) 2015-01
Month(text) January
FDate (date) = Jan-01-15
TDate (date) = Jan-31-15
Holidays = Query from Table1
NWD ("Net working days") = TDate - FDate - Holidays = XXXXX

NWD is very important number as many of calculation based on this NUMBER

BIG Question
Let say for the sake of learning, if there is subsequently, I came to know there's another holiday in the month of Jan/2015, e.g. on Jan 10, 2015

- Which I can simply go into table2 and add the new holiday

However, Table3 will not going to be update

Here I need, your Professional Tip how to manage this that Table3 automatically update (both "Holidays" and "NWD")

Option 1 = which I can think based of my limited knowledge and/or concept is that simply recall the record and refresh the Query from Table1 and update the record

Based on online search, I learn that query result shouldn't be save therefore :banghead:

Option 2 = Non-professional thinking but I have no clue how this will be done, that instead of saving both "Holidays and NWD" in table3 these filed should be generated every time, means whenever, I need to USE "NWD", I have to generate queries and made calculation every time to get updated "NWD" - however, this will create delay in procedures etc.

Option 3 = Can I link the Query from Table1 to Table2 (BUT HOW) instead of saving value and calculate "NWD" on time

Option 4 = Waiting for you the GURU's valuable suggestions or inputs

Have a nice day

 

[CJ_London]

Here I need, your Professional Tip how to manage this that Table3 automatically update (both "Holidays" and "NWD")

You've described tables 1 and 2 but not table 3. - do you mean table 2?

Assuming you do, it seems to me that in table 2 you have two unnecessary fields - holidays and NWD.

The first is an unnecessary lookup and the second is a calculated field. Both of these should be calculated in a query - since, as I think you have discovered it does not contain the correct value - and I'm not sure how you would calculate holidays anyway from a lookup - you would need a count.

So my tip is to remove both these fields and calculate in a query when required.

One final check - your NWD calculation assumes every day is a working day (other than holidays) - are you not concerned about weekends?

 

[CJ_London]

Option 3 = Can I link the Query from Table1 to Table2 (BUT HOW) instead of saving value and calculate "NWD" on time

 SELECT datediff("d",Fdate,TDate)-Dcount("*","Table2","HDate BETWEEN " & FDate & " AND " & TDate) AS NWD
 FROM Table2

or

SELECT datediff("d",Fdate,TDate)-(SELECT count("*") FROM Table2 WHERE HDate BETWEEN Table2.FDate AND Table2.TDate) AS NWD
FROM Table2

 

[sohailcdc]

Thanks CJ
As I said, I am just trying to learn, since you pointed out, weekend I will trying to incorporate as well

Usually, the place where I am working, plant mostly 24/7 other than stats holidays, summer and winter plant maintenance shutdown

Based on your inputs, I guess, option 2 is what you're recommending - however, don't you thinking this will slow down the procedures if future calculation 100% based on "Net Working Days"

 

[MarkK]

You should store raw data only. If you can calculate a result from your raw data, then you should not store that result, and your table 2 is all calculable from a single date, so the stored values break normalization.

 

[CJ_London]

Option 2 is what you're recommending - however, don't you thinking this will slow down the procedures if future calculation 100% based on "Net Working Day

Not that you'll notice - just make sure HDate is indexed in table 1 Also my second code suggestion will be slightly faster. DCount and the other domain functions is the slowest way of doing it