apply calculation to group of records and append into the same tab

[Guest]

Hello all,

Working with access 2003, consider myself an intermediate user, but no (very
limited) programming skills.

I haven't attempted this on my own yet, as I thought I'd write it in first
to see if I'm on track.

I've created a relational financial database for our military command. In
the beginning of the year, we receive our annual budget and spread it to
different programs. Based on changing priorities, funding will get moved
from one program to another throughout the year and these changes need to be
captured as separate entries, so that an audit trail can be tracked. So
right now the database has a record for each program and an amount assigned
as a 'startpoint'.

So here's my hypothetical situation. Say each programs supply line needs to
be reduced by 10%. I should be able to query the table for 'supply', and in
a new field enter an expression(?) = [amount]*.-10. Then have these records
append to the original table so that I would end up with two records similar
to below:

program amount
MILSTAR 100 (this would be the original record)
MILSTAR -10 (this would be the appended record)

Thus when I total MILSTAR, I have 90.

Here's the catch...I need to be able to let the user enter the change, ie
-10% or +25% and select the category (supplies, or travel, or training etc).

So is this difficult?

I'll be happy to post database details as requested.

Laura

 

[bmarland]

It seems like your database would need a bit more structure, such as a
create date. If you are creating an audit trail then it would probably
be best to base all your calculations on the last entry, not the first
entey (startpoint). This kind of entry can be created with a query but
you are probably better off creating an entry form based on the query
for end-users. You should also add an autonumber key field to identify
rows.

This is an attempt at a query, notice the parameters for the user to
enter the desired section and calculation:

insert into supply (program, amount)
select [enter program name] as program, ([enter percent change from
last entry]*a.amount)+a.amount as amount
from supply a inner join (select program, max(create_date) as last_date
from supply group by program) b
on a.program = b.program and a.create_date = b.last_date

hope that helps.

 

[Guest]

Thanks for the advice...it should get me started. The table already has an
auto date field and an index auto number field. I'll give it a shot this
morning when I get into work and let you know.

Laura

It seems like your database would need a bit more structure, such as a
create date. If you are creating an audit trail then it would probably
be best to base all your calculations on the last entry, not the first
entey (startpoint). This kind of entry can be created with a query but
you are probably better off creating an entry form based on the query
for end-users. You should also add an autonumber key field to identify
rows.

This is an attempt at a query, notice the parameters for the user to
enter the desired section and calculation:

insert into supply (program, amount)
select [enter program name] as program, ([enter percent change from
last entry]*a.amount)+a.amount as amount
from supply a inner join (select program, max(create_date) as last_date
from supply group by program) b
on a.program = b.program and a.create_date = b.last_date

hope that helps.

Hello all,

Working with access 2003, consider myself an intermediate user, but no (very
limited) programming skills.

I haven't attempted this on my own yet, as I thought I'd write it in first
to see if I'm on track.

I've created a relational financial database for our military command. In
the beginning of the year, we receive our annual budget and spread it to
different programs. Based on changing priorities, funding will get moved
from one program to another throughout the year and these changes need to be
captured as separate entries, so that an audit trail can be tracked. So
right now the database has a record for each program and an amount assigned
as a 'startpoint'.

So here's my hypothetical situation. Say each programs supply line needs to
be reduced by 10%. I should be able to query the table for 'supply', and in
a new field enter an expression(?) = [amount]*.-10. Then have these records
append to the original table so that I would end up with two records similar
to below:

program amount
MILSTAR 100 (this would be the original record)
MILSTAR -10 (this would be the appended record)

Thus when I total MILSTAR, I have 90.

Here's the catch...I need to be able to let the user enter the change, ie
-10% or +25% and select the category (supplies, or travel, or training etc).

So is this difficult?

I'll be happy to post database details as requested.

Laura

 

[Guest]

Hello,

The structure is there already and your ideas helped me out. I did it with
4 steps. I created a query to select the files and apply the percentage
change using the parameter method as you suggested. Step 2 involved an
update query to assign the audit and remarks, Step 3 involved a form to
confirm the total of the transaction amount. I wanted the form to include the
% I used, but could figure out how to say it. Right now it reads:

