Is there a way to make a query that can choose several values in a single field and find matching values in another field. Example: Field 1 is Care&Supervision with a list of 45 care choices. Field 2 is a list of Care Facilities that each have different care options. All care values are listed in Field 1. Field 3 is the city in which the care facility resides. So if I want to know all the care facilities in sacramento that treat dementia, bi-polar,males only and combative behabior, can a parameter query be created that will show the mutiple all care facilities in sacramento for these care options?
Thanks for responding. I will attempt to explain what I need to happen. I have a table that has 45 care values--example: Acting Out, Alzheimers, Dementia, Combative, Deaf, Blind. I also have a table of about 3000 Licensed Care Facilities with names and addresses located in northern California. So if you called me and said I can not take care of grandmother in our home;she needs to be placed into a licensed care facility that handles dementia, incontinance, combative behavior. I would like to show the results of all facilities in let's say Sacramento that have these care options. I would llike to be able to choose care options from a list and then somehow (there's my question) have access 2003 be able to find all care facilities in sacramento that can handle grandmothers care needs. Can this be done?
GUI girl
So do you have one table with 45 fields one for each condition? or do you have one field that can contain up to 45 conditions?
Either case this is not normalised. What you should have is 3 tables
Table 1 is the property
Table 2 is the conditions
Table 3 is the Property conditions
Table 1 contains
Property ID PK
Table 2 contains
Condition ID PK
Condition Description
Table 3 contains
Property ID FK
Condition ID FK
A 1:M relationship between T1 & T3
A 1:1 relationship between T2 & T3
You would then add the condtions associated to the property in T3 and then filter T3 where condtion = Many Conditions this would bring up all properties that have the nominated conditions.
i would keep the table as it is - I think this IS normalized, as presumably none (or most) of the care functions are dependent on others - it is just a true/false yes/no whether a particular care home offers alzheimer care, and a different true/false regarding incontinence care.
So i think you need a function to do this.
for each row of your table call a function with all the yesno fields as arguments (yes 45 arguments) - ie just have a query based on your care table with a column calling the function
NOW, in the function, just test those yesno fields for the values you want, and return true or false for the function based on the comparison test. - if you make the values you want variables then this becomes a dynamic function
eg simply have a form with all the care types available (check boxes) - tick the ones you want, and run the query - the function tests the settings in the table against the ticks to return the matching items.
the function ends up looking something like this. note that with slight changes, this is easily amended to find all rows satisfying say 3 or more tick boxes - just check all flags and count the matches
Code:
function includethis(flag1 as boolean, flag2 as boolean flag3 as boolean .... etc) as boolean
'check1, check2, check3 etc are the REQUIRED flags
if check1 and not flag1 then
includethis = false
exit function
end if
if check2 and not flag2 then
includethis = false
exit function
end if
if check3 and not flag3 then
includethis = false
exit function
end if
etc
'we havent relected it, so we must need it
includethis = true
end function
I agree with what you are saying Dave, however, 45 check boxes on a form is a bit busy, and what happens if they want to increase the options? this would involve more development, wouldn't it?
You could have a multi-select listbox and use the ItemsSelected event. The form would be a lot cleaner an not be subject to changes if the rowsource came form a table.
You could still have the 45 yes/no fields in the table but again what happens if they increase the options? you would have to modify the table to accomodate them.
i agree that 45 is a lot, but it is still a reasonable thing to do, i think. And if it becomes 46 in due course, then that is immaterial - its no different to any system needing an additional field adding to manage some process
With regard to the specific function - you dont EVEN have to pass 45 arguments into the function, or do 45 specific tests - you could just pass the recordid, and use a recordset to recover all the values you wanted - and you could get the check box states by checking each control on the tick box form - maybe have the checkbox name correspond to the name of the "care" attribute, or use the tag property
so then you get (pseudocode)
Code:
for each control on the master control form
if its a checkbox, and if the checkbox is true then
verify that the carehome attribute for that setting is also true
if not then function is false, so exit it
next
I do think it is more correct to have 45 separate fields, than to have a sparse matrix controlling these as "lookup" type values.
thinking out loud
i think if you showed the facilities as (sparse) lookups to a care home
care home 1 , facility 1
care home 1 , facility 3
care home 1 , facility 6
care home 1 , facility 10
care home 1 , facility 12
care home 1 , facility 20
care home 2 , facility 2
care home 2 , facility 3
care home 2 , facility 9
care home 2 , facility 15
care home 2 , facility 17
care home 2 , facility 22
then to find care homes offering all of facilites 1,3,6,9 (but allowing the searched for facilities to be dynamic) then you have an even trickier problem - i actually think its far harder than having the table with 45 attributes - and I cant even think how i would do it.
All points condsidered I don't think our comments are going to help the poster, if he is folllowing our discussion. It's time for them to make the descision on how best to approach it, don't you think.
