none
Which checkbox is true in one query column RRS feed

  • Question

  • Hi,

    I have table with 4 chceck boxes. Every time there is only one checkbox = true. I need to make query that will have result in one column which check box is true. I tried to use Choose or Switch function but id does not worked.

    Thank you.

    Thursday, January 18, 2018 9:44 PM

Answers

  • What attributes do the four Boolean (Yes/No) columns represent?  Multiple Boolean columns in a table often mean that data is being encoded as column headings.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

    There might well be a better way of modelling the data, by decomposing the table, which would also allow you to achieve your object more easily, as it would merely require a query over a set of rows, rather than columns.

    Ken Sheridan, Stafford, England

    • Marked as answer by Seeter Monday, January 22, 2018 3:02 PM
    Thursday, January 18, 2018 11:28 PM

All replies

  • Hi,

    Can you show us your Switch() expression? What do you mean by "does not work?"

    Did you try something like this?

    Switch(Checkbox1=True,"Checkbox1",Checkbox2=True,"Checkbox2", etc...)

    Thursday, January 18, 2018 9:54 PM
  • Yes I did, but still it does not work. Window pops up and it wants parameter...

    • Edited by Seeter Thursday, January 18, 2018 10:24 PM
    Thursday, January 18, 2018 10:23 PM
  • When you get a parameter prompt, it usually means a field's name is misspelled.
    Thursday, January 18, 2018 10:26 PM
  • What attributes do the four Boolean (Yes/No) columns represent?  Multiple Boolean columns in a table often mean that data is being encoded as column headings.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

    There might well be a better way of modelling the data, by decomposing the table, which would also allow you to achieve your object more easily, as it would merely require a query over a set of rows, rather than columns.

    Ken Sheridan, Stafford, England

    • Marked as answer by Seeter Monday, January 22, 2018 3:02 PM
    Thursday, January 18, 2018 11:28 PM
  • Database represents manufacturing proces that has 4 phases. Each checkbox represents one of the jobs. I use chceck box as filters for several forms. When I made database I was not experienced, I could make it as integer variable with number that shows phase of manufacturing but now I have many SQLs and forms nad people are using it.
    Friday, January 19, 2018 11:24 AM
  • I could make it as integer variable with number that shows phase of manufacturing but now I have many SQLs and forms nad people are using it.

    Hi Seeter,

    You could make a new field, with the phase of manufacturing, that is updated in the AfterUpdate events of the 4 checkboxes, and thus always synchronized.

    One by one you can then change your SQL's from checkboxes to the phase field. Finally you can modify the different forms one by one.

    Personally, I use these kind of "dynamical changes" quite a lot.

    Imb.

    Friday, January 19, 2018 11:56 AM
  • Does each checkbox have its own field in a table? If yes, then just include each field in a query. Then use -1 (True) in each criteria box. The query should show only those fields that have been checked.
    Friday, January 19, 2018 2:45 PM
  • Hello Seeter,

    What's the current statue of this issue? Has your original issue been resolved? If it has, I would suggest you mark the helpful reply as answer or provide your solution and mark as answer to close this thread. If not, please feel free to let us know your current issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 22, 2018 1:26 AM
  • I gave up my previous way, that covered checkboxes. In future I will make one column with number.
    Monday, January 22, 2018 3:02 PM
  • You can use a Combobox or Radio buttons instead to represent each phase. But in the database it is just 1 number.
    Tuesday, January 30, 2018 7:20 PM