Building a database to track inventory

[hle0000]

I have very little experience with building databases and I am grateful for any help anyone can offer...

I work for an art museum, and I am trying to build a database to help keep track of our stock picture frames. We will soon be getting a full count of each type and size of frame we have, and will be applying barcode labels to the back of each frame. We will use MS Access to know when a frame is available for a new project, or if it is in use in the gallery or out on loan. As we prepare for each project, we will "check-out" whichever frames we need - kind of like in a library. As a work is unframed, after it returns from loan, or from being on view in the gallery, we will "check-in" the frame so that it can be used for a future project.

I got as far as creating a table of our stock frames (I'm currently working with fake data for now until we get an actual count of our frames), as well as a form that we can use for our initial frame count, where we can assign a barcode and capture data about the frame size, style, etc.

I have also created a table of our upcoming projects with begin and end dates, and a query with an expression that shows a given frames availability based on its current location. So if it is in storage, it is available, if it is in the gallery or out on loan, it is unavailable.

What I'd like to do next is create a form that we can use for the check-in and check-out process. This is where I am getting hung up because I want to have a field with a drop down list of the project names (from the project table), but the rest of the data will be coming from the stock frames table. I tried this already, but the form was not working as I wanted it to work. The record count at the bottom showed that it was primarily talking to the project table, where as I want it instead to work with the frames table. Also, when I would try to click on a selection in the drop down for the project field, I could not select anything and I would just hear a dinging noise. I think I just need to start over.

Thank you in advance for any help you can offer.

[NeoPa]

You may need to start over. You may not.

It all rather depends on what you have so far. This is a technical question that requires a technical answer. So far though, your explanation has been a bit anecdotal. General overview description without detail.

That's fine. I suspect you're just feeling a little daunted at the task & possibly wondering if it can be done.

Yes. It can be done. That's something you can relax about. How to do it does rely on your understanding of what needs to be done though. This is why the consideration of the project is so critically important. Any time spent getting this right is time saved later on in spades.

My suggestion to you at this stage is to revisit your table structure. Is that capable of handling what you need from it. Think it through carefully. Let us know.