Query calculations across different records. Is it possible?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is challenging for me. I hope some in this community can help.

We are trying to convert a rather complex XL spreadsheet of financial
investments into a database. The reason is that the system has to be more
robust and structured. People have been entering formulae and macros (many
are undocumented and don't work any more) and the spreadsheet is now very
unreliable.

If we convert to Access, we would import the current verified data as static
tables and then every month, a number of new records would be appended from
downloads. This is no problem.

The problem is that the vast majority of the current calculations in XL
invoke numbers coming from all over the place and across sheets, both with
relative and absolute references.
So, while on one hand we could reproduce the formuale in Access, on the
other (as far as I know), calculations in a query only work within the same
line record and I don't think that we can recreate an XL calculation such as
S24 = D10*C55+$F$15/365, i.e. taking numbers from discontinuos fields above
and below the current record.

Is there a practical way of addressing this issue, without having to write
horrendously complicated code?
Thank you everybody in advance for any creative suggestion.
 
Is there a practical way of addressing this issue, without having to write
horrendously complicated code?
Click to expand...

Only by rationalizing the calculations.

Databases *are not spreadsheets* - just for one thing, there ARE no
"cell references", and records (unlike rows in a spreadsheet) have no
defined order. Trying to duplicate spreadsheet functionality -
especially using spreadsheet logic! - will be a nightmare of
frustration!

As painful as it may be, you'll do better to sit down and come up with
a FUNCTIONAL specification of what the application is supposed to do,
and implement it in Access (or perhaps implement it *correctly* in
Excel, if that turns out to be more appropriate). You can use the
existing spreadsheet as a guide and as examples of what it should
do... but don't try to follow it too slavishly, a lot of what's there
may be "ad hoc" solutions to one-time problems which are no longer
relevant, or complex getarounds to problems stemming from the initial
data structure.

That's going to be a TOUGH problem... good luck!

John W. Vinson[MVP]
 
Back
Top