Multi-Value Field Query

mabrown81

Registered User.
Local time
Today, 00:56
Joined
Sep 20, 2012
Messages
17
I am new to building a database, but I have created a multi-vaule field for patient conditions in which any number of the conditions can be chosen in the field (1-8) as there are 8 conditions and any combination. I want to create a query that will show me the patients that have more than one value selected in this field. Having a world of trouble doing this and any help would be greatly appreciated.
 
Thanks Bob, I actually read that several times and it it show how to create a query that will show all patients, but I want a report that specifically shows all patients that have more than once a greater than 1 selected value. Been stumped for 2 days now. Thanks for the reply.
 
First off, you need to use .Value with the field in your query so it will return the selections made for each record. In that query just have the patientID, visitID, and then this Condition field (Condition.Value) as the field and do a Grouping on it grouping by patientID, visitID, and then COUNT of the Condition field. That should give you the number of conditions per visit for each patient. You can then put in the criteria, >1. Does that help?
 
definitely helps so far... I actually don't have a VisitID field.. should I?
My fields are as follows
PatientID
LastName
FirstName
DOB
PhysicianID
InsuranceID
MedicalCondition (Lookup Table Field)

I apologize for sounding stupid in the first place but how do I do a grouping of them and a count of the condition field?? Thanks again!
so my query should be
PatientID MedicalCondition.value
 
Okay, I wasn't sure what you had. So, you can use all of the fields in the query if you want or you can just use

PatientID, MedicalCondition.Value

with grouping on PatientID and Count for MedicalCondition depending on what you need.
 
cool.. getitng there, so how do i place a count on medicalcondition which is how i figured i would have to do it, but no clue on how...
 
cool.. getitng there, so how do i place a count on medicalcondition which is how i figured i would have to do it, but no clue on how...

In your query you select all of the fields and then you click on the Grouping button
attachment.php


And on all of the fields the TOTAL line should show GROUP BY except for the medical condition field which would have COUNT selected.
 

Attachments

  • sigma.png
    sigma.png
    519 bytes · Views: 4,959
Bob, thanks that worked!! I also listed them by name by grouping by expression.. thanks so much!! have a great day sir...
 
So Bob, after all of that, the application building tool that I am using, doesn't support multi-value fields. Any idea how I woud go about obtaining this information with an alternative route? Any help would be greatly appreciated.
 
So Bob, after all of that, the application building tool that I am using, doesn't support multi-value fields. Any idea how I woud go about obtaining this information with an alternative route? Any help would be greatly appreciated.

So what tool are you using and how does that relate to Access?

As for how to deal with it you create a junction table to store the selected values and you can use a subform or multi select list box(in conjunction with some code). Pbaldy has code for the list box on his sure at http://www.baldyweb.com.
 
Thanks again, I am using Iron Speed. It takes the database and coverts it to a web app. This one may take me some time as everything you said looks like foreign language as of now. But I am going to read up on it. Thanks again.
 
Basically you add a table like this:

tblPatientConditions
PatientConditionID - Autonumber (PK)
PatientID - Long Integer (FK)
ConditionID - Long Integer (FK)

And then you can select as many conditions for a patient as necessary.
 
First off,

Thanks for all of your help Bob. Ok, I have created the patientconditions table and I created a query of lastname, firstname, dob, and medicalconditionID. I did a group by on all and a count on the medicalconditionID of >1. The query worked but it just shows a number. I was able to create an additional column on the last one so that I could see the names of the conditions with it. Basically, how can I add it so that along with seeing the number of conditions, I can also see the name of the conditions? Thanks again.
 
You need to join the table which holds the condition id and condition name into the query as well and select the name from that table.
 
Hello Bob, I have attached screen shots of what I have in which I get no results when trying to add the med condition name. I get results with a count when I de-select the med condition name. Thanks for your help on this.
 

Attachments

It returns data if you uncheck the DISPLAY checkbox on Medical Condition Name? But doesn't return any if you check it? I'm not seeing anything that would indicate that should be the case. You do have data in the Medical Conditions table, right?
 
Sorry Bob, correction I only get results if I do not include medical condition name at all. I have attached a screenshot of the patientconditions table, the medical conditions table, and the results i get when not including medical condition name in the query.
 

Attachments

Hmm, I'm not seeing anything that jumps out at me. Is there any chance you can either post a copy of the database (with fake data) or email me a copy (if you don't want it out for everyone to see)? Either way, if you do decide to do that, make sure to run Compact and Repair on it first and then zip the file (You can right click on the file and select SEND TO > COMPRESSED FOLDER).
 

Users who are viewing this thread

Back
Top Bottom