Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2008
    Posts
    3

    Unanswered: Database key problem

    Hi! Im new to database syntax so please bear with me

    I want to create a third table or a report into xls based on two tables.

    I have two tables with car data (the keys are marked in bold):
    Table1: ModelCode, VIN
    Table2. ModelCode,ModelName
    (VIN = Vehicle Identification Number)

    All tables are retrieved from a ODBC connection from an external DB2 database.

    I want to create a third table(or a xls sheet) that adds following information from Table1: ModelName
    => New table/xls sheet with following information:
    VIN, ModelCode, ModelName
    Hence, the new table/xls sheet has added ModelName into Table1 from Table2.

    Problem
    Table2 have 1-4 different ModelName for each ModelCode. If i join the two tables I will get a list with 1-4 different ModelName for each VIN.

    Note that any one of the ModelName for each ModelCode in Table2 are valid to use.

    Question
    How can I make the third table without multiple entries for each VIN. I only want one ModelName for each VIN.

    Limitations
    - Can not locally store Table1 since it is to large to update frequently
    - Only have ODBC connection to database


    Non viable solutions
    1. Export Table1 and Table2 into two xls sheets. Use query to limit amount of data since Table1 is huge. Use VLOOKUP in xls to retrieve ModelName. This works since VLOOKUP only returns the first matching "hit" => extensive manual work
    2. Create a new locally stored table that contains ModelCode and ModelName without double entries. Join Table1 and new table. => extensive manual work

    I hope someone can help me !


    /Teo

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    *sniff* *sniff*

    Smells like homework to me.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's not homework, that's a typical real-world cock-up

    why would the same vehicle have multiple models?

    every car has a unique VIN, so if there are multiple models per VIN, somebody gonna get a hurt real bad

    solution: take the MAX(modelname) with a GROUP BY on VIN

    yes it's arbitrary, but just as correct as any other solution

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2008
    Posts
    3

    Problem2

    Factory do not change model code if they change a small detail on the car while this change could be a selling point for customer, hence change of model name while model code is the same.

    As a result, depending on what time the vehicle is sold the model name is sometimes different.

    However, the problem is that Table2 saves historical data. So every change of model name for a model code is saved.=> Headache for me

    Now I need a even better solution to my problem. Based on dates I want the correct model name.

    Problem2
    Table1 also contain the wholesale date
    Table2 also contain the date change of ModelName for certain ModelCode
    =>
    Table1: ModelCode, VIN, WSDate
    Table2. ModelCode,ModelName,ChangeDate
    Sample:
    Table1:
    BSGGA123 KNEUP123456789100 02022008
    BSGGA123 KNEUP123456789111 03032008
    BSGGA111 KNEUP123456789122 04042008
    Table2:
    BSGGA123 Volvo 1.0 BASIC 01022008
    BSGGA123 Volvo 1.0 SPORT 01032008
    BSGGA111 Audi 1.0 ABS++ 01042008
    As you can see the ModelCode BSGGA123 is stated twice but for different dates. Moreover, the ModelName has also changed

    How do i create a third table with following information from Table1 and 2:

    ModelCode,VIN,WSDate,ModelName
    BSGGA123 KNEUP123456789100 02022008 Volvo 1.0 BASIC
    BSGGA123 KNEUP123456789111 03032008 Volvo 1.0 SPORT
    BSGGA111 KNEUP123456789122 04042008 Audi 1.0 ABS++

    /Teo

    Quote Originally Posted by r937
    that's not homework, that's a typical real-world cock-up

    why would the same vehicle have multiple models?

    every car has a unique VIN, so if there are multiple models per VIN, somebody gonna get a hurt real bad

    solution: take the MAX(modelname) with a GROUP BY on VIN

    yes it's arbitrary, but just as correct as any other solution


  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by r937
    every car has a unique VIN, so if there are multiple models per VIN, somebody gonna get a hurt real bad
    if a car has multiple VIN's, then its probably been the beneficiary of some TLC from a "dodgy dave" and is a cut & shut


    but seriously why would you want to create a third table?.. do you mean you want to get the data form the two tables and merge the results in a query so you can use that in a report? if so have a look at the query browser.. create a new query

    personally I'd argue that a vehicle manufacturer or car distributor system woudl have a differnet entity list

    I'd expect to have a table for a car manufacturer (up to you could be say Ford, GM, Toyota, Honda etc al, or it could be the car brand eg Ford, Jaguar, Land Rover, Volvo
    of you could do both and identify the manufacturer in one entity, identify the marque in another entry (referring back to the manufacturer table so you would know which manufacturer owned which marque)

    Id then expect a model, identifying say that Ford made the Escort, Capri or whatever, possibly identfying the different models here, eg Escort Mark 1 1967.....
    having identified the

    I'd then expect to define the different versions within the range, eg 2 door, 4 door, petrol, diesel, trim level

    however I get the impression that most car makers change the trim detail annually.. os it almost begs the question do you need a detail for that.
    or do you just have a date started for this trim version.

    eg Freelander 2008 5TDSi introduced blah.......
    how many tables do you need for your assignment?

  6. #6
    Join Date
    Mar 2008
    Posts
    3
    Answer1:
    A third table is not a must. It could be a report/query as well.
    I dont know how to make the query so the wanted data is retrieved...
    Answer2:
    Im gonna use two tables to create the "report"/query.


    Previous post gave me a solution by using MAX(), however the result wouldnt be 100% correct. Other solution would be to use LAST() so the most recent entry of the model name is used, this as well is not 100% correct, but slightly better.

    So now im still looking for the 100% correct solution...

    /Teo


    Quote Originally Posted by healdem
    if a car has multiple VIN's, then its probably been the beneficiary of some TLC from a "dodgy dave" and is a cut & shut


    but seriously why would you want to create a third table?.. do you mean you want to get the data form the two tables and merge the results in a query so you can use that in a report? if so have a look at the query browser.. create a new query

    personally I'd argue that a vehicle manufacturer or car distributor system woudl have a differnet entity list

    I'd expect to have a table for a car manufacturer (up to you could be say Ford, GM, Toyota, Honda etc al, or it could be the car brand eg Ford, Jaguar, Land Rover, Volvo
    of you could do both and identify the manufacturer in one entity, identify the marque in another entry (referring back to the manufacturer table so you would know which manufacturer owned which marque)

    Id then expect a model, identifying say that Ford made the Escort, Capri or whatever, possibly identfying the different models here, eg Escort Mark 1 1967.....
    having identified the

    I'd then expect to define the different versions within the range, eg 2 door, 4 door, petrol, diesel, trim level

    however I get the impression that most car makers change the trim detail annually.. os it almost begs the question do you need a detail for that.
    or do you just have a date started for this trim version.

    eg Freelander 2008 5TDSi introduced blah.......
    how many tables do you need for your assignment?

  7. #7
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Use two queries. The first query is a group by with ModelCode (GroupBy) and Date (Max). The sencond query uses both tables and the first query and links the two fields from the query to the corresponding fields in each table.

Posting Permissions

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