Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2007
    Posts
    16

    Cool Unanswered: Matching data in fields

    Hi

    I am new to access and this forum, I need help with the following.

    I have a table which is to be updated regularly. The table1 only has one field which contains the:
    Car manufacturer, Model, Mileage and Rental price
    In the following format
    BMW X5 on business plan 3250 (12 Month). 1000 Miles. ¶œ600.00 Rental

    Table2 has the following fields
    Car manufacturer
    Model
    Description

    Can some one please tell me the best way to match car manufacturer and model between the 2 tables so the database can display the description in the report?

    Any will be appreciated.
    Last edited by Weebot; 07-07-07 at 15:25.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Weebot

    My first thought was that table 1 was a staging table from a data load but it sounds like you are using it as a genuine table.

    Why isn't the data split out into their proper fields?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2007
    Posts
    16

    The data is imported

    I will split the data I was thinking if there was anyway to match the data without splitting the data. Can you please tell me how to match the data to table two after it has been split.

    e.g. how do i match BMW X5 in table1 to BMW X5 in order to get the description in table2.

  4. #4
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Yes, I can help. Break the field in the first table into individual fields. Once you have all the data in it's own field, then you will be able to match the CarManufacturer and Model from Table1 to Table2 and use the Description from Table2 in a report. Actually, the one field in Table1 looks like it has 5 pieces of data in it. CarManufacturer, Model, whatever "on business plan 3250 (12 Month)." is, mileage, and RentalPrice.

    If you just don't want to break the one field into the individual fields, you could create a query that would give you CarManufacturer, Model, and EverythingElse. Then you could match the CarManufacturer and Model from this query with the CarManufacturer and Model from Table2 to get the Description to show on your report. To get the CarManufacturer, use this:
    Mid(OneField, 1, Instr(OneField, " ") - 1)
    To get the model, use this:
    Mid(OneField, Instr(OneField, " ") + 1, Instr(Instr(OneField, " ") + 1, OneField, " ") - Instr(OneField, " ") - 1)
    And yes, I did it all in one statement to show why we have a separate field for each piece of data. And, unless you want to turn this into a function to be called from the query, it has to be in one statement.

    If there is something about these two lines of code, please review the Access help file on the use of the Mid() and Instr() functions, then ask whatever question(s) you need help with.

    Again, I highly recommend you turn that one field table into the separate fields that are represented by the data in that OneField.

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Ops, looks like while I was working on a reply, Pootle started one. Hope you can see how this helps.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by GolferGuy
    Again, I highly recommend you turn that one field table into the separate fields that are represented by the data in that OneField.
    Totally agreed.

    Apart from any other concerns, your queries will perform poorly if you keep the data like this. It is fine if this is imported data - you would call this a "staging table". However, the staging table is an intermediary to moving the data into a proper table in the database, with properly defined fields, datatypes, keys etc..

    Then querying is simple:
    Code:
    SELECT Table_1.x, Table_1.y, Table_2.z
    FROM Table_1 INNER JOIN Table_2 ON Table_1.x = Table_2.x
    An excellent SQL tutorial:
    http://www.w3schools.com/sql/default.asp
    An excellent introduction to database design:
    http://r937.com/relational.html
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jul 2007
    Posts
    16
    Hi

    Thanks for you replies everyone

    Now that I have split the data into different fields need to know how the data can be matched.
    e.g. Match BMX X5 in table1 to BMW X5 in table2 so i can get the description from table2. BMW and X5 are in different columns in table2.

    All different manufacturer cars are listed in different tables. Please can you let me know how these can be matched, Audi cars have their descriptions listed in table3 and so on....

  8. #8
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    It would be better to have all your car manufacturers details in table 2. Otherwise it would be a nightmare to show the link between a car in table 1 to the relevant table. Each time you added a manufacturer you would need to create a link etc.

    By having the ALL manufacturers in on table all you have to do, is do a lookup for the manufacturer and model in table 2 to get the description etc.

  9. #9
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Please follow the link in my signature that is titled Access Design Tips. Read the first article about Normalization. You will be FOREVER fighting with your database trying to get things to work if you don't normalize your data, and to normalize your data, you need to do some studying up front.

    One of the first things you can learn about normalizing data is that every piece of data needs to be in it's own column (field). You keep refering to a BMW X5. That needs to be two columns. The BMW (Manufacturer) in one column, and the X5 (Model) in it's own column. And, you will also see that ALL manufacturers need to be in one table, just as Poppa Smurf has suggested.

    The article about normalization that I suggested at the top of this post is the same article (just a different location) that pootle flump suggested.
    Quote Originally Posted by pootle flump
    An excellent introduction to database design:
    http://r937.com/relational.html
    I would highly recommend you do some study in this subject before going too much further. The dividends from the study of normalization are more than what time you will invest.

Posting Permissions

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