directormac
Occasional Presence
- Local time
- Today, 10:07
- Joined
- Oct 24, 2001
- Messages
- 259
Greetings Access Wizards – been many years since I used Access, started to seem like the right tool again LAST February but then… well, you know, and my work shifted radically. Now things are getting back on track at my desk, so I’m again trying to scrape the rust off my brain. Which leads me to the following. I’ve searched around a bit but haven’t found anything that hits this quite right (though maybe I’m not using the right search terms?)
I have a need to track things that have multiple characteristics, any of which may or may not apply to any particular thing. Further, each characteristic can get measured/tagged for in a different way. Just to make things even more fun, at any time a new characteristic may need to be added to the set and applied retroactively, or not, to things already being tracked.
For ease of discussion, let’s say you started to make a list of animals in the forest. And you wanted to be able to record/report on them by things they can do, or are, or such. So you find a parrot, and parrots can fly, so “Can Fly?” is now a yes/no characteristic you want to track for all the animals. Next you find a cat, and you see it’s speedy, so you decide you want to know how fast any animal is, so you add “Top Speed” as a numeric characteristic, fill it in for the cat and go back and fill it in for the parrot. Next you find a whale… and so on.
Now one way to do that would be to add a new field to tblAnimals everytime you needed to add a characteristic, with the appropriate field type (bool/int/txt/etc.) to measure that characteristic:
But that’s very high on the PITA factor and anyway means re-doing a bunch of stuff (forms, queries, reports) every time you have a new characteristic to add. Plus it means the average user can’t add any characteristics and has to wait for the DB admin to do it, causing delays (as well as being high PITA).
A better option all around is obviously to break out the characteristics, almost certainly with a many-to-many join table so we can mix and match as we like.
BUT.
We have an issue, in that all characteristics are NOT created equal. The fact that they are different data types means we can’t put them all in the same place, at least at first glance. We’d need a table for each data type, right? And then put everything together with queries, something like:
That’s going to hit some limitations pretty quickly. For one thing, it doesn’t give us a way to differentiate between “unknown” values vs. 0/FALSE values. For another, it restricts our types of answers to the built-in data types. I imagine there will be a bunch of characteristics for which we’d like multiple-choice metrics, like “low/medium/high”. But we can get around both those by using text fields with validation rules, I think?
I keep wondering, though, if there’s not a more streamlined structure possible. I haven’t gone too far down this road, but I’m thinking something like:
This would require a set of VBA Get and Set functions to enforce answer type integrity and parse the results where needed. Almost every answer type would boil down to either (1) some free value that could be stored as text and converted to another type (e.g., int) by the function as needed; or (2) one or more choices from a list of distinct values (e.g., “yes/no/unknown” exclusively, or “walks/flies/swims/burrows check all that apply) which could likewise be stored as text and parsed by the function. All of which would mean that the DB admin would have to write a new function anytime a new answer TYPE was required, but once the tool is built those occasions should be relatively rare. Meanwhile, common rubrics like “low/medium/high/unknown” could get applied to any newly required metrics freely.
On some level I’ve been working this post out as a way to brainstorm which path to go down. As appealing as I find the unified structure idea (fewer tables), I’m also aware that it’s a lot more complex in setup, and likely to exhaust my meager VBA skills. As I was typing that last paragraph, it occurred to me to wonder: is a hybrid possible, with one set of free characteristics and one set of multiple choice characteristics, each handled by a single pair of set/get functions?
Thoughts/reactions/advice are very welcome. Obviously, this is a design approach that I need to settle on before I go too far down any implementation. I vaguely recall a lot of discussion here a number of years ago around creating quizzes/tests for students that felt applicable when I was working this out, but which I haven’t been able to find an applicable thread for now.
So… yeah… thoughts? Anyone set up something similar with great success?
I have a need to track things that have multiple characteristics, any of which may or may not apply to any particular thing. Further, each characteristic can get measured/tagged for in a different way. Just to make things even more fun, at any time a new characteristic may need to be added to the set and applied retroactively, or not, to things already being tracked.
For ease of discussion, let’s say you started to make a list of animals in the forest. And you wanted to be able to record/report on them by things they can do, or are, or such. So you find a parrot, and parrots can fly, so “Can Fly?” is now a yes/no characteristic you want to track for all the animals. Next you find a cat, and you see it’s speedy, so you decide you want to know how fast any animal is, so you add “Top Speed” as a numeric characteristic, fill it in for the cat and go back and fill it in for the parrot. Next you find a whale… and so on.
Now one way to do that would be to add a new field to tblAnimals everytime you needed to add a characteristic, with the appropriate field type (bool/int/txt/etc.) to measure that characteristic:
But that’s very high on the PITA factor and anyway means re-doing a bunch of stuff (forms, queries, reports) every time you have a new characteristic to add. Plus it means the average user can’t add any characteristics and has to wait for the DB admin to do it, causing delays (as well as being high PITA).
A better option all around is obviously to break out the characteristics, almost certainly with a many-to-many join table so we can mix and match as we like.
BUT.
We have an issue, in that all characteristics are NOT created equal. The fact that they are different data types means we can’t put them all in the same place, at least at first glance. We’d need a table for each data type, right? And then put everything together with queries, something like:
That’s going to hit some limitations pretty quickly. For one thing, it doesn’t give us a way to differentiate between “unknown” values vs. 0/FALSE values. For another, it restricts our types of answers to the built-in data types. I imagine there will be a bunch of characteristics for which we’d like multiple-choice metrics, like “low/medium/high”. But we can get around both those by using text fields with validation rules, I think?
I keep wondering, though, if there’s not a more streamlined structure possible. I haven’t gone too far down this road, but I’m thinking something like:
This would require a set of VBA Get and Set functions to enforce answer type integrity and parse the results where needed. Almost every answer type would boil down to either (1) some free value that could be stored as text and converted to another type (e.g., int) by the function as needed; or (2) one or more choices from a list of distinct values (e.g., “yes/no/unknown” exclusively, or “walks/flies/swims/burrows check all that apply) which could likewise be stored as text and parsed by the function. All of which would mean that the DB admin would have to write a new function anytime a new answer TYPE was required, but once the tool is built those occasions should be relatively rare. Meanwhile, common rubrics like “low/medium/high/unknown” could get applied to any newly required metrics freely.
On some level I’ve been working this post out as a way to brainstorm which path to go down. As appealing as I find the unified structure idea (fewer tables), I’m also aware that it’s a lot more complex in setup, and likely to exhaust my meager VBA skills. As I was typing that last paragraph, it occurred to me to wonder: is a hybrid possible, with one set of free characteristics and one set of multiple choice characteristics, each handled by a single pair of set/get functions?
Thoughts/reactions/advice are very welcome. Obviously, this is a design approach that I need to settle on before I go too far down any implementation. I vaguely recall a lot of discussion here a number of years ago around creating quizzes/tests for students that felt applicable when I was working this out, but which I haven’t been able to find an applicable thread for now.
So… yeah… thoughts? Anyone set up something similar with great success?