Access - Excel - Access

[Jacinda]

Does this make sense?

We have an existing quote/ design program written in Excel. It uses quite a
few complicated calculations, including Matrix look-ups that I have not been
able to duplicate in Access. This program however does not store the end
result data.

So what I want to do is set up a form in access where the user can enter the
data to be calculated - behind the scenes I want the data to go into Excel
calculate my end result, and then pull the data back into Access on another
table.

We are trying to keep our users in one program as opposed to going from
Excel to Access...

My plan was to have the form feed a table or query, then in excel drop those
values into my calculation fields, then create a named range that pulls the
results that can be imported back into access.

In therory, this should work right? but does it make sense to do it this way?

 

[Douglas J. Steele]

What are the Matrix look-ups that you haven't been able to duplicate in
Access?

While what you're describing is possible, it will likely be slow. And as you
increase the complexity of any application, you increase the potential for
problems with it!

 

[Jacinda]

The maxtrix are used for design... and it accounts for rounding up or down...
For example:

0 2 4 6 8
0.001 2 2 3 4
0.002 0.002 0.002 0.003
0.023 0.002 0.003 0.004
0.035 0.002 0.004 0.006
0.051 0.003 0.005 0.007


The top reprents a wire type and the left represents the coil diameter...
when the use enters .025 as the diameter they choose and a wire type 3... we
have this designed to give the result of .004- (it can not be a fixed option)

We have several of these and these reuslts are plugged into a calculation.

I know Excel is designed for these types of calculations, that is why I want
to continue to use it. Rewriting it in access does not appear to be the mose
efficent way to handle this.

Would it still be slow if we only used one row in and one row out... instead
of adding rows in excel?
-Jacinda

 

[Douglas J. Steele]

I think rewriting that for use in Access would indeed be efficient. I just
can't figure out your data to show you how to do it!

Take a look at the article by Tom Ellison that Allen Browne has at
http://www.allenbrowne.com/ser-58.html

While one row in/one row out would likely be quicker, the issue is the time
spent interchanging information between the applications. I also believe
you're introducing unnecessary complexity which could make the application
less reliable, and more difficult to maintain.

 

[Jacinda]

Thank you for the article. I read through this, and this is close to what I
would need, but if I am reading this right, he is saying that this query will
not round to the nearest value.

My users are engineers... so if the diameter of a spring is 1.5648, that is
what they will enter... Excel is friendly with rounding and finding the right
value .... I really just need a way to store the end result.

 

[John Spencer]

You need to change your matrix to a table that is tall and thin.
Something that would probably have three fields. You would end up with one
record for each intersection in your table

WireType
CoilDiameter
TheValue

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Douglas J. Steele]

Access can round just as well as Excel.

You need to design your table such that if they type in 1.5648, your query
would retrieve the specific row to which 1.5648 corresponds. If it's easier,
use the intial approach Tom shows, with BracketLow and BracketHigh. Your
query would retrieve the row WHERE 1.5648 BETWEEN [BracketLow] AND
[BracketHigh] (or, if you prefer, WHERE [BracketLow] <= 1.5648 AND
[BracketHigh] >= 1.5648)

 

[John... Visio MVP]

I provide help with Access applications for a modest fee. If you would
like my help setting up the calculations in Access keeping your users in
one program, contact me at (e-mail address removed).

Steve

These newsgroups are provided by Microsoft for FREE peer to peer support.
Steve is a known troll who pretends to offer help, but from his examples
posted in these newsgroups he has proven many times he does not know what he
is talking about. Even his free help is over priced.

John... VIsio MVP