Balance

[Steven Stewart]

Is it possible to do the following?

I have a field called BalCarFor (Balance Carried Forward). If the
primary key of a new record is identifcal to a primary key of a previous
record (except for the month/year) for that specific employee, and that
previous record is the most recent month/year with respect to the new
record, is there a way to make the BalCarFor equal to the ending Balance
of the previous record?

i.e. the BalCarFor (starting balance) for Joe in April 2003 who worked
for Team A should equal the ending balance for Joe in March 2003 for
Team A, or the most recent, existing record for Team A.

So when the user goes to create a new record for Joe and selects Team A,
the starting balance of the new record is automatically filled in with
the ending balance of the most recent previous record for that
employee/team combo.

Thanks for any help, because I have no idea how to do this.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Terry Kreft]

Re: Balance

Steven,
Your structure and terminology appear non-normalized.

Firstly a record cannot have the same Primary Key as another record. The
point of a Primary Key is that it is unique.

Secondly this BalCar field is a calculated field. If instead of storing
this you calculated it when you needed it (e.g for reports) then your
problem would go away.

Terry

"Steven Stewart" <o6t6i@unb.ca > wrote in message
news:3fccc9d7$0 $88383$75868355 @news.frii.net. ..[color=blue]
> Is it possible to do the following?
>
> I have a field called BalCarFor (Balance Carried Forward). If the
> primary key of a new record is identifcal to a primary key of a previous
> record (except for the month/year) for that specific employee, and that
> previous record is the most recent month/year with respect to the new
> record, is there a way to make the BalCarFor equal to the ending Balance
> of the previous record?
>
> i.e. the BalCarFor (starting balance) for Joe in April 2003 who worked
> for Team A should equal the ending balance for Joe in March 2003 for
> Team A, or the most recent, existing record for Team A.
>
> So when the user goes to create a new record for Joe and selects Team A,
> the starting balance of the new record is automatically filled in with
> the ending balance of the most recent previous record for that
> employee/team combo.
>
> Thanks for any help, because I have no idea how to do this.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[Steven Stewart]

Re: Balance

Terry,

I mean the records are identical for the primary key except for the
month and year. In that situation, the records are for the same region
and team, but a different month/year. If the team and region was
different in the previous record, then I wouldn't be carrying over that
balance.

i.e. This is what I want...notice BalCarFor as it relates to the EndBal
of previous record. InvOut=Inventor y Out

Employee Month Year Team Region BalCarFor InvOut EndBal
Joe 1 2003 A NB 10 5 5
Joe 4 2003 A NB 5 3 2
Joe 5 2003 A NB 2 1 1
Joe 8 2003 A NB 1 0 1

Also notice that some months Joe doesn't have an existing record.

The ending balance actually IS a calculated field but I am showing it
like this for demonstration purposes. The Balance Carried Forward is
not calculated. In fact, what I want to know is how to make BalCarFor a
calculated field that obtains the appropriate ending balance from a
prior record. That's essentially what I was trying to ask :)

Thanks for any advice on this!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Terry Kreft]

Re: Balance

The BalCalFor and the EndBal are both calculated fields from what I can see.

How do you get an initial balance and how can the EndBal be larger than the
BalCalFor for the same record, in other words this seems to be a series of
decrementing records for the balance, can it be incremented as well?

Terry


"Steven Stewart" <o6t6i@unb.ca > wrote in message
news:3fcdc6f8$0 $88383$75868355 @news.frii.net. ..[color=blue]
> Terry,
>
> I mean the records are identical for the primary key except for the
> month and year. In that situation, the records are for the same region
> and team, but a different month/year. If the team and region was
> different in the previous record, then I wouldn't be carrying over that
> balance.
>
> i.e. This is what I want...notice BalCarFor as it relates to the EndBal
> of previous record. InvOut=Inventor y Out
>
> Employee Month Year Team Region BalCarFor InvOut EndBal
> Joe 1 2003 A NB 10 5 5
> Joe 4 2003 A NB 5 3 2
> Joe 5 2003 A NB 2 1 1
> Joe 8 2003 A NB 1 0 1
>
> Also notice that some months Joe doesn't have an existing record.
>
> The ending balance actually IS a calculated field but I am showing it
> like this for demonstration purposes. The Balance Carried Forward is
> not calculated. In fact, what I want to know is how to make BalCarFor a
> calculated field that obtains the appropriate ending balance from a
> prior record. That's essentially what I was trying to ask :)
>
> Thanks for any advice on this!
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[Steven Stewart]

Re: Balance

Hi Terry,

Actually, I've had some help and got the query figured out that will
calculate this value BalCarFor value I want (the starting balance). The
query returns prior records and the specific record is found using the
MAX function based on the month and year.

Anyhow, my real problem now is that I don't know how to have different
controls on my form to be based on another recordset. The form is based
on "Employees INNER JOIN DataRecords". The starting balance for the
displayed record on the form needs to be calculated using the other
query I mentioned.

Is there a way to make it so that I can have a text box that is bound to
this query, while the other text boxes are bound to the underlying
recordset of the form?

I am a student and this is a project for me at my co-op job. My task
has been to learn about Access. I told them that I thought I could do
it in VB as I had used ADO controls before in VB. That would be ideal
for me, but I don't know how to do it in VBA. There is no ADO control
that I can drop onto the form.

Anyhow, thanks for the assistance!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!