B
Bryan Linton
I'm a network administrator for an insurance agency with little experience
using Excel to access external data sources (like Access databases, etc).
I'm trying to accomplish a simple task, but efforts to figure it out on my
own have so far been unfruitful. I'm hoping someone here can point me in
the right direction.
We have a fairly complex spreadsheet in Excel that calculates experience
ratings used in worker's compensation insurance. Currently, the agent has
to look up a particluar Class Code in a printed table containing about 600
Class Codes to find and enter two corresponding values for that Class
Code -- they're called the ELR and the D-RATIO -- to be used in the
spreadsheet calculations. I'd like to automate this process. In other
words, I want the agent to simply type the Class Code in the appropriate
field in Excel, and have the corresponding ELR and D-RATIOS auto-populate
from an electronic table into their respective fields in Excel.
It's fairly simple conceptually, but I haven't been able to determine the
best way to do this. I created an Access database with a single, simple
table into which I entered the values for the three fields: Class Code, ELR,
and D-RATIO, with Class Code set to be the primary key. However, I haven't
figured out how to get Excel to query the Class Code in this table and
return the values from the other two fields into the spreadsheet. I've
created a query in the Database that I think would accomplish this. In
Excel, I go to Data > Get External Data > New Database Query and try to
browse to the database or to the query, but this gets me nowhere.
Am I making this more complicated than it needs to be? Any help is
appreciated. I have a feeling I might be going about this the wrong way,
but haven't found much help from online searches or the built-in help.
Btw, I'm using Office 2000.
Thanks,
Bryan
using Excel to access external data sources (like Access databases, etc).
I'm trying to accomplish a simple task, but efforts to figure it out on my
own have so far been unfruitful. I'm hoping someone here can point me in
the right direction.
We have a fairly complex spreadsheet in Excel that calculates experience
ratings used in worker's compensation insurance. Currently, the agent has
to look up a particluar Class Code in a printed table containing about 600
Class Codes to find and enter two corresponding values for that Class
Code -- they're called the ELR and the D-RATIO -- to be used in the
spreadsheet calculations. I'd like to automate this process. In other
words, I want the agent to simply type the Class Code in the appropriate
field in Excel, and have the corresponding ELR and D-RATIOS auto-populate
from an electronic table into their respective fields in Excel.
It's fairly simple conceptually, but I haven't been able to determine the
best way to do this. I created an Access database with a single, simple
table into which I entered the values for the three fields: Class Code, ELR,
and D-RATIO, with Class Code set to be the primary key. However, I haven't
figured out how to get Excel to query the Class Code in this table and
return the values from the other two fields into the spreadsheet. I've
created a query in the Database that I think would accomplish this. In
Excel, I go to Data > Get External Data > New Database Query and try to
browse to the database or to the query, but this gets me nowhere.
Am I making this more complicated than it needs to be? Any help is
appreciated. I have a feeling I might be going about this the wrong way,
but haven't found much help from online searches or the built-in help.
Btw, I'm using Office 2000.
Thanks,
Bryan