To Do List Query Based on 7 Tables

[ledmark]

Hello - I have built a electroninc medical record for someone and it has 7 tables that all contain fields for PatientName, ToDo, and DoneBy. The user would like to click a button that will bring up a to do list for the current day. I have tried and tried to make one query to pull all the data from each table but cannot seem to get how to do it. So, I made 7 append queries and a table to put the data into, then built a macro that will run each query and append it to the ToDo table. I then built a query based on that table to make a report with the days to do list.

Is there a way to assign the macro to a command button without the user having to answer all of the append questions? I just want her to be able to click a button and have the To Do report come up so she can print it out.

I'm reading, reading, reading but nothing is clicking to show me how to do that and I don't know VBA.

Any help with this would be super-duper and very much appreciated.

Laura

 

[pbaldy]

I wouldn't mind knowing what the 7 tables contain, as it sounds like an issue, but you can use SetWarnings to stop the messages (make sure to turn back on at the end).

 

[ledmark]

I can give you a list of each table but I have to write them all down first then write to you again. The main table is Patient Information which is done one time only for each new patient. Then there are tables for Alternative Medicine Notes, Attendance, Current Medications, Clinical Notes, Outside Provider Phone Contact, and an insurance information form. The patient Information tablel has a one-to-many relationship with the other 6 tables. Let me know if you want all the fields in each table or if I've set this up totally wrong - LOL!! That would be poopy!!

Thank you for your help.

Laura

 

[pbaldy]

I though maybe you had a table for each day or something. Some of those sound appropriate, but I would expect to have one table with "To do" items, with perhaps an extra field to denote which of the other areas it related to. That said, I don't know enough about what's in the tables to judge the design.

 

[ledmark]

Another question. Is there a way to only append what is newly put in each day instead of duplicating everything every time the append queries are run. I want the user to be able to pull the To-Do items from the tables only once, so if the user wants to take something off the To-Do list the same record won't be in the table numerous times. Am I making sense?

Example: User opens the database and clicks the button to view the To-Do list. All the append queries run and then open the table so the To-Do itmes are listed in descending order by date entered. Everything that has been entered is populating the table, but if the user closes it and then opens it again later to take something off it will duplicate all the appending.

Is there some kind of criteria I need to enter into each append query or some function in the macro that will allow this to happen? Or is VBA code needed to tell it to append only newly entered items based on date/time of entry?

Thanks for any help.

Laura

 

[pbaldy]

Rather than append queries and another table, I'd probably use a UNION query:

SELECT Field1, Field2
FROM Table1
UNION ALL
SELECT Field1, Field2
FROM Table2
UNION ALL
...

You could add criteria so you only see today's, or whatever.

 

[ledmark]

OK - I'm going to try that. I'll let you know if I have problems - thank you very much for your help.

Laura

 

[ledmark]

OK - I made the Union Query and it works perfectly. Now, can I use this union query to make a form that will allow the user to delete items on the To-Do list once they are done? The user can't modify the query so that's why I was thinking about the append queries and table before - the user can add or delete records.

If I use the union query in a form can I use command buttons to delete records in any of the tables once the To-Do itme are completed?

I'm trying to wrap my head around how I can do this.

Thanks for any more help

Laura

 

[pbaldy]

As you have found, the UNION query is read-only. I would execute an UPDATE query that uses the current record in the UNION query to modify the underlying record. Because you have more than one table, you'll need to include the table somehow. You can do this with the UNION query:

SELECT Field1, Field2, "Table1" AS SourceTable
FROM Table1
UNION ALL
SELECT Field1, Field2, "Table2" AS SourceTable
FROM Table2
UNION ALL

Which you can then use to determine which table to update. I won't say you can't do it with your append queries/extra table, but it's not what I would do (of course I wouldn't have the to-do items in multiple tables in the first place).

 

[ledmark]

Hi Paul - I have been wanting to do just what you said without using all the tables and I know how to do it that way. So thank you for your feedback and all your help. I have a whole new ides that is far easier and less cumbersom for the dtatbase. The hardest part for me is getting the user to understand what is best to do.

Thanks tons!!

Laura

 

[DCrake]

Laura

When the users are using the app performing different duties/input there will be certain tasks that will fall under the category "Do Something Significant Point" (DSSP). Lets say it is a diary entry to call the patient to confirm an appointment. The user enters the date they need to call the patient. As soon as they have entered the date you could get your app to pop up a message box to say something along the lines of "Do you want to add this to your To Do List?" If they click Yes then add the relevant infomration to your "ToDo" table. That way you are forcing the user to make a decision regarding the DSSP. If you wanted to be really clever you could send the task to Outlook as a reminder/diary event.

David

 

[ZBKAI189]

Laura

When the users are using the app performing different duties/input there will be certain tasks that will fall under the category "Do Something Significant Point" (DSSP). Lets say it is a diary entry to call the patient to confirm an appointment. The user enters the date they need to call the patient. As soon as they have entered the date you could get your app to pop up a message box to say something along the lines of "Do you want to add this to your To Do List?" If they click Yes then add the relevant infomration to your "ToDo" table. That way you are forcing the user to make a decision regarding the DSSP. If you wanted to be really clever you could send the task to Outlook as a reminder/diary event.

David

Oo... Oo... I want to do that! My DB tracks employee tasks (feedback, annual reviews, etc). Currently, I click a button that runs a send report macro to the supervisor. I also want it to add a task to their taskbar... is that what you're talking about? How do I do it?

 

[DCrake]

You can't really add an item to their taskbar but you can send a reminder to them via outlook. There are plenty of discussions on the forum on that topic I am sure that there will be one that will suffice.

David