Time bound column

[senkurion]

Hello,

I have a simple database which tracks the activity of staff members untill
he/she retires from the company. This database keeps tracks of Tel bill,
expenses, allowences etc and finally total cost to keep various staff member
in the company. all the expenses are recorded in quaretrly basis and all the
cost are presented in running sum, so the query looks like below.

Name---Date/employment---Base salary---Allowences (Q1)---Other expenses
(Q1)---Total

In above query Total is running sum(life time expenses on staff member).
Allowences and other expenses fields are calculated on quaretrly basis, so
if a staff memebers works say for 5 yrs for the company theres should be 20
quarters for allowences and 20 for other expenses.

At first I created a query with all fields and 20 quarters in design view
but while converting to datasheet view it asks for the value for "allowences"
and "other expenses" for all quarters which will appear in future. It is not
possible to give the value for future allowences and expenses because we dont
know yet.

I was thinking that may be it is possible to automaticall creat new column
only at the end of each quarter (time bound field creation which bears the
same formula as previous quarter) so that the value is automativcall
calculated and filled.

or there is any other trick to solve this problem??

Gurus please help

Thanks

 

[Dale Fye]

And how are you trying to present this? Is it all on one row, or separate
pages for each employee? I think I'd do separate pages for each employee and
have the year and quarter as the left most values in the report.

If you do this in a report, you can group by the employee. Then add a
column that has the sum of the Salary, Allowances, Other_Exp, then set the
Running Sum property to "Over Group".

If you insist on doing this as columns, you are going to have nothing but
headaches.

HTH
Dale

 

[senkurion]

Thanks for the reply. We wanted that in either table or in querry so that
the data is saved in the sysytem. Presentation is one row for each employee
and differet payments in different columns and one Total cost column as a
running sum column. It is ok for me if we can do that in table but I think it
is not a good idea to do calculation in table so I created a querry. I tried
to do that in report but then report will only present situation not past. or
there is some other trick that i dont understand?

 

[Dale Fye]

If I understand you correctly, what you really want to do is display this
data as a spreadsheet, is that correct? If you are storing this data in a
table, I would recommend something like:

EmployeeID
CalendarYear
Quarter
Type_Pay ("Base", "Allowance", "Other", ...)
Amount

There is no need to store the Totals for the quarter as that can be
calculated in a query.

So, if each employee has a row in this table/query, how do you decide where
the numbers start for each employee? Do you have some employees that started
in 2000 or earlier and others that just started this quarter, how would you
display their data>

Would you have colums like:
Name
CY1999-1Qtr_BasePay
CY1999-1Qtr_Allowances
CY1999-1Qtr_Other
CY1999-1Qtr_Total
CY2000-1Qtr_BasePay
CY2000-1Qtr_Allowances
CY2000-1Qtr_Other
CY2000-1Qtr_Total

With these columns being blank for people that did not join the company
until CY 2002?

If you want this type of structure, you could create a CrossTab query, and
concatenate the Year, Quarter, an Type Pay to come up with something like
this, but that would be a nightmare.

Dale.