Excel Linking

[Mark T.]

Hi,
I've got a simple DB (Office Win XP Professional).
Basically, it's like a check book register - deposit, deduction and running
sum.
I'd like to somehow link it to an Excel spreadsheet that will do the
calculations and then keep the data updated in the form
and report in the DB. Apparently this isn't done in Access because
the "cell" where you enter the data is actually a field. I have a tabular
form that looks just like a checkbook register and I'd like each
individual record to show the change that occurs 'for that record'
in real time, as the data is entered.
I've tried Tools / Office Links / Analyze it with Excel, but without
a dynamic formula in the spreadsheet, nothing happens and there is
no link established to update the data.
Ideas? Tips?
Thank you!
Mark

[PC Datasheet]

Re: Excel Linking

Mark,

Try basing your form on a query and in the query use calculated fields. You'll
get the effect you want this way. Linking to Excel won't give you the effect you
want!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdata sheet.com

https://www.pcdatasheet.com

"Mark T." <turnermark@ver izon.net> wrote in message
news:bhoBc.1832 7$Yb1.10440@nwr ddc02.gnilink.n et...[color=blue]
> Hi,
> I've got a simple DB (Office Win XP Professional).
> Basically, it's like a check book register - deposit, deduction and running
> sum.
> I'd like to somehow link it to an Excel spreadsheet that will do the
> calculations and then keep the data updated in the form
> and report in the DB. Apparently this isn't done in Access because
> the "cell" where you enter the data is actually a field. I have a tabular
> form that looks just like a checkbook register and I'd like each
> individual record to show the change that occurs 'for that record'
> in real time, as the data is entered.
> I've tried Tools / Office Links / Analyze it with Excel, but without
> a dynamic formula in the spreadsheet, nothing happens and there is
> no link established to update the data.
> Ideas? Tips?
> Thank you!
> Mark
>
>[/color]

[James Fortune]

Re: Excel Linking

"Mark T." <turnermark@ver izon.net> wrote in message news:<bhoBc.183 27$Yb1.10440@nw rddc02.gnilink. net>...[color=blue]
> Hi,
> I've got a simple DB (Office Win XP Professional).
> Basically, it's like a check book register - deposit, deduction and running
> sum.
> I'd like to somehow link it to an Excel spreadsheet that will do the
> calculations and then keep the data updated in the form
> and report in the DB. Apparently this isn't done in Access because
> the "cell" where you enter the data is actually a field. I have a tabular
> form that looks just like a checkbook register and I'd like each
> individual record to show the change that occurs 'for that record'
> in real time, as the data is entered.
> I've tried Tools / Office Links / Analyze it with Excel, but without
> a dynamic formula in the spreadsheet, nothing happens and there is
> no link established to update the data.
> Ideas? Tips?
> Thank you!
> Mark[/color]

It sounds like you only need Excel to do a calculation that Access can
also do. If you use a subform in datasheet view, you can have it
update your running sum whenever the user updates a value. The
running sum could be a text box on the main form. A nice touch is to
include a check box value that indicates when a check clears. You can
reconcile your bank statement by having another running sum that
subtracts all cleared checks from the sum of all deposits. If you
don't like waiting for the automatic calculations you can use a
command button to do the calculations when you are finished editing.

James A. Fortune

[James Fortune]

Re: Excel Linking

"Mark T." <turnermark@ver izon.net> wrote in message news:<bhoBc.183 27$Yb1.10440@nw rddc02.gnilink. net>...[color=blue]
> Hi,
> I've got a simple DB (Office Win XP Professional).
> Basically, it's like a check book register - deposit, deduction and running
> sum.
> I'd like to somehow link it to an Excel spreadsheet that will do the
> calculations and then keep the data updated in the form
> and report in the DB. Apparently this isn't done in Access because
> the "cell" where you enter the data is actually a field. I have a tabular
> form that looks just like a checkbook register and I'd like each
> individual record to show the change that occurs 'for that record'
> in real time, as the data is entered.
> I've tried Tools / Office Links / Analyze it with Excel, but without
> a dynamic formula in the spreadsheet, nothing happens and there is
> no link established to update the data.
> Ideas? Tips?
> Thank you!
> Mark[/color]

It sounds like you only need Excel to do a calculation that Access can
also do. If you use a subform in datasheet view, you can have it
update your running sum whenever the user updates a value. The
running sum could be a text box on the main form. A nice touch is to
include a check box value that indicates when a check clears. You can
reconcile your bank statement by having another running sum that
subtracts all cleared checks from the sum of all deposits. If you
don't like waiting for the automatic calculations you can use a
command button to do the calculations when you are finished editing.

James A. Fortune

[Mark T.]

Re: Excel Linking

Hi,
Thank you for the input.
However, I'm obviously new at this,
so are there any examples of how to make
this particular calculated field or query?
I've tried to type into the zoom box,
running sum-deduction+depos it
and what I finally wind up with is a grand
total for the entire DB apperaring in the running
sum "cell" for each record. It updates, but only
shows the total, not the changes caused by the
individual entry - deposit or deduction.
Thank you,
Mark