Buttons? Scripts? Calculations? Help!

[KiwiKim25]

Help!

I have no idea how to go about getting this 'idea' (demand) in place. I've attached a picture of what I need, to help with my poor explanation skills.

I have what is essentially a medical record. For each ID number, there is a lot of things being entered. I'm stuck on one section. "Actions".

I need to be able to state what day an action (once a day, twice a day, etc) was 'started' and 'stopped'. There can be more than one action active at one time. Each with it's own start and stop date. I need to keep a history of these start/stops (but not visible on the current table/portal). Also, the less typing the people entering the data have to do, the better (drop down menus, etc).

I also need a "summary" of what is currently active. This means some sort of script that says shows fields that have a start date, but no end date containing a date newer than the last start date... or something along those lines.

I'm feeling overwhelmed. Please help!!!

~Kim~

[comment]

If, as it seems, each ID number can have several actions then you need to add a table of Actions and relate it to your current table (Patients?).

I also need a "summary" of what is currently active.

You could use a filtered portal to show the active actions. Not sure what a "summary" means in this context.

[KiwiKim25]

I do have a related Actions table. I gave each action that can be taken it's own field.

How can I filter it to only show the 'active' actions? Say Action 1 2 and 3 are currently active (started), but 4 5 and 6 have been stopped, what is the best way to have only the ones that haven't been stopped (1, 2, 3) show up? Each ID number can have multiple actions start and stop a number of times over the year, it's not just a one time thing.

I'm probably just having a dumb moment, but I'm not finding that 'ah ha' moment just yet.

Thanks for taking the time to help!!

[comment]

I do have a related Actions table. I gave each action that can be taken it's own field.

I am not sure I understand what you mean by that. Each action should be a separate record.

[KiwiKim25]

Hm, I guess I don't understand still...

I have a main table, each Person (ID number) has a record in that main table.

I have a second table "Action", that is related by the field "ID number".

People working with this data base will only be looking at the "Main Table". They don't want to have the whole history on the main table, but would like to see what is currently "active" or "not stopped yet".

What I was doing was making a field for each action that could be taken (once a day, twice a day, etc) plus start/stop fields (dates). That's how I set up other tables, which are on the "main table" via portals.

I'm not certain how to set this up so each 'action' is a record instead. Am I missing something basic here?

Thanks again for helping me!

[LaRetta]

What I was doing was making a field for each action that could be taken (once a day, twice a day, etc)

This is immediate red flag. When you find yourself adding many 'like' fields to a table, stop yourself short. Many means those fields *should be records in a related table which is the 'many' side to this one parent. As said, the actions (with the action date) should be records in a related table. :`)

* Of course there are exceptions but it is best to err on the side of basic principle of maintaining data in its smallest part.

ADDED: Of course they could be entries in a multiline text field (such as checkbox or delimited string) but only if 1) no summarizing by value is required and 2) there is only one value involved.

Edited January 19, 2012 by LaRetta

[KiwiKim25]

Huh, I knew I must be doing something odd. Thanks for bearing with me trying to make sense of my mess! Let me go try then and come back with my next snag.

Thank you both very much!!

[KiwiKim25]

Ok, next snag, which is almost the same as the first.

I have it set up so in the actions table I have a record for each type of action that can be take (once a day, twice a day, etc). Each record has the ID Number and a start and stop date.

In my main table I have a portal to the actions table. I have an edit box to type in the actions taken (record) and enter the start and/or stop date. But I still need to have a summary on my main table that says what actions have a start date and no end date.

The people using the database do not want to switch screens to look it up. They want it at a glance. How do I accomplish that?

I really appreciate all the help. There is no one at my work available to ask these questions to.

~Kim~

[comment]

But I still need to have a summary on my main table that says what actions have a start date and no end date.

You could have another portal to Actions, filtered to show records when:

Actions::StartDate and not Actions::EndDate

[KiwiKim25]

Thank you!!!!!!!!!!!!!!

[Ron Cates]

"I have it set up so in the actions table I have a record for each type of action."

You don't want a record for each type of action. You want a record of each action and a field on that record should indicate what type of action it is. Something like a dropdown menu with the different types of actions available that yuou would select to indicate what type of action it is. (once a day, twice a day, etc)

[KiwiKim25]

I have it all set up and working like a champ now. Thank you all for your help!