Database Mess

I'm creating a new database for an Insurance Firm. I really thought I knew how to do this, but I need some advice. They currently have a "tool" that was created in Excel that works, but is so convoluted and hard to follow (and update) that the decision was made to put it in Access. Right now, they create by using the tool then save it in a folder. We want to be able to keep records and report off the records in Access.

I started the database by going through the Excel tool and pulling out all of the lookup tables and putting them in tables in Access. I sat with the people who currently use the tool to find out what problems they were having and what enhancements they would like to see. I have everything I need, I just have one problem. I can't seem to pull the rates so that I can use them in calculations. I think there may be a better way to set this up so that I can pull the information that I need, but I have reached a mental block and no matter how much I think about it or try to get it to work, I come back to the same roadblocks.

I'm ready to start over if I have to. I just need to know the appropriate way to set up the data so I can use it to do what we need. I've never had problems like this before, and I have set up many applications in the past.

Is there anyone out there who can help me?

I could email the tool and what I have done so far. It's hard to explain over email, but I'm willing to give that a shot too.

Thanks,

Kelly

re: Database Mess

Hello, Kelly.

Could you please explain how those rates are supposed to be obtained.

Regards,
Fish

re: Database Mess

Kelly I sent you PM.
You can send me the tool and database to have a look.
I have some extra time so I could try to find something helpfull.

re: Database Mess

Thank you so much! Both are on the way.

Kelly

re: Database Mess

The usual way to extract rates from a (presumably tblRates) table is that they would be identified by some ID value.

The ID value is stored in the records of other tables where the rate is needed then the data is accessed via a query. In its simplest form you might have something like :
Table Name = [tblRates]
Field Type Index
Some simple contents :
[tblRates] Data
Table Name = [tblProducts]
Field Type Index
Some simple contents :
[tblProduct] Data
To expand this to show the rate and the increased price you would join the two tables using a query.
Giving :
[qryProduct] Results