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:
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)
tblDropDown has three fields:
DropDownID (Primary Key)
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:
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.
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!
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.