Database Mess

[LSGKelly]

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

[FishVal]

...
I can't seem to pull the rates so that I can use them in calculations...

Hello, Kelly.

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

Regards,
Fish

[hjozinovic]

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.

[LSGKelly]

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.

Thank you so much! Both are on the way.

Kelly

[NeoPa]

...
I can't seem to pull the rates so that I can use them in calculations.
...

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

RateID  String(1)  PK
Rate    Numeric

Some simple contents :
[tblRates] Data

RateID  Rate
  A       0
  B       5
  C      10
  D      25

Table Name = [tblProducts]
Field Type Index

ProdCode  String      PK
ProdDesc  String(50)
Price     Numeric
VATCode   String(1)   FK

Some simple contents :
[tblProduct] Data

ProdCode  ProdDesc                   Price  VATCode
AC1000    Paper ...                   3.56     A
AC2000    Paper ...                   4.90     A
BR1040    Office Equipment ...       34.12     B
BZ2010    Office Equipment ...       55.00     B
CD1000    Electronic Consumable ...   9.10     C
CX9800    Electronic Consumable ...  13.49     C
DD0560    Luxury Item ...           135.00     D
DM3320    Luxury Item ...           500.00     D

To expand this to show the rate and the increased price you would join the two tables using a query.

SELECT tP.ProdCode,
       tP.ProdDesc,
       tP.Price,
       tR.Rate AS VATRate,
       tP.Price * (100 + tR.Rate) / 100 AS VATPrice
 
FROM tblProduct AS tP INNER JOIN
     tblRates AS tR
  ON tP.VATCode=tR.RateID

Giving :
[qryProduct] Results

ProdCode  ProdDesc                   Price  VATRate  VATPrice
AC1000    Paper ...                   3.56     0         3.56
AC2000    Paper ...                   4.90     0         4.90  
BR1040    Office Equipment ...       34.12     5        35.83
BZ2010    Office Equipment ...       55.00     5        57.75
CD1000    Electronic Consumable ...   9.10    10        10.01
CX9800    Electronic Consumable ...  13.49    10        14.84
DD0560    Luxury Item ...           135.00    25       168.75 
DM3320    Luxury Item ...           500.00    25       625.00