435,080 Members | 1,845 Online
Need help? Post your question and get tips & solutions from a community of 435,080 IT Pros & Developers. It's quick & easy.

# TABLE LOOKUPS FROM PRICE MATRIX

 P: n/a 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 Nov 12 '05 #1