Choosing Between One Menu, or Three Separate Menus for Form Design

[tatihulot]

Hello, I’m using Access 2021. I’m not really an expert in databases, so I’m hoping I could get some advice on how to approach my design with an eye toward simplicity.

It is intended to keep track of where my digital movies are stored.

In the menutest.accdb file, I have one drop down menu with all of the possible locations. I set it to allow multiple selections.

I’ve also created three separate drop down menus, each permitting multiple selections as well, each containing a different type of storage: External hard drives, MicroSD/CD/DVD, and USB Flash Drive.

I’d like to know if you think I’d be better off using the one single drop down menu that contains all of the locations, or if you think the three separate ones would be preferable.

Personally, I prefer the three. If I use the one drop down menu, it’s sorted in alphanumeric order. That’s fine, I can deal with that, but I’d prefer each type to be together. Also, all of the selections are in one long box, which I think makes it just a little more difficult to read. For that reason, I prefer the three separate ones. That option seems more organized, at least to me. Do you agree?



But not being really well-versed in database design, I don’t know for sure which option would be more practical. For example, would using the single menu be easier when I do queries? Or, will doing queries using three separate menus not be a big deal?

Whatever advice and/or constructive criticism you’d like to give will be appreciated. Thank you. Jd

PS: The attached .zip file contains two rough drafts. One is just for this experiment, named menutest.accdb. The other, details.accdb, is more developed. Please feel free to comment on either or both, if you so choose.
menutests.zip

[June7]

First, you are using multi-value field for Location. I NEVER NEVER use multi-value field.

Second, media inventory database is a common topic and many examples can be found. For example, https://access.microsofttemplates.or...emplate-2.html

Search web and forum and will find many discussions, such as https://www.accessforums.net/showthread.php?t=40720

Third, if purpose of db is to know where movies are stored, is it really necessary to know size of device?

[Micron]

If location is an entity, that is a table. Every location is a record, not a field, but that is how you designed the table. Your approach requires that for every location you require another field and perhaps another control. If you add a location, that requires a redesign of the table at least, maybe the form and who knows what else. That is a common newbie design error so don't feel too bad about it.

You would actually have the same file on different storage devices? Then the better approach would be a main form/subform design. Choose a file and the subform shows all the locations. As for data input, the common approach would be cascading combos if you must compartmentalize location by device type and size.

[orange]

I am attaching this tutorial Entity_Relationship by Roger Carlson. It will lead you through a process by which you will learn and experience database design. If you spend 45 to 60 minutes with the tutorial, you will certainly understand the basics that can be used with other databases.
This and several other articles and links are in the Database Planning and Design link in my signature.
Good luck.

[tatihulot]

June,
I think I understand why you would never use that feature. If I explain what I’ve learned, will you tell me if I’m correct?

I watched a tutorial video on You Tube that explained relational databases for beginners (which is basically what I am).

Here’s the link for it. I don’t expect you to watch it, since you already know the information, but I want to direct your attention to just one portion of it, beginning around 18:55.

https://www.youtube.com/watch?v=mEakGW6vzpM

In it, the narrator says, “One fact, one field.” I’m not 100% sure I’m right about this, but here’s what I got from it.

You shouldn’t put more than one piece of information in one particular field. It makes processing the database to retrieve information inefficient.

The way I’ve designed the database, which is flat-file, one field can have as many as four facts in it: External hard drive, MicroSD, USB Flash Drive, and CD/DVD.

As the database exists now, if I have, oh, let’s say 5,000 movies, and I want to search for every movie that is backed up onto MicroSD and USB Flash Drive, the entire database has to be searched. Every field has to be searched. It’s unavoidable in the flat-file format.

If, however, the backup location is in its own table, and is linked to another table, such as Title, then only the relevant tables have to be searched. Not all of the tables need to be loaded and searched. Only Title and Location.

Am I correct about this? Am I beginning to understand this properly? Am I close, in the right ball park?

Thank you!
locations78.zip

[tatihulot]

You would actually have the same file on different storage devices? Then the better approach would be a main form/subform design.

Yes, I believe I understand that now. I believe it is called a Continuous Form? It makes more sense to me now, thank you.

[tatihulot]

Third, if purpose of db is to know where movies are stored, is it really necessary to know size of device?

For me, yes. I have several external hard drives, each one a different size. The same can be said for my MicroSD cards and USB Flash drives. They are different sizes. It will make things much easier for me if I know the size of the storage device a particular film is on, because I could retrieve that media more quickly.

[Micron]

You shouldn’t put more than one piece of information in one particular field

To me, that describes a csv field, not a mvf. The mvf mimics related table design as I alluded to - without having to do the work, but it has its pitfalls. On my phone so watching the vid is not practical now.
No, main form/subform does not necessarily involve a continuous form but it can be.

[davegri]

tat-davegri-v01.zip
This should get you started. The MVF is gone and we have a real many-to-many structure designed.



The subform recordsource is the junction table which allows you to pick location(s) for the movie currently on the main form.
If you need more detail for the locations, you could add location names, such as 'USB FLASH 32GB Blue case'

[tatihulot]

Thank you. I'm in the process of studying junction tables, which right now is a bit over my head. But this greatly helps. Thank you again.

[davegri]

Thank you. I'm in the process of studying junction tables, which right now is a bit over my head. But this greatly helps. Thank you again.

OK. I urge you to continue with the MTM junction table approach and strictly avoid multi-value-fields. Queries can be a nightmare with MVF while MTM will easily allow you to get desired your results.
The example DB is classic MTM Access structure.

[tatihulot]

I think I got it. I think I got it. Let me attach my revised database here, and let's see what you think.

revised.zip

[davegri]

I think I got it. I think I got it. Let me attach my revised database here, and let's see what you think.

I think you got it. Very nice. Added 3rd FK to junction table for size. Good work.

[tatihulot]

Thank you for the vote of confidence! I appreciate it.

I've already tested out a couple of rudimentary queries and reports, and they seem to work. Thank you, everyone.

[tatihulot]

Hi again everyone, I need a bit more help, please.
I decided to get ambitious and add another section to this database, for actors. I created a separate form and subform for this category.
When I try to enter an actor's name into the subform, I get a dialog box that says Field Cannot Be Updated.

I click OK and am able to enter a name. But when I try to add another name, this dialog box appears again.
Also, when I go back to double check the records, the actors' names I entered are not saved in the subform.
Would anyone please be willing to take a look at what I've done, and tell me where I made mistakes? It would really help.
Would I be better addding another subform to the main form, instead of creating an entirely separate form with its own subform?
Thank you.
actors1.zip