Proper Database Design??

[jondavidf]

Sorting is working correctly.



I am just keeping your names until I fully have a grasp on everything. I can always modify the names later.

I think I am ready for the reports.

If it's not too much trouble I do have one question on the activity table. How hard would it be to add an "Current" field with a "Yes/No" property that would determine whether it appeared on the drop down of the "Event" form? If it's too much for right now I can try to work that later on my own.

Thanks again for all the help you have given me to this point.

[Rod]

If it's not too much trouble I do have one question on the activity table. How hard would it be to add an "Current" field with a "Yes/No" property that would determine whether it appeared on the drop down of the "Event" form? If it's too much for right now I can try to work that later on my own.

Yoy have a think about it then I'll get back to you in a couple of days with my solution. It's actually straightforward.

[jondavidf]

All is working correctly for the drop down. I have added the "Current" field to the Activity form with a Yes/No property. I then went to the row source and added WHERE CurrentActivity =YES.

[Rod]

Your solution is spot on. You can now do all sorts of fancy things based on the 'Current' value.

OK, here's the report and some instructions.

Jon2.zip

MSAFJon.zip

[jondavidf]

Everything is working as prescribed. I still need to look at the code a little more in depth to see exactly what is going on. I will look at it in more detail a little later and send a reply back with any questions. Once again, I'm blown away with how great everything is working so far!

[jondavidf]

I have gone through the code more in depth. Most of it is beyond my level of detailed understanding, although I can roughly follow through with what each section of code is doing. It amazes me that you can know the VB code that well to write all of it out.

I think there's really only a couple things that I need before I deploy the database for use. The functionality that I do still need is to print a very similar report to what you created for the daily attendance rosters but to do a monthly attendance roster by person. What I am thinking is Name, Room Number and Month at the header of the report, with the days of the month as column headings and the activity names as row headings. The filled data will be A, P, S, R, or U as it is on the current report you made. If there is any way you could help me with this one last thing it would be very greatly appreciated.

There are a couple of things I think I can handle myself to add a little functionality to the database also. The first would be to create an archive table for residents that are no longer present at the facility. The second may be to create a report that lists the total number of activities and total number of participants for each month or specified time. I think all the basic data is already there for that. I should be able to figure that out on my own, though. If I run into issues I'll submit a post here.

[Rod]

Time to switch to email. I've sent you a forum message.