In my last post, I talked about the kinds of information I'm gathering and how it's structured. But the interface you saw there isn't very conducive to quick browsing – the WoB text field would need to be obscenely wide, there are some fields we don't particularly care about most of the time, and the information was spread across several tabs. Thankfully, there's a better way built into Access to view information in a table: Forms.

In an Access form, you basically view a single row at a time, but spread the fields in that row all over the page. You can make them bigger, you can make them smaller, you can add in text boxes with explanations, you can choose which fields you don't want to display on that particular form. But, at the end of the day, it's still a direct way of getting at the data in a table, it just looks different.

Here's the WoB input form I've been using:

If you look back at the table, which I included in my last post, you'll be able to identify that all the fields and checkboxes in the top part of the form correspond to columns in the table. But that's only half of the form; what's going on with the tags at the bottom of the form?

One other huge thing you can do with forms is create subforms, that will pull in data from a second table that is connected, in some way, to the first table. This lets you look at multiple tables at the same time – absolutely essential, since as I said in the post about the back end, I need work on 8 tables at once when adding WoBs (the WoB database, and the 7 Junction tables). I made subforms for each Junction table, and threw them on the bottom.

So, when I open up the form, I'm looking at a particular line from the WoB table. Each WoB has a unique WoB Number, which is what's referenced in the 7 Junction tables. So, when I set up the subform, I designed it to only include the lines where the WoB ID in the Junction table entry matches the WoB ID in from the WoB table. That means it also brings up every line in every linked table that shares that WoB ID. Access is actually incredibly smart when you know just what to ask it to do, so when I add a new tag in a subform, it automatically includes the WoB ID of the quote I'm looking at on the form at that moment. It makes my job much easier – no need to remember the WoB Number, remember who the quote is about, then open up the WoB/Character table and type in the number and the characters, then go back to the quote and do the same thing for the other tag categories.

So, that lets me get information into to the WoB database in a timely manner. Copy-paste the quote, click a couple checkboxes, put in the sourcing information, and then type in any tags I see. All from one screen. That's all well and good, but how do I get stuff out of it? I can use something called a Query.

A Query will basically build me a new table out of pieces of other tables. I get to say exactly which pieces come along: bring columns A, B, and H, and only bring rows where the date is in 2016. This lets you combine pieces from multiple tables, as long as you properly define the relationship. So, I can also say, bring along the Interview Date column, and for any individual row it would match the Source.

I have a lot of work to do on actual implementation of queries, but I've developed a couple of simple ones so far that are a 'proof of concept,' to show that the database structure will allow for these searches. Here's are two views: first is how I'm telling Access to run that query, which pieces to pull from which tables, and the second is what the query returns. So, here's the Character Search Query:

When I bring up this query, it prompts for a character. When I type in a name (say, Kelsier), it looks through the Character/WoB Junction table, finds all WoB Numbers associated with Kelsier, and then displays information about those WoBs. Right there, every WoB that I've tagged about Kelsier. (It doesn't actually display the character on the final table, since that would be redundant. It just uses it to filter.

But that output sheet is hard to read, isn't it? When I had that problem earlier, with my Tables, I made a Form for easier viewing. Can I do the same thing here? Of course I can! Here's my Character Search Form:

This form utilizes an Access function called Split Forms, where half of the form is the original table, and half is the form itself. This can be used to quickly browse through the results, without having to click through each one individually. (Still some functionality to be worked out; I'm thinking of adding a short description for each WoB, something that will fit easily in the table. That will involve a lot of rework, but I'll most likely have to make a second pass through everything, anyways, to double-check my tags.)

One thing you might be wondering about if you're astute and paying attention (nice job!) is the date and the hyperlink. A WoB can have a source from the Source Table, or just an individual hyperlink. The WoB table has a spot for the date, as well; how do I avoid duplicating information?

