Export thread

Hello there my unending source of wisdom slaves thinking for me so I don't have to @stienman and @PatrThom friends with some knowledge about IT.
I have a fairly simply question about organizing something. Recently, my girlfriend took over as coordinator for a small facility. At the moment, all remarks are jotted down in a paper log, then copied (you know, physically) several times and put in different folders and binders. Let's just say that's approaching "quaint" by now.
Her idea was to have all remarks noted down in one big Word-document, and her manually separating remarks about each topic, copy-and-pasting those in several different other Word-docs and creating separate logs for each user. This makes anyone with some knowledge of computers cringe.
Now, let's first explain exactly what sort of thing she needs - it's not much.
Every day, employees have to be able to make remarks about users, and based on categories. One remark can be about more than one user, or fall in more than one category (so, theoretically, one thing would have to be able to fall under "John, medical issues" and "Bob, behavioral problems", if Bob stole John's meds.).
When other employees go on or off duty, they need to be able to see all of these from the past few days at a glance; on the other hand it's useful to be able to only see those about a specific user, and/or for a specific date-range, and/or specific categories - the filters are pretty self-explanatory.

My Access-days are far behind me, and my DB-knowledge is generally pretty limited. Still, I could probably work something out that works. I'd say it'd even still be possible to do in Excel - might be a bit tricky if one note is added to odd combinations of categories/users/employees, but still.

However, my girlfriend's one of those computers-are-magic-boxes sort of people. She can use them, but has no understanding of how they work and having her mess around in Access-style menus isn't a good idea. Coupled with some of the employees being near-computer-illiterate, and having people type everything in in a system where they could accidentally delete whole parts or mess up completely seems like a bad idea. Ideally, for the employees, they'd just have a couple of checkboxes/fields (User, category, employee name typing it in, content, date...I think that's it, so really basic) and a selection screen (All/filter by...) to review earlier messages. My GF needs a bit more access (adding new users and employees, removing/archiving older ones, if necessary adding categories...though perhaps it'd work better if I did those things for her )

Okay, now, my question falls apart into several parts:

• Would it be easiest to just do the lot in Excel and block everything else off for editing?
• Is it worth it to try and put the lot in Access? I feel like I'd be using a tool waaay too complicated for an easy task.
• Is there/are there free and easy log apps/programs out there that would be suitable for this? If so, which one/where?
• I'd like to clarify I'm not asking someone else to suddenly fill it all out or anything I'm just trying to work out what the easiest/best way is to solve this, not in the "oooh, look what all I can do with a computer" way but in the "the end users won't be able to cock it up and will be able to do everything they want without further intervention" way ;-)

Cheers and thanks for any suggestions/tips

Edit: and since I didn't mention it, there's a little over a dozen users at the moment, 6 categories, and about 6 or 7 staff members. Which is why it's currently feasible to do it all by hand...But...ACK!

I've made a temporary version in Excel, just all the basics on one sheet, the log on another, and enforcing choosing author/user/category from a list, leaving date and content as free fields. Suits most needs (though it's not very pretty ), but it's not possible to put one comment in 2 categories and apply it to 3 users, without copy/pasting it a few times...Which isn't ideal. Is that sensibly possible in Excel? 'cause then I might as wel lscrap the question of using something more complicated

The last time I had anything to do with databases, it was with DBASE II+, not counting that time I dabbled in FileMaker.
I know you could bang together a front-end to some sort of usable database with Xojo (another IDE I have dabbled with) but I don't know how soon you need this sort of thing. Wish I had more practical experience with this sort of thing for you, but I don't.

--Patrick

PatrThom said:

The last time I had anything to do with databases, it was with DBASE II+, not counting that time I dabbled in FileMaker.
I know you could bang together a front-end to some sort of usable database with Xojo (another IDE I have dabbled with) but I don't know how soon you need this sort of thing. Wish I had more practical experience with this sort of thing for you, but I don't.

--Patrick

Click to expand...

Hey, waidaminnit! I pay my monthly HF subscription specifically to make use of the extensive IT service desk! I insist you start a short-term high-volume course to remedy this issue right away, this is simply unacceptable! Less-than-total knowledge on a part of your field simply will not be tolerated. You don't hear Jay saying he's not up to date with local comic heroes costumes and therefore can't post any cosplay next week, now do you?!
(the fact that my mind went cosplay > Jay instead of cosplay > Fez may say something about what threads I do or do not visit with any frequency. Err, shush )

No worries

Access is good for small projects, but gets to be too big too quick. With Excel, you can do pretty much everything you can in Access, and even do things more easily like graphs and pivot tables.

But when you are talking about a bunch of users in the same accounts, you start to run into issues no matter what you do. So while Excel might work for you, an Access dashboard would be more user friendly.

