show when more than 1 value is true

[Dazza666]

Hey,

i've got what i thought was a really simple query thats proving hard for me to suss, search turned up no results.

I have a query that has the following:

First Name / surname / diabetic? / physical disability / learning dificulty /Other disability /

apart from first name and surname all fields are true/false (tick boxes)

I need a way to query for records that are true in two or more categories.

I can't really work out how I would go about this,

is there a way?


thanks

 

[Kryst51]

I have a suggestion for you but first, if there is a question mark in your field name, I would remove it, it can cause problems for you later, try to avoid spaces and special characters in field names.

In the QBE, if you are searching for items that meet more than one case of true, just put "true" in the criteria of each field on the same line.

If you want to search for items that are true for one case OR true for another case, but not necessarily true for both then put "true" on one criteria row for the field and then on the other field move down a row and put "true" for the criteria.

*"True" may not work, you may need to use the number, it is either -1 or 0, I can never remember which and always have to test to get it right.

Please let me know if this info doesn't help, or if you need more information, or if I misunderstood your question.

 

[MSAccessRookie]

Hey,

i've got what i thought was a really simple query thats proving hard for me to suss, search turned up no results.

I have a query that has the following:

First Name / surname / diabetic? / physical disability / learning dificulty /Other disability /

apart from first name and surname all fields are true/false (tick boxes)

I need a way to query for records that are true in two or more categories.

I can't really work out how I would go about this,

is there a way?


thanks

First of all, I am not sure that your table has the best design structure, since adding any more categories will require modification to the table, but using the structure that you have, I make the following suggestion. Use the IIf() Function to change each Yes/No into 1/0, and add the categories. Any row that has a category count of over 1 should qualify for display. Something like the following is what you are looking for:

SELECT [First Name], 
    [surname],  
    (IIf([diabetic?]=True, 1, 0) + 
    IIf([physical disability]=True, 1, 0) + 
    IIf([learning dificulty]=True, 1, 0) + 
    IIf([Other disability]=True, 1, 0)) As DisabilityCount

Another thing to consider is your choice of Variable names. Spaces and Special Characters ("?") should be avoided at all costs. If for no other reason, you may find that upgrades to other platforms (such as SQL Server) do not support them.

 

[Dazza666]

First of all, I am not sure that your table has the best design structure, since adding any more categories will require modification to the table, but using the structure that you have, I make the following suggestion. Use the IIf() Function to change each Yes/No into 1/0, and add the categories. Any row that has a category count of over 1 should qualify for display. Something like the following is what you are looking for:

SELECT [First Name], 
    [surname],  
    (IIf([diabetic?]=True, 1, 0) + 
    IIf([physical disability]=True, 1, 0) + 
    IIf([learning dificulty]=True, 1, 0) + 
    IIf([Other disability]=True, 1, 0)) As DisabilityCount

Another thing to consider is your choice of Variable names. Spaces and Special Characters ("?") should be avoided at all costs. If for no other reason, you may find that upgrades to other platforms (such as SQL Server) do not support them.

Hey thanks,

yeah I probably could have designed the table differently but I needed to be able to select more than one and since the categories are specified from a framework they will not change in the life of the database.

Your solution worked perfect thanks for that

 

[namliam]

FYI
IIf([diabetic?]=True, 1, 0)
is the same as: IIf([diabetic?], 1, 0)
or since the value of true is -1, Abs([diabetic?])

they will not change in the life of the database

Oh, how often have I heard these words or simular ones...

 

[MSAccessRookie]

Hey thanks,

yeah I probably could have designed the table differently but I needed to be able to select more than one and since the categories are specified from a framework they will not change in the life of the database.

Your solution worked perfect thanks for that

I always assume that users given a working system will always want to make it more powerful by adding new features, options, or data categories. It tends to save a lot of headaches as time goes by. While I am glad to hear that the solution worked for you, I still feel that you might want to consider redesigning your system to allow for such user requests in the future. Good luck!

 

[Dazza666]

I always assume that users given a working system will always want to make it more powerful by adding new features, options, or data categories. It tends to save a lot of headaches as time goes by. While I am glad to hear that the solution worked for you, I still feel that you might want to consider redesigning your system to allow for such user requests in the future. Good luck!

Hey,

I completely agree with you, normally I would have a drop down box with the options stored in another table, however we need to be able to select more than one option? I'm not really sure how to go about that so I decided to build the options in to the table. The reason I say the options won't change is that my company has funding that relies on this fixed framework. If the framework changes, the database will become mostly redundant anyway as it is used to provide info about the framework only.

If you know of a way I can select multiple options where possible (without using placeholder fields) I'd be really grateful.

Thanks all