Should I give Up on This Form?

[Palomino33]

Current Method: We have a paper form that has about 40 check boxes on the front. Each check box is a "Topic" of discussion. On the back of this form lists about 3 objectives for each discussion. I want to put this in Access and somehow get the topic & discussion on one side of the form. It isn't necessary that all topics show on a form, just the topic of discussion and the objectives necessary.

I have:
1. A Patient Table containing 20 patients. (Fields: Name, MR#, Admission Date, Discharge Date, etc.)
2. A Memo Table containing 40 records. (40 standard comments)
3. An "Active Patient Query" that gives me my active patients (for this example - 10 active patients) based on "is null" criteria in a discharge date field.
4. A form based on the "Active Patient Query" - that gives me the same exact form for all 10 patients. (Exactly what I want here). This form contains 40 yes/no checkboxes "Various Topics of Discussion" .

I Want:
1. When I click the "Topic of Discussion" check box on the form....I want the memo field from the Memo table to show on the form, and for all 10 forms to still be exactly the same.

**Of Note...the form doesn't have to be a check box form. It could have a list box, combo box, or any other method. The form just isn't big enough to have 40 topics and their objectives listed on the front side of a form.

I have Tried:
So many different things and have been able to come up with the solution. One of the things I just knew I could get to work was....
1. Topic Table: ID, Topic, Memo.
2. On the Form, I did a combo box that lets me lookup the value I want from the Topic Table....but....I have to do this same step on each of the 10 active patient forms. I want them all to be the same. (10 isn't that many to do, but things change when you have to do 100). I have tried other things, but just havn't got it to work.

Any ideas? Thanking the experts in advance

[orange]

In your PatientTable you have Admission Date, Discharge Date, - are you only allowing 1 stay per patient?

You may want to research Normalization and DataBase design to get your structures set up properly.
There are free data models - this one may be useful -
http://www.databaseanswers.org/data_...care/index.htm

[Palomino33]

We have frequent flyers. They would have multiple admissions. There is an autonumber field for each table record; however, the primary key is the MR#

[orange]

OK. But what do you store about the Patient? Name, address, etc
Are you going to repeat that for every admission/discharge in the Patient Table?
I think you should show us your tables and relationships as a jpg.

Get your structures correct before you get too hung up on forms and combos etc.

[Palomino33]

here is the table layout...see if this helps.

[alansidman]

Repost as there is nothing attached.

[Palomino33]

Thanks Orange. And Alan, Glad you dropped by Guys, not sure how looking at this will help, so if if this doesn't say something quickly, please go to the next post where I put the form.

[Palomino33]

I feel certain looking at this will be more helpful to understand the goal. Each topic below has 2 or 3 objectives that go with it. For example....if I click "Diabetes"...then I want the "Diabetes Objectives to fill the Objective box." I can get this to happy on one form, but I want all 10 forms to be the same, without me have to click on each form.

[June7]

I can't tell from that Relationship diagram which tables are relevant to this issue. What table has the set of standard Objectives? What table associates the objective to patient? Why would you want this objective to be the same for all 10 active patient records? Can you provide the project for analysis? Make copy, remove confidential data, run Compact & Repair, attach to post.

There are only 27 checkboxes.

[Palomino33]

Hi June7 Thank you for coming You are right, there are only 27 boxes on this form. I have another form similar that has more boxes that I need to do the same thing with It might be best not to think about my approach and reason it out and you just make sense of how it should be. The DB doesn't have any real data in it yet.

The reason I want it the same for all 10 patients is because, this is group therapy. All patients receive the same instruction. Just have to have a document for each patient chart. Still want me to post the db?

[June7]

Yes, don't need real data.

[Palomino33]

Hi, I deleted all of the reports from my db....and zipped it and it's attached. Hope this helps. When you look at the forms....
there is a psychobojective, and a psychoobjective1. Both are attempts at accomplishing the same thing.

[June7]

First problem I see is that query ActivePatientList has two tables without a JOIN.

Orange posted a valid question. Can patients have multiple visits, and even have different diagnosis? If yes and you want to maintain that history, need another table for Visits, and not keep admission and discharge data in the PatientDataTable.

A problem with checkboxes in the 'Check One' sections is that more than is checkable. Need to use an OptionGroup arrangement to limit choice to one. Or else use a list or combo box.

With this setup, would need VBA code to save each selected item to appropriate table. This 'click to pick' design might be nice for the users but requires more programming skill by developer.

Several queries include special characters (#, -) in names. Should avoid spaces, special characters, punctuation (underscore is exception) in names. Better would be ActivePatientListByMRNum or ActivePatientListByMR_Num.

You have an ActivePatientList query for each provider (10 and growing?) and I presume a corresponding report for each. This is inefficient. Should have one query and report and filter by provider as needed.

You are setting Lookup in tables. I never do that. Might review this http://access.mvps.org/access/lookupfields.htm

[Palomino33]

First problem I see is that query ActivePatientList has two tables without a JOIN.

Can you help me understand why I need a join? The bed table is a lookup table for the patient table.

Orange posted a valid question. Can patients have multiple visits, and even have different diagnosis?

Yes to both.

If yes and you want to maintain that history, need another table for Visits, and not keep admission and discharge data in the PatientDataTable.

This is awesome! I havn't thought about doing it like this....but this does make perfect sense. And...I didn't understand the meaning of this in the prior post..but understand it totally here. Thanks to you both!

A problem with checkboxes in the 'Check One' sections is that more than is checkable. Need to use an OptionGroup arrangement to limit choice to one. Or else use a list or combo box.

Here's the main dilemma for me at present. The thing is....the form doesn't "have" to have checkboxes. It could be any other setup.....with a topic...and it's objectives. I just need it to generate the Same form for all active patients. (Hope this makes sense).

With this setup, would need VBA code to save each selected item to appropriate table. This 'click to pick' design might be nice for the users but requires more programming skill by developer.

I'm hoping to get something easier for me....

Several queries include special characters (#, -) in names. Should avoid spaces, special characters, punctuation (underscore is exception) in names. Better would be ActivePatientListByMRNum or ActivePatientListByMR_Num.

Thank you....will try to brand this in my thinking.

You have an ActivePatientList query for each provider (10 and growing?) and I presume a corresponding report for each. This is inefficient. Should have one query and report and filter by provider as needed.

We shouldn't have more than 10 providers (at the max). Here's the thing here...A provider (a counselor) does a group note in Microsoft Word....and then merges from his/her list from the database. There aren't any reports based on any of the provider list. My reasoning for setting it up this way, is if a counselor quit, and was replaced by a new counselor...and I wasn't there...(say I no longer work there)...then all they would have to do is update the provider table. It wouldn't require a skilled user to change a query. In the past 20 years...I've worked with over 800 people (two employers)....and out of that many people, only 4 people had any access skills at all. (Count me as one, and that isn't say much .

You are setting Lookup in tables. I never do that. Might review this http://access.mvps.org/access/lookupfields.htm

Off to read this.....thanks so much for sharing!Look forward to your reply.

[June7]

You want objectives assigned to all patients in a 'group'. It is not clear to me how a patient is identified with a 'group'.

Why have users enter name in title case and upper case? Just enter the title case, the upper case can be set in reports by formatting code. Why have them enter initial of last name? This can also be extracted from the name with string manipulation function: Left(LastName,1). FirstNameLowerCase field looks more like nicknames.

Since you have the Objective descriptions in multiple similar fields (which is not strict data normalization), will need to use concatenation string manipulation to display them together in one textbox. Like:
Description1 & Chr(13) & Chr(10) & Description2 & Chr(13) & Chr(10) & ... Create a query doing this with an expression in Field row. Then this constructed field will be available to bind a textbox to when it is included in a form/report RecordSource. You will never, ever need more than 6 descriptions?

Without the join on AssignedBed and BedID keys, the related BedCode and Location information cannot show on forms and reports (except as combobox RowSource), only AssignedBed value saved in PatientDataTable. See No 5 of the link I show in earlier post. Could not sort and group on the related data.