About Geoff Expertise I specialise in database analysis and design, SQL and database queries using QBE and VBA. In my work, I use MS Access together with MS SQL Server as ETL (Extraction - Transformation - Loading) tools for migrating data between business ERP systems and data stores. My forte is building bespoke functions and applications.
See my
website for example apps and downloads
Experience I am a chartered engineer with 30 years of engineering and business experience, member of the BCS and have been working specifically in database applications, including SQL Server (v7/8/2000) for the last 9 or so years. I previously taught a course in Database Analysis and Design, but am now a freelance consultant and systems analyst.
Commercial database design and development work undertaken.
Question Geoff-
I'm creating a health care database to keep track of patient information. One of the fields is symptoms (short of breath, a-fib, palpitations...). I wanted to have a separate form pop up with checkboxes next to the appropriate symptom, so we can select more than one symptom. I have struggled getting more than one symptom to show ... not sure what to do or if there is a better way to accomplish this.
Any help would be appreciated.
Thanks,
Kevin
Answer Hi Kevin
The data structure for this part of your DB will include 3 tables; the patient, the full list of symptoms and a table with all patient/symptom combinations (this last table is called an Associative table and has a primary key comprising the PKs from both the patient & symptom tables.
Probably the neatest way to do this (Microsoft use it all over their software) would be a form comprising two lists - the LH list contains all the possible unselected symptoms, and the RH list all the selected symptoms, for a given patient. In between the lists are a series of buttons to migrate symptoms left to right & vica versa.
The strategy behind the form is such: the query behind the LH list looks like:
SELECT * FROM tblSymptom
WHERE SymptomID Not In
(SELECT SymptomID FROM tblPatXSym
WHERE PatID=XXX );
here, XXX is the patient ID pulled from the main form
The query behind the RH list looks like:
SELECT T1.SymID, T2.Symptom
FROM tblPatXSym AS T1 INNER JOIN tblSymptom AS T2
ON T1.SymID = T2.SymID
WHERE T1.PatID = XXX;
The buttons, then are used to INSERT or DELETE records from tblPatXSym, followed by a form requery.
You will need to use a listbox object and its ItemSelected property - there is a reasonable example in onboard help.
I have previously developed a model of interactive lists but this is not immediately to hand at present. I will try to retrieve this sometime, smarten it up and put it on my download site http://www.gmjohns.fastmail.fm/ (if you are in a hurry, don't hold your breath !)
