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

    Unanswered: Help needed to create such an SQL Query, a looong query?

    I am trying to create one long Sql query based on users selection, but keep getting confused, need some guidance. I have created a sample access database and here is my explanation, hope i make sense!

    Below is my brief explanation of the tables and fields

    tblAssessment have 4 Fields:
    UserID (PrimaryKey)
    CompanyName(this stores an ID that refers to the name in the dropdown table)
    SportName(This stores an ID that refers to the sportsname in the dropdown table)
    CountryName

    tblDropDown has three fields:
    DropDownID (Primary Key)
    ParentID
    Name

    The way tblDropDown is designed is that if the Field "Name" has data "CompanyName" and its DropDownID is "1", then
    all the companyNames will have ParentID "1", so fields Nike, Reebok and Puma will have parentID "1"

    tblSummary has three fields:
    CompanyName
    SportName
    CountryName
    This table has pure text data for all fields.

    I need to create an sql statement that takes all the selection of a User and update the CountryName field of tblAssessment.

    So for example if UserID "1", has CompanyName 2 (which is Nike, from DropDown table) and SportName 6 (which is Football from DropDown table) then lookup
    for them two selection from table tblSummary and get the CountryName and update tblAssessment's CountryName Field. So in this case it will be "England" as the tblSummary
    has CountryName "England" where SportName is "Football" and CompanyName is "Nike"

    Could someone plz guide me how to create such a query?

    I have attached a sample access database that mirrors the same above format.

    Thanks,
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2008
    Posts
    3

    uploaded the correct access file

    I am sorry, but I attached an incomplete access file, I forgot to enter tblSummary details. Here is the updated file

    Thanks
    Attached Files Attached Files

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This looks like a variation of OTLT and maybe EAV (I think it is OTLT with a second table for the values though - more like TTLT ).

    If this makes no sense try googling for OTLT. I will nudge Rudy and ask him to post his library of resources on this.

    My $0.02 - your problem is not writing SQL, it is getting round this design. I made a OTLT once - never again. My advice is just design a standard, normalised design.

  4. #4
    Join Date
    Feb 2008
    Posts
    3
    Yes I can use OTLT, thats why I have all the dropdowns with ids as a table, I have found one solution where I have a query for all the colums in dropdowns table and then construct the query but this will be creating 12 queries as my main database has many dropdowns. Would it be advisable to make one big query with 12 queries.

    I just want to know how to create an effective query!

    Any SQL expert can help me here.

    Thanks

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    OTLT and effective queries do not go well together. And no - I would not advise one big query with 12 queries - I would sort out your database design.

    But if you are adamant that you want to go down the OTLT route then yes - create 12 queries and use these. Notice, however, that these 12 queries are actually taking your OTLT and "spoofing" a proper normalised design! The worst of both worlds IMHO.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thankyou Rudy

    I will bookmark this time honest

Posting Permissions

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