Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2013
    Posts
    6

    Question Unanswered: Select columns from a table

    First I should be grateful for you all for spending your valuable time on assisting people like us.

    I need a assistance regarding my first Access project

    I have a access table with column headings like below(say table1)

    year , month , feeder name , FaultType1 , FaultType2 , FaultType3

    now I have created a form with combo boxes to select year month and FaultType etc... and i need to draw a pivot chart based on my selection in access form.

    it's easy to get the combo box values for year, month(for instance combo value for Year will be 2011,2012 etc..). code like below meet that requirement
    select Year from Table1 where Table1.Year=[Forms]![FormName]![Combo Name]etc..

    however I need user to select FaultType through another combo box(in the same form) and then draw a pivot chart based on the FaultType he selected. my combo box drop down values should be FaultType1, Faultype2,FaultType3 which is infact the column names in my Table1 and once he select a specific FaultType then a pivot chart should be drawn based on that selection.

    to do that I guess I have to write a SQL statement first to create a dynamic table based on user's selection and then draw the pivot chart

    my SQL query will be like below one;

    SELECT Year, Month, FeederName , "and the Fault type based on the user's selection through combo box" FROM Table1 WHERE etc...

    Here the FaultType is actually the column names in my Table1

    I need a help to fill the " " part in my above SQL which is on how to select the FaultType(1 or 2 or 3) from Table1 based on the users selection

    Thanks for helping

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use a compound IIF statement to select the approproiate column
    say you list/combo box was called cmbFaultTypes and had 3 vales FT1,FT2 & FT3
    then providing the form is open you can uyse the value of the list/combo box in the query

    select year, month, feedername, iif(cmbfaulttypes = 'FT1',FaultType1,iif(cmbfaulttypes = 'FT2',FaultType2,FaultType3)) as FaultType

    or use the brute force approach
    design the report then copy it twice, modify the report as required. that way round you can put the reports as part of a batch process so there need no be any user intervention required to generate the report set

    BTW just seeing columns named FaultType1,2 & 3 would normally suggest there may be a problem with your table design. I'm ont saying it is a problem but it could be a symptom of not normalising the data. its doubtful its a problem in this application but something to bear in mind in future
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2013
    Posts
    6
    healdem, it appears that what you have proposed is working however still there are few things to fix

    each time when i make selection in my cmbfaulttypes Combo Box and refresh the related table it asks to enter parameter value this Combo Box.
    (I have attached the error message with this)

    Any idea on why it asks to enter such parameter?
    I f I can overcome this issue I guess my work is done

    anyway thanks for the help on this
    Attached Thumbnails Attached Thumbnails error.jpg  

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You probably need to refer to a specific property of the combo box within the query

    Eg
    Mycombobox.value.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2013
    Posts
    6
    hmm...
    still no luck with this
    once i change my query, now the same message appear while the name of the combo box has changed to Combo220.value (based on my initial figure)

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what is the name of the combo box you are pulling the value from (make certain there are no typos.

    have you got a value in that combo box (I dont' know what woul hapopen if you had not selected a row int he combo box)

    what are the values of the combo box, how many columns does it have, and what are they.
    Im assuming its two columns a key and a description, the key MUST be the same as used in the IIF.


    can we see your SQL?
    can we see the properrties of your combo box, and ideally its data or data source
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Feb 2013
    Posts
    6
    Hi,

    Well after some modifications I was able to done what I wanted.
    thanks to you healdem for directing on this.

    My final code in a single text box in access was like following


    SELECT FeederTripping.Year, FeederTripping.Month, FeederTripping.GSS, FeederTripping.FeederNo, FeederTripping.FeederName, IIf([Forms]![Table3]![FaultType].ListIndex=0,[AEF],IIf([Forms]![Table3]![FaultType].ListIndex=1,[AOC],IIf([Forms]![Table3]![FaultType].ListIndex=2,[AEFOC],IIf([Forms]![Table3]![FaultType].ListIndex=3,[AUF],IIf([Forms]![Table3]![FaultType].ListIndex=4,[ALoadSh],ATotal))))) AS FaultType
    FROM FeederTripping
    WHERE (((FeederTripping.Year)=Forms!Table3!Combo223) And ((FeederTripping.GSS)=Forms!Table3!Combo227) And ((FeederTripping.FeederNo)=Eval("[Forms]![Table3]![Combo225].[Column](1)")) And ((FeederTripping.FeederName)=Eval("[Forms]![Table3]![Combo225].[Column](0)")));


    which I used IIF for selecting columns dynamically based on the Combo value.
    I also has to use commands such as 'Eval' in order to avoid some constraints

    Thanks Once again

Posting Permissions

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