Let me think on this a bit.

It might not be the most elegant solution, but perhaps a note keeping program, like Evernote or One Note? They have a much more friendly metaphor than a database or a spreadsheet, and are pretty good at non-relational organization.

Were it me, I'd just spend a little time with access, compile it as an app, and be done with it. I don't know enough excel to pull it off easily, because the data you're tracking is very relational, and while you can build relationships in excel, it's pretty cumbersome.

I at one point even made an app that read excel files and presented the data usefully, so the users could stick with excel (which they liked) to store their data, but they could also view it in a way that made it easier to see the complex relationships between the different excel file stores they were using.

In this case, with relatively computer newbies, I'd worry that excel would be too much trouble.

It wouldn't be too much work to download VB express and just do the whole thing in that. It has built in database capability, and you could design the entire user interface, without all the complex menus in excel, access, or other general purpose programs. And if you can do it in excel, chances are good with a little reading online doing it in VB isn't that much of a stretch.

I second Evernote but I am kind of one an Evernote kick right now. It is free (with a max upload limit per month) but if you are not doing lots of audio or video storage, it is perfectly usable for relational storage (and searching). If you really want to explore it as an option, take some existing logs and try adding some notes to the default notebook (you can make more) and some tags and see if it works for what you need.

I third Evernote.

Each user would have their own Notebook, with the first Note being unchanging personal information. Subsequent notes could be added as needed. The one thing that this does NOT do is automatically show who authors the note, so they'd have to be signed. You can set an author, but you have to click and fill it in by hand, which means you could theoretically change authors and that could be bad.

So while it's easy for what you need it's by no means a secure or auditable solution.

stienman said:

It wouldn't be too much work to download VB express and just do the whole thing in that.

Click to expand...

That's part of the reason I suggested Xojo. It is visually very similar to VB, but more cross-platform. That is, you get to design the interface as well as the functionality.

--Patrick

Designing a database for what you're describing would be tricky because it sounds like you would need what's called an EAV table. This stands for Entity-attribute-value, its a way of handling datasets with a lot of variety in input, which seems like what you would need when you have notes that could effect multiple people. The problem is that EAV is an incredibly bad way of designing a database, and has very serious long term problems. For that reason, I would suggest checking out Evernote first. Otherwise, I would suggest using Access. Its by far the easiest database front end out there, and it includes a form builder which is pretty decent.

Sure, its not the best application for enterprise level apps, but its fine as a starting place. If you ever get too large you can always export the data to a proper SQL server and switch the front end to a proper Visual Basic thing. But all of this will require a fair amount of work from someone pretty knowledgeable.

Excel is often an easier solution because it allows such fine grained control. If you aren't writing queries and whatnot Excel would probably work fine.[DOUBLEPOST=1390850219,1390850180][/DOUBLEPOST]I re-read your post. Use Pivot Tables in excel. That should do what you want.

Just lock it down so that there is a master sheet with only one authorized user, or something like that.

Pivot tables would work, but not if they wanted to continue adding notes.

Necronic said:

it sounds like you would need what's called an EAV table.

Click to expand...

I'd have a different model, myself. One table for caregivers, one table for patients, one table for notes, and one table to link notes to patients. The caregivers would just be the employees entering the notes. The patients would just be the people being cared for. The notes would be connected to the caregiver, and hold the note itself. The link table would connect the patients to the notes.

So you could put in a single note, then "attach" all the patients it relates to. A search could bring up a patient and all the notes attached to them in the last period of time, or looking at a note would show all the patients it applies to. Could probably skip the caregiver table if you didn't need auditing, and just have another field in the note for a caregiver name which you could sort/search on.

Caregiver
- CaregiverID
- Name

Patients
- PatientID
- Name
- Active (as people move through the program, they are marked inactive when they leave so the selection of patients when making a note doesn't grow too large, or get confusing - which John?)

Notes
- NoteID
- CaregiverID
- DateTime
- Note

Link
- NoteID
- PatientID

It's a simple, limited database, but should provide the needed tracking, and is a good foundation for further additions.

Ok, yeah that makes a lot of sense and avoids EAV. I guess I was thinking of having the link be NoteID and CaregiverID, which would have left PatientID in the Notes table. Your model is way better.

Dave said:

Pivot tables would work, but not if they wanted to continue adding notes.

Click to expand...

I've never messed with Pivot Tables in Excel much, I thought you could just add stuff to them as you went.

Necronic said:

I've never messed with Pivot Tables in Excel much, I thought you could just add stuff to them as you went.

Click to expand...

Pivot tables are super duper reliant on the data fields. Like if you rename a column it breaks the pivot until you reattach the data. So making notes in the data page would be problematic and overly complex for this user base.