Microsoft Access WoB Database

[Pagerunner]

I've started playing around with Microsoft Access to try to create a WoB database of my own. The online help tools have been fine, and I've got the database structure down pretty well. (Main table for WoB's, secondary table for interview/signings, and then five tables of tag categories, Series, Shard, Planet, Character, and Magic. Each WoB can be associated with a signing and any number of tags.) It's to the point where I'm comfortable actually putting some data in, so I've got two months of Brandon's Reddit comments and one AU signing in there, and the information is structuring the way I want it.

But enough about the good stuff. Right now, data input is extremely clunky; I have to switch between up to six tables in order to get all the tags in, so I'd like to be able to get a form together to put everything on a single page. However, I can't find any good training on forms, either on Office.com or on Youtube, since it's probably all done in macros and that's beyond the scope of just forms. I have some experience through my job in designing InfoPath forms, and I was hoping this would be similar, since I can envision how to build all the functionality I want in InfoPath. However, Access forms look like there are a lot more behind-the-scenes macros, so I'm not sure how to go with this.

Improving querying is another item on my agenda. I think I can get a functional query, to sort by a single tag, but it's a little clunky (need to go into design view every time I want to change the character), and I don't think I have the ability to combine multiple tags (i.e. everything about Hoid on Scadrial with the Candlestick), since my queries are using junction tables and it's literally separate lines for the WoB#13/Hoid connection and the WoB#13/Scadrial connection.

So, is there anyone out there who has any experience with Microsoft Access, specifically adding functionality to forms and queries? Anyone know any good training tools? Like I said, I have some experience with the now-discontinued InfoPath, so I don't think what I'm looking for is outside the ability of a database tool like Access. I'm just not sure how to actually implement the functionality.

[Argent]

I believe a tag-based system would be much better than a SQL database. WoBs are more like... memos, I guess, than database entries

[Pagerunner]

On ‎1‎/‎23‎/‎2017 at 9:41 AM, Argent said:

I believe a tag-based system would be much better than a SQL database. WoBs are more like... memos, I guess, than database entries

I'm not exactly sure what that means; I'm not well versed in IT terminology. It's more of a 'if you have a hammer, every problem seems like a nail' situation, where I've seen some utilization of databases at my job (not Access, but that's what I've got on my personal machine), and the idea popped into my head. So, I'll try and explain myself, and you can tell me if it makes sense.

One concern I have with Theoryland's Interview Database (aside from not including Reddit and Twitter, which is understandable) is that there isn't a really good way to approach it without being too granular; there's no Advanced Search that would let me say "show me everything relevant to Stormlight Archive." But keyword searches won't necessarily be good for those kinds of things.  Let's say I'm looking for information on the world of Roshar, and I do a keyword search for Roshar, I'm getting stuff like Entries 31 and 154, which isn't what I'm looking for, but missing an important piece of history from Alethkar.  If they were going to put in enough keywords into Theoryland to allow this kind of dividing of the information, there would be a lot of tags floating around at the bottom of WoBs, and users would need to know exactly what to search for, like "World-of-Roshar." In Access, I can do these cross-references in their own table, and it's all going behind-the-scenes until you actually use it to search. When I narrowed down the ways I would want to parse WoBs, I thought of five main ways: Character (done well in Theoryland), Magic (reasonably well), Planet (lacking), Series (impossible), and Shard (well). Giving them each a table of their own, and then adding in a cross-reference table between the WoB table and the tag tables, lets each WoB have a ton of tags. It also allows consistency with the tagging, so you don't miss stuff that got tagged "Surgebinders" instead of "Surgebinding." Although, I did allow for free-text keywords, for stuff like "Cognitive Shadows" and "Future Plans," since there will be plenty of stuff that won't fit neatly into one of those categories. Oh, and filtering by date, in case you want to see everything said about Kelsier after BoM came out.

There's some other minor functionality that I'm planning on using from the get-go. I'm putting in the ability to archive WoBs without deleting them. (At some point, the WoBs we have about Vasher using Stormlight will make their way into the books, so I won't include them automatically in searches.) Tagging RAFOs, so you can exclude them if you're just browsing for cool information. And marking WoBs as 'Notable,' for the ones it feels like I keep having to pull up and quote (like the 'Survival' Shard's intent, or the force that opposed Adonalsium.) And I, personally, want to exclude all the "Conflux" questions from signings that are along the lines of "what would happen if a Surgebinder with Breath tried to use Stormlight to Awaken an electrum metalmind that was made with silver from Threnody and imprinted with a functional Aon that was drawn using invested sand from Taldain and..." (I exaggerate, of course. There's a lot of good value in some of these questions, but there's also a lot that I don't find terribly enlightening.)

And then, I have some longer term plans in mind. I don't expect I'll be working on the WoB portion of the database for years and years (either I'll get it, or I'll give up), but I've got some further phases in the unlikely case things turn out well:

• Notable passages from the books will probably be the first expansion, utilizing the same tag structure but having a different source structure. I can always find an excuse for a reread.
• Then, I'd like to create a List of Questions, which can cross-reference to the existing tags, and then to a WoB or a passage once a question has been answered. That way, it's not just "what are the open answers," but could turn into a FAQ as stuff gets answered. (This would greatly utilize the Conflux marker that I mentioned above, and would probably have a hidden FAQ marker I could use to put together an actual FAQ query for newer users.)
• If I'm really feeling ambitious, I'd make a theory database, too, that draws from multiple WoBs and passages. It's kind of like what I said above for Theoryland's database, I don't think there's an easy way to see what theories there are (i.e. what theories are there about Kelsier). And the only way to browse to see the best ones is to find a good theory and track down Espoused Theories in peoples' sigs. Some good old ones get lost as members become inactive.

These aren't priorities, but they would need to utilize the connections that Access makes available. You'd be able to think of a question and go see that it's been answered, read the WoB, say "Wow, that's a cool fact! I wonder if anyone built any good theories off of it," run a theory search on that WoB# and see what theorists have said about it or see what passages can influence interpretations of that WoB.

That being said, I'm pretty sure I can get all of the functionality I could possibly want for that kind of cross-referencing in Access, and most of the struggles I'm coming across have to do with forms, queries, and user interface situations. Buttons are nice, but I have no idea how they work in Access forms. (Incidentally, I managed to solve my original question about forms using sub-forms, so now I can add a WoB and all its tags from a single page, and it all fits on the small screen of my Surface 3. With no buttons whatsoever. And I got variable queries down, as well, so that's 2 of my 3 original questions solved.) But, even if things fall apart in the long run or this isn't the most effective way of organizing the data, my current focus (collecting Twitter and Reddit cosmere tidbits into a single location) has been greatly enhanced by what I have working so far, and push comes to shove I can export the quotes and links to Excel for someone else to play with. Sure, maybe someone smarter than me could come up with a better system. But, I don't have any skills outside of relatively basic Microsoft Office, so that's why I'm using Access.

Granted, I'm not sure how well an Access desktop database could transfer to other people using it over the internet, since there's no Access online like there is Excel online. Web Apps look like queries and forms change pretty drastically, but the database structure wouldn't change. Also, I've saved a link of a company who says they have a free option that can convert an Access database to something embeddable online, so I have hope it would fit that need. But, if you know of anything better that than Access that 1) is free, 2) enables collaboration, and 3) can do what I'm looking to do, by all means, help me out before I go too far down the rabbit hole.

[Mestiv]

Do you know any programming languages? 

[Pagerunner]

9 hours ago, Mestiv said:

Do you know any programming languages? 

I can make a simple function on my trusty TI-83. That's the extent of my knowledge.