You're right, those are duplicate fields for anything from a signing, and the Source field isn't important for stuff directly from Twitter or Reddit. For interviews, I leave the Hyperlink and Date empty for the individual WoBs; that information is taken care of by the Source. For stuff from social media, I made an entry in the data table for "N/A," because I had problems with an early query when I left that field blank. I wanted to leave all WoB's, social media and signings, all in the same table; otherwise, each WoB wouldn't have its own unique WoB number. (There'd be Reddit/Twitter Row #1, and Interview/Signing Row #1).

When I run a query, though, I can combine these columns into a single field. In Excel (which I'm sure more of you are familiar with), you have all these calculations available to you. In Access, calculated fields are very bad to have in tables (although they are possible), because if you change information the other data might not automatically update correctly. But, when we run a query, we're building it fresh every time, so I design a query to look at the WoB Type and then choose whether to look at the WoB's date or the Source's date.

I've done the same thing for the hyperlink, but it doesn't work right. Hyperlinks are apparently a wonky kind of data in Access, and don't play well with calculations. I'm looking into a 'combined query,' where you can pull two separate lists and then add them together, as long as their column types match. So, I can pull a query for Reddit/Twitter with the individual date, run a query for Interview/Signing with source date, and then just stick the query results together end-to-end. That wouldn't use a calculated cell, so the hyperlink should work. I think.

There's a lot up in the air with queries and interfaces. The reasons for this are twofold: advanced queries require coding experience I don't have, and I'm not sure how well these queries would carry over if I find a suitable method of sharing this database.

First, advanced queries. Obviously, I'd want to be able to say 'show me everything about Vasher in the Stormlight Archive,' if I'm looking for what Brandon has said about Vasher on Roshar. I could throw in all the parameters into a single query, but then there would be, like, a dozen prompts for each search, which is way too many keystrokes to be reasonable, and I'd have to include instructions on how to fill in the prompts to not filter that column. I could design individual queries for each combination... but that's 7*6/2 = 21 different combinations. And then there's a triple intersection, which has 35 combinations. Quadruple also has 35, and the pattern reverses from there, so that would be 64 queries. But, each of those queries would need to have the ability to filter archived posts, RAFOd posts, Conflux posts... it gets messy. Access lets you write and run code, which I'd probably  use to take the first option (a ton of prompts) and have Access do all the work (of filling in those prompts based on which boxes you check on a form). (Like I said above, it's very good at doing what you ask it to, as long as you know how to ask. I don't know how to ask it to do what I want. I know how to ask people to do it on their own when they search, but it's too much work. Access won't complain about having to do all those clicks, but I know you all will. No offense.) Only thing is, I don't know how to code, so VBScript is 100% unfamiliar to me. I'm sure there are tools to learn online, but that's a big time commitment. I'll save it for later, but if you can use Microsoft VBCode, feel free to reach out to me.

But the second reason is, even if I get an advanced search query operational, I don't know if it will carry over if I find a way to share the database. Access itself can publish a database as a web app (I don't have anywhere to host it right now, which is a separate problem), but it doesn't appear to have the exact same functionality with regards to forms and queries. I might also look into a third-party database software that can import stuff from Excel, in which case the back end will probably look very similar (it's just tables!), but the front end (forms and queries) might be drastically different. Whatever solution I might be able to come up with for a working desktop database, I might have to redo completely for an online implementation. And, if I don't get anything online, if it turns into my personal repository, I don't need this particular functionality; I'm very comfortable with the filtering, like in Excel, so I know how to do everything I want to do.

But the functionality is possible, which is what's important for now. The input form, that's something only I'm getting a ton of use out of building the database in Access, so it's paid for itself already in time savings. I know that, wherever the database winds up, there will be the ability to query it, but I'm waiting to do a lot of the detailed query work until I get to a point I'm evaluating how to bring the database online.

Part of me hopes that I use existing Access forms and queries in my final solution. At my job, I interact with my company's central database, which has obscene amounts of data in it (thousands of tables and reports), and I've seen a lot of really cool functionality. It's not Access, it's something much more powerful, but it's phenomenally easy to navigate between financial transactions, asset structure, and maintenance scheduling, all by just clicking on fields. As an engineer, and not an IT guy, I find moving around this massive, massive database is... well, it's still a pain at times, but I'm getting better, and I could easily see how it would be a lot worse. For Access, I know you can tell it to do stuff when you click on a field or push a button; I would need to learn a lot about coding stuff to utilize that functionality effectively.

Part of me hopes I find something that does it all for me. Because that's a lot to learn. And I have enough to learn already. Even a lot to unlearn; at work, I've used Microsoft InfoPath to add and interact with some data before. InfoPath basically lets you make forms, it's all front-end, and all the functionality is buttons and what happens when a cell gets populated, so it was very easy to make buttons and commands. The coding for that was very simple, but limited to the choices it presented. Also, because it was separate from the tables you were working with, you needed separate commands to call data to view or to submit data to a table. So, it was a paradigm shift for me in Access, because there's no middle step, when you open a form you're directly editing the data. But Access also is much hard to get buttons to do exactly what you want, since they all do it in VBCode. Very powerful if you know what you're doing, but InfoPath was better for me as a fairly uninitated user.

Okay, that's enough of me rambling. The front end isn't as well developed as the back end, but that's intentional. I'll probably need lots of time to get it where I want it to be, or maybe find someone much more knowledgeable than me. For now, I can put data in and get data out, but I'd want it to be much easier to do the latter if I find a way to make this publicly available.