By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,500 Members | 2,809 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,500 IT Pros & Developers. It's quick & easy.

Database Mess

P: 38
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
Aug 27 '08 #1
Share this Question
Share on Google+
4 Replies


FishVal
Expert 2.5K+
P: 2,653
...
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
Aug 27 '08 #2

100+
P: 167
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.
Aug 27 '08 #3

P: 38
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
Aug 27 '08 #4

NeoPa
Expert Mod 15k+
P: 31,494
...
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
Expand|Select|Wrap|Line Numbers
  1. RateID  String(1)  PK
  2. Rate    Numeric
Some simple contents :
[tblRates] Data
Expand|Select|Wrap|Line Numbers
  1. RateID  Rate
  2.   A       0
  3.   B       5
  4.   C      10
  5.   D      25
Table Name = [tblProducts]
Field Type Index
Expand|Select|Wrap|Line Numbers
  1. ProdCode  String      PK
  2. ProdDesc  String(50)
  3. Price     Numeric
  4. VATCode   String(1)   FK
Some simple contents :
[tblProduct] Data
Expand|Select|Wrap|Line Numbers
  1. ProdCode  ProdDesc                   Price  VATCode
  2. AC1000    Paper ...                   3.56     A
  3. AC2000    Paper ...                   4.90     A
  4. BR1040    Office Equipment ...       34.12     B
  5. BZ2010    Office Equipment ...       55.00     B
  6. CD1000    Electronic Consumable ...   9.10     C
  7. CX9800    Electronic Consumable ...  13.49     C
  8. DD0560    Luxury Item ...           135.00     D
  9. 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.
Expand|Select|Wrap|Line Numbers
  1. SELECT tP.ProdCode,
  2.        tP.ProdDesc,
  3.        tP.Price,
  4.        tR.Rate AS VATRate,
  5.        tP.Price * (100 + tR.Rate) / 100 AS VATPrice
  6.  
  7. FROM tblProduct AS tP INNER JOIN
  8.      tblRates AS tR
  9.   ON tP.VATCode=tR.RateID
Giving :
[qryProduct] Results
Expand|Select|Wrap|Line Numbers
  1. ProdCode  ProdDesc                   Price  VATRate  VATPrice
  2. AC1000    Paper ...                   3.56     0         3.56
  3. AC2000    Paper ...                   4.90     0         4.90  
  4. BR1040    Office Equipment ...       34.12     5        35.83
  5. BZ2010    Office Equipment ...       55.00     5        57.75
  6. CD1000    Electronic Consumable ...   9.10    10        10.01
  7. CX9800    Electronic Consumable ...  13.49    10        14.84
  8. DD0560    Luxury Item ...           135.00    25       168.75 
  9. DM3320    Luxury Item ...           500.00    25       625.00
Sep 1 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.