Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simplifying processes (A2002)

    Hi,

    I have just sat and copied and pasted about 4000 records (with about 28,000 to go) into a table before it dawned on me that it must be easier to do what I want to do. I have a tariff of rates that I want people to be able to look up by entering a country name and a weight and the result would be returned (Hans and Wendel kindly helped me out earlier on a problem I had with this).

    Anyway. what I have is about 80 countries and 9 different sets of rates( 0.50 - 20.00, therefore 40 rates per country in 9 rate bands), I have been typing in 20 rates for say a country in band 1, then copying and pasting it and changing the names in 40 records to another country, I thought I was being smart but it suddenly occured to me that I should make 9 tables with the wights & rates and a country table with the 80 countries and a lookup to their respective rates/weights.

    Is that right, or am I still looking at it wrong?

    Appreciate any comments (even rude ones)

    TIA

    Steve

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simplifying processes (A2002)

    I wouldn't create a different table for each rate band; that complicates things instead of simplifying them. Instead, do the following:
    - A table with rate band ID, weight and rate; this table will have 9 x 20 = 180 records: one for each rate band - weight combination.
    - A table with country and rate band ID; this table will have 80 records: one for each country.

    Create a relationship between the tables on the rate band ID field; enforce relational integrity.

    Note: why is weight a text field? I would make it a Number (Double precision) field. You can set the Format property to make it display a fixed number of decimals if you like. Sorting by weight is much easier if it is a number.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simplifying processes (A2002)

    Boy, am I glad I don't do this for a living!!

    Usual thanks Hans, however I don't think I quite got it, when you have a moment would you take a look at the attached zip and tell me (probably the obvious) what I am missing.

    Thanks

    Steve
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simplifying processes (A2002)

    In the first place, you had "rate" in the criteria row for the Rate field. Since Rate is a currency field, you can't use a string as criteria.

    In the second place, you had linked the rate band in the Country table to the autonumber field RateBandID. In fact, you don't need this autonumber field.

    See attached modified database. I changed rate band and weight to numeric fields.

    Note: if you want to add descriptions to the rate bands, the structure could be normalized one step further by having a separate rate bands table and weights table.
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simplifying processes (A2002)

    Thank Hans

    I could not have the RateBand as a Primary because there were 9 different rate bands to consider, likewise with the weight that too could not be a primary as I have 40 different weight increments between 0.5 kilos and 20 kilos. However using your example and just using RateBand and Weight as fields it worked great.

    By the way how did you make 2 primary fields because when I tried to literally copy your example to begin with PP2002 would not allow me to do this?

    Thanks again

    Steve

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simplifying processes (A2002)

    PP2002? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    You can make a composite primary key containing two fields by selecting the two fields in the table design window, then clicking the Primary Key button on the toolbar. Another way that works for other types of index too, is to enter the name of the index in the first column of the Indexes window, the name of the first field in the same row in the second column, and the name of the second field in the row below, leaving the first column empty.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •