TABLE LOOKUPS FROM PRICE MATRIX

[Gigi.com]

Hi All.
I need some help trying to pull prices from a price matrix. Here's an
example:

1000 1500 2000 2500

-----------------------------------------
1000 ¦ 10.20 11.95 12.55 13.76

1500 ¦ 11.23 12.23 13.45 14.45

2000 ¦ 12.99 13.56 14.71 15.62
Let me explain. The header row is millimetres, and indeed relates to
the width of a window. The first column is also in millimetres, and
relates to the height of a window. The data are prices in GBP.

From this table, I can work out the price of a window depending on
it's size. For example, a window of 1000w x 1500h is £11.23. A
window of 1100w x 1500h would be £12.23.(This is because the width of
the window breaks the 1000mm price band, and goes into the 1500mm
price band.)

Okay, so far so good. Now then... up until now, in order to get
access 2k to pull this information out of a table format, I have had
to create a table with three fields in a standard access list table
format. The field names are: WIDTH, HEIGHT, PRICE.

I extract the table using a SELECT SQL statement, and it works fine.
(ie - SELECT PRICE from PriceTable WHERE WIDTH >=myWidth AND HEIGHT=myHeight etc). My problem is that I need to allow the user to

create a new price table, and enter the data into an excel style grid
(as shown above), but I just can't work out how I'm supposed reference
the data, or indeed store the data from this format.

If I want to view my standard access table in matrix format, I simply
use a crosstab query to do so. The problem with crosstabs are that
they are not editable, and that still wouldn't answer my question on
how I'm supposed to allow the user to enter the data in the first
place.

I'm aware of the INDEX/MATCH functions from excel, but it's long
winded, and of course relies on calling Excel Dll's.

I've thought about the possiblility of creating a table with say 10
fields, named HEIGHT, WIDTH_1, WIDTH_2 etc etc.... And having the
first row as my width breakpoints, but I can't see how I would
reference that from SQL statements.

Can somebody please help me??

Thanks

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Just have a form that has the columns (fields) set up the same as the
table's. Why can't a user understand that? It is just as easy to
understand as a matrix (which may expand off the visible part of the
display screen). Just be sure the Height & Width columns are marked as
Primary Keys so no duplicates can be entered.

You could have the above set up & a button on the form that shows the
user the cross-tab results (matrix) of the data they just entered, if
they wanted to see it that way.

If you insist on having a matrix input form:

The form will have to be unbound.
When the user is finished - click a save button.
The save routine will go thru each cell & insert/update the table
records, 1 record/cell at a time. Lots of VBA coding.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIhKeYechKqOuFEgEQI7cACffz+jy47tkIj5Y8QtYpskFg YxVQUAoJ3f
gpxr6C0wWMN6aymyV7rWNwkQ
=efx+
-----END PGP SIGNATURE-----
Gigi.com wrote:

Hi All.
I need some help trying to pull prices from a price matrix. Here's an
example:

> 1000 1500 2000 2500

-----------------------------------------
1000 ¦ 10.20 11.95 12.55 13.76

1500 ¦ 11.23 12.23 13.45 14.45

2000 ¦ 12.99 13.56 14.71 15.62
Let me explain. The header row is millimetres, and indeed relates to
the width of a window. The first column is also in millimetres, and
relates to the height of a window. The data are prices in GBP.

From this table, I can work out the price of a window depending on
it's size. For example, a window of 1000w x 1500h is £11.23. A
window of 1100w x 1500h would be £12.23.(This is because the width of
the window breaks the 1000mm price band, and goes into the 1500mm
price band.)

Okay, so far so good. Now then... up until now, in order to get
access 2k to pull this information out of a table format, I have had
to create a table with three fields in a standard access list table
format. The field names are: WIDTH, HEIGHT, PRICE.

I extract the table using a SELECT SQL statement, and it works fine.
(ie - SELECT PRICE from PriceTable WHERE WIDTH >=myWidth AND HEIGHT

=myHeight etc). My problem is that I need to allow the user to

create a new price table, and enter the data into an excel style grid
(as shown above), but I just can't work out how I'm supposed reference
the data, or indeed store the data from this format.

If I want to view my standard access table in matrix format, I simply
use a crosstab query to do so. The problem with crosstabs are that
they are not editable, and that still wouldn't answer my question on
how I'm supposed to allow the user to enter the data in the first
place.

I'm aware of the INDEX/MATCH functions from excel, but it's long
winded, and of course relies on calling Excel Dll's.

I've thought about the possiblility of creating a table with say 10
fields, named HEIGHT, WIDTH_1, WIDTH_2 etc etc.... And having the
first row as my width breakpoints, but I can't see how I would
reference that from SQL statements.

[Gigi.com]

MGFoster <[e-mail]> wrote in message news:<OT****************@newsread2.news.pas.earthl ink.net>...
Thanks for the concept. That idea would work fine. Unfortunately,
the data must be entered like this becuase it's very much an industry
standard way of view price tables. It also makes it very easy to copy
data from a price table.
If you can help me with any code it would be appreciated.

Cheers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Just have a form that has the columns (fields) set up the same as the
table's. Why can't a user understand that? It is just as easy to
understand as a matrix (which may expand off the visible part of the
display screen). Just be sure the Height & Width columns are marked as
Primary Keys so no duplicates can be entered.

You could have the above set up & a button on the form that shows the
user the cross-tab results (matrix) of the data they just entered, if
they wanted to see it that way.

If you insist on having a matrix input form:

The form will have to be unbound.
When the user is finished - click a save button.
The save routine will go thru each cell & insert/update the table
records, 1 record/cell at a time. Lots of VBA coding.

[James Fortune]

gi*******@yahoo.co.uk (Gigi.com) wrote in message news:<f5**************************@posting.google. com>...

MGFoster <[e-mail]> wrote in message news:<OT****************@newsread2.news.pas.earthl ink.net>...
Thanks for the concept. That idea would work fine. Unfortunately,
the data must be entered like this becuase it's very much an industry
standard way of view price tables. It also makes it very easy to copy
data from a price table.
If you can help me with any code it would be appreciated.

Cheers

This is a very interesting problem. It's much tougher than it looks
to make Access do too much Excel stuff. Some other threads in this NG
talk about using Excel within Access. Since Excel was likely used by
the people who created the industry standard it seems a
non-Access-only solution might be in order. I've had success with
Excel automation from Access but have never tried to embed an Excel
object within Access. Of course having Access emulate Excel
capabilities, though a bit of a kludge, is much better than trying to
use Excel as a database :-). If your Height and Width values vary for
different kinds of windows or your price matrix is over about 15 X 15
then I think you're trying to push Access to do something that is done
better by Excel. Finding prices from the spreadsheet is similar to
what you use currently in SQL except you are traversing the first row
and column instead of Width and Height values in an Access table. It
would be really nice if Access actually had native two and three
dimensional tables that could easily map to arrays. A 3D interface to
change the values would also be nice. Just click up or down to see
the next 2D layer. Er.., click right or left to see the next 2D
layer. Er.., click shallower or deeper to see the next 2D layer.

James A. Fortune

Being poor is best left to those who have no money.
- Taxi