Combine Different Query Columns into One on Form

[Comtech]

I do a lot of woodworking and I have a lot of woodworking magazines that are PDF files. I want to create a database to store all of the topics for each magazine and then be able to search for a specific topic. I have atable containing a list of all magazines (just titles of each), called “tblListofBooks”. I have also beguncreating a table for each magazine with the topics for that magazine. So far I only have 2 in case I’m doing this incorrectly.
Each table is as follows:

tblListofBooks
BookID


BookTitle
PageNo

tblBook1
BookID
BookTitle
PageNo

tblBook2
BookID
BookTitle
PageNo

Each remaining book will be set up the same, and they each have a relationship to tblListofBooks, joined by BookID.

Here’s my issue. I have created a form where I enter a key word to search for. This opens a query, which uses “Like” to find any matches. The query works ok, but the topics and page numbers for each magazine appear in different columns (tblBook1.topic, tblBook2.topic, etc). This is fine if it was only a few magazines but I have about 200, and this could get congested. What I would like to do is, create a form and have all book titles in one column, all topics in one column, and all page numbers. I have spent many hours at this, so any help would be appreciated.

[ranman256]

why do you have a table for each book? you should have 1 book table, all books listed in it.

[Comtech]

I had thought of that, but that would be a lot of entries into that one table. I wasn’t sure if that was recommended or not, because there may be times when I want to access individual magazines. Thank you for your reply.

[Micron]

Definitely recommended. Suggest you read up on db normalization.
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

[Comtech]

Awesome. That would make it a lot easier. Thanks for the help.

[Micron]

Are you not going to need a 'keywords' field with keyword values for each topic?
You want to find the article you once read about designing a router overarm so you type in "router, overarm" (or maybe gooseneck) and find records with those keywords in the keyword field? If you have not thought about how you're going to use the db as opposed to just how to design it, you need to reflect on how to get data as well as how and where to input it.

BTW, I'm also a bit of a woodworker. If you want to discuss/compare, send me a PM.

[Comtech]

Micron,
The only thing I want to be able to use the database for, is to search for articles. I will still use the keyword to achieve this, and then have a query output all articles with that keyword. I will also have a button that I can use to open the magazine if I wish.

As for the woodworking part, I’m more of a hobbyist who owns a bunch of power tools. I would love to discuss ideas. I was part of a bunch of groups on Facebook, but I accidentally shut down my account. I’m back on now, and will be joining them again.

[davegri]

Comtech-davegri-v01.zip

Here's the way I would approach it. A main form for the books (Magazine names) and a subform for the articles. I didn't address any keyword search logic.

[Micron]

A listbox bound to that query might be an option. If you selected more than one article to review, listbox code could open all of the associated pdf's. One trick might be to only open a pdf once, or perhaps there is a way to open a pdf to a particular page. If there is, I don't know. The first page covering the article associated with the topic would have to be part of the record and even if 2 articles are in the same magazine you might be able to load the magazine twice, each one at a "bookmark". Just throwing out ideas here, so if none seem attractive, just ignore!

BTW, I'm not a professional woodworker either. Just a hobbyist for the most part.