You are making an change to [auditfield] (either reads increase or decrease)
with a remark of [rmksfield] for a total change of [sum([change]).

And step 4 involves appending the records to the main table. Works rather
nicely, but need to do a few more tweaks.

Intend to use a macro to combine all the steps and put a button on a form.

Does this seem an efficient way (I don't code yet)?

Thanks
Laura

It seems like your database would need a bit more structure, such as a
create date. If you are creating an audit trail then it would probably
be best to base all your calculations on the last entry, not the first
entey (startpoint). This kind of entry can be created with a query but
you are probably better off creating an entry form based on the query
for end-users. You should also add an autonumber key field to identify
rows.

This is an attempt at a query, notice the parameters for the user to
enter the desired section and calculation:

insert into supply (program, amount)
select [enter program name] as program, ([enter percent change from
last entry]*a.amount)+a.amount as amount
from supply a inner join (select program, max(create_date) as last_date
from supply group by program) b
on a.program = b.program and a.create_date = b.last_date

hope that helps.

Hello all,

Working with access 2003, consider myself an intermediate user, but no (very
limited) programming skills.

I haven't attempted this on my own yet, as I thought I'd write it in first
to see if I'm on track.

I've created a relational financial database for our military command. In
the beginning of the year, we receive our annual budget and spread it to
different programs. Based on changing priorities, funding will get moved
from one program to another throughout the year and these changes need to be
captured as separate entries, so that an audit trail can be tracked. So
right now the database has a record for each program and an amount assigned
as a 'startpoint'.

So here's my hypothetical situation. Say each programs supply line needs to
be reduced by 10%. I should be able to query the table for 'supply', and in
a new field enter an expression(?) = [amount]*.-10. Then have these records
append to the original table so that I would end up with two records similar
to below:

program amount
MILSTAR 100 (this would be the original record)
MILSTAR -10 (this would be the appended record)

Thus when I total MILSTAR, I have 90.

Here's the catch...I need to be able to let the user enter the change, ie
-10% or +25% and select the category (supplies, or travel, or training etc).

So is this difficult?

I'll be happy to post database details as requested.

Laura

 

[Guest]

This was not the most efficient way. Have managed to combine al steps into
one query - whoo- hoo.

It seems like your database would need a bit more structure, such as a
create date. If you are creating an audit trail then it would probably
be best to base all your calculations on the last entry, not the first
entey (startpoint). This kind of entry can be created with a query but
you are probably better off creating an entry form based on the query
for end-users. You should also add an autonumber key field to identify
rows.

This is an attempt at a query, notice the parameters for the user to
enter the desired section and calculation:

insert into supply (program, amount)
select [enter program name] as program, ([enter percent change from
last entry]*a.amount)+a.amount as amount
from supply a inner join (select program, max(create_date) as last_date
from supply group by program) b
on a.program = b.program and a.create_date = b.last_date

hope that helps.

Hello all,

Working with access 2003, consider myself an intermediate user, but no (very
limited) programming skills.

I haven't attempted this on my own yet, as I thought I'd write it in first
to see if I'm on track.

I've created a relational financial database for our military command. In
the beginning of the year, we receive our annual budget and spread it to
different programs. Based on changing priorities, funding will get moved
from one program to another throughout the year and these changes need to be
captured as separate entries, so that an audit trail can be tracked. So
right now the database has a record for each program and an amount assigned
as a 'startpoint'.

So here's my hypothetical situation. Say each programs supply line needs to
be reduced by 10%. I should be able to query the table for 'supply', and in
a new field enter an expression(?) = [amount]*.-10. Then have these records
append to the original table so that I would end up with two records similar
to below:

program amount
MILSTAR 100 (this would be the original record)
MILSTAR -10 (this would be the appended record)

Thus when I total MILSTAR, I have 90.

Here's the catch...I need to be able to let the user enter the change, ie
-10% or +25% and select the category (supplies, or travel, or training etc).

So is this difficult?

I'll be happy to post database details as requested.

Laura