Im dying here please someone help!!!!!!!! (1 Viewer)

[godseyj]

Ok chaps heres the deal. I have a database for my Human Resources Department. Basically we track personnel information, When people leave and Awards that people receive. Well my boss wants a report that shows people scheduled to leave and if they have been submitted for an award and the timeliness on the award. EX. (name) John Doe (loss Date) 1 Jan 02 (Type award) ARCOM (Due to HQ) ----- and (Due BN) ----- ok these last two dates are the hard part. There are about three different type awards. Each one s due to "HQ" and "BN" at different times, i.e. ARCOM is due to "HQ" 60 days before the "loss Date" But due 45 days before to "BN" AND the amount of days depending on the award is different. Now... hope I havent lost you. I have a report made that draws from a query of two tables. [Personnel Data] and [Awards Archive]. Basically is already shows Name, Loss Date, and Projected Award, I need to have two more that say "Suspense to BN" and "Suspense to HQ" I want these dates to automatically back up from the "Loss Date" depending on "Projected Award" Can you help pa pa pa please!!!!!!!!!

 

[Fornatian]

You need to calculate these figures from the data that you already have stored in the DB. Because the info is changing all the time you cannot get a true reflection by storing the data. It is best to recreate it each time the query is run.

Therefore, I believe you need an Awards table with three fields:

Award Type:Text
TargetToBN:Integer
TargetToHQ:Integer

By including this field in the query, you should by using the DataAdd function and calculated columns be able to discern the figures you are after and thus be able to calculate the state of play today.

Capiche?

 

[raskew]

It sounds as though your report draws on those personnel with established departure dates. Now, if you have a field named "Awardtype", you can use the Switch() function in two calculated fields to return the necessary suspense dates.

Here's what it would look like from the debug window:

awardtype = "MSM"
dprtDate = #07/14/02#
sBn = iif(not isNull(awardtype), dprtDate - switch(awardtype = "AAM", 30, awardtype = "ARCOM", 45, awardtype = "MSM", 60), "")
'if the timeframe between Bn and HQ changes based on the award, use a similar calculated field for
'HQ - otherwise:
sHQ = sBn - 15
? sBn
5/15/02 
? sHQ
4/30/02

Once that's working, go back to the top and change the awardtype to "AAM" or "ARCOM" and note how the result changes.

Then, it's just a matter of placing the calculated fields in your query.

 

[Fornatian]

Bob,

Our replies are equivalent however I believe my reply more user friendly because setting up a table with the data would firstly allow more awards to be created AND the tolerances of existing awards to be manipulated without fiddling with the query via custom data forms.

Ian