import 2-dimensional lookup table from Excel

[mattolson01]

I'm sorry if this is answered elsewhere, I promise I looked! Maybe I don't know the right terminology to find it.

--

I have a situation where someone regularly puts two-dimensional lookup tables into Access. If the table in Excel looks like this:

"Customer 'bob' Insurance Table"
           
        20lb.     50lb.
$400    1.50      2.50
$500    3.50      4.50

She creates (by hand) a table that looks like this:

cust |minVal |maxVal |minWgt |maxWgt   |cost
---------------------------------------------
bob     0.00  399.99   0.00      19.99  0.00
bob     0.00  399.99  20.00      49.99  0.00
bob     0.00  399.99  50.00   99999.99  0.00
bob   400.00  499.99   0.00      19.99  0.00
bob   400.00  499.99  20.00      49.99  1.50
bob   400.00  499.99  50.00   99999.99  2.50
---PLUS 3 MORE ROWS----

Then appends the new table into an Access table with customers "dave", "mary" and so forth. Essentially every cell in the lookup table has its own row in Access, and she runs queries to pick the appropriate value of "1.50" when "bob, $475, 35lbs." shows up in her source data.

Every customer has their own grid, per contract, which can be much larger than this. Any similarities in price or ranges would be a matter of coincidence.

I can't believe there's not a better way to do this...

the questions are:

* Does this thing in Access have a name so I can look for help next time?
* Can this process be automated at all?

Thanks, Matt

 

[BNPMike]

First off, I'm not sure you need to put in all the zero values eg
bob 0.00 399.99 0.00 19.99 0.00

Incidentally I would have thought $1.50 would be charged for lower weights, but that's by-the-bye.

As far as I can see you have a price table, so no special Access concept.

What I can't see is why you are changing from
bob, 400.00 , 20,1.50,40, 2.50
to
bob, 400.00 499.99 20.00 49.99 1.50

Both are ok - normalised for example.

What do you want to automate and why is the data in both excel and Access?

 

[mattolson01]

First off, the numbers are made up--this isn't actually about insurance, but another industry altogether. I just made up a 2-D lookup table to illustrate the idea. I didn't want to confuse the issue with all the other problems the real data has!

The row:

cust |minVal |maxVal |minWgt |maxWgt   |cost
---------------------------------------------
bob   400.00  499.99  20.00      49.99  1.50

represents the single CELL:

        20lb.     50lb.
$400    1.50      ----
$500    ----      ----

Both represent "if the item is worth between $400 and $500 and weighs between 20 lb and 50 lb, the charge is 1.50." Every cell in the original table has a row entry in the Access table, plus a row for each of the zero values (the way she is doing it, anyway).

So if she has a 10 x 10 table of values, that translates to 121 rows in Access (11 x 11 counting the zeros).

Now, she is doing that for about 3,000 clients, and all that data is put into a single "rates" table in Access. She does this by hand every time the rates change in a customer's contract.

Why Excel and Access? actually, she is working from a Word doc of the contract. she pastes the table into Excel, creates the Access-like table, and sends that new table to Access.

Anyway, hope that helps clarify. I'm still left with the question of whether there is a better way to do this in Access. Any takers?

Matt

 

[BNPMike]

Heh - don't complain - you're doing quite well. In a parallel universe there's someone having to put it into Powerpoint as well.

The obvious thing to do is to generate the price table in Access first. Then derive the document from that.

If you can't do that then at least paste the data in the source format. You stiil haven't explained why you change the format. Surely this is just a rod for your own back?

Beyond that, the volumes concerned do sort of suggest a professional approach ie programming in VBA.

 

[mattolson01]

This is most definitely a naive approach--she's a rookie and I'm an Excel jockey.

Mike, you ask the very question I am trying to discover the answer to. How _can_ you put this into Access in the original format?

If you think about it, the Excel table depends on treating the column HEADERS as long numbers so they can be compared to a lProductWeight value, and selecting the appropriate FIELD from the appropriate ROW (which is found from the product characteristic lProductPrice).

Since neither of us knows how to treat a column header as a number value, we don't know how to do this. But since she does know how to pick a row out of excel, she has modified the format as discussed above.

Sorry for the delay, just got back.