Question for clarification:
a form will display products in that machine with a empty box to enter the amount of stock required to fill machine.
Is this where you would indicate how many you HAD to put into the machine? Like, in slot C8, currently containing Mr. Goodbar, on this date, I had to refill with 10 units. So in other words, an after-visit fill-in-the-blank item? And the info you get gives you history that might lead to more efficient product re-ordering, but CERTAINLY helps you with knowing how much you should have seen in income from the machine for that date?
Just asking for clarification of intent, not that there is anything wrong at all with the idea. If we understand your intent better, we can advise you better.
I gather that you are in the early stages of trying to put this together. This is a CRUCIAL time in your project because a good or bad design will make or break the usefulness of this database. So NOW is the time to be ultra careful. Not as in "so careful as to be frozen for fear of error" but just "careful enough to spend some time thinking about each choice."
You have gotten some concepts that are helpful and on point. I will not try to confuse you with more specific design ideas. Instead, I'm going to give you some analysis tips for this stage of your product.
You are in essence building a "model" of business, for which Access will help you build the tracking tool for that business model. Just remember that YOU are the subject-matter expert for your business. Access is the subject-matter expert ONLY for the narrow topic of building database components and fitting them together.
Your "model" will contain things that you track. Some of them will be physical, like the machines you manage or the product you load to the machine or the physical site location for each machine. There will be more items than this, but what I listed is enough for this concept: Each class of item = machine, product unit, physical site - is a business entity. Each entity should have its own table, where all entities of the same type are listed. Then you can establish the relationships among entities. Like "product" goes into "machines" and "machines" reside at "sites" and "customers" own "sites."
You can have abstract entities such as "visits" that have dates and sites. You can have abstract entities like "invoices" listing product units loaded to a machine on a particular visit. It is up to you as to what and how you track this.
In order to do this right, you need to do some reading. I'll name some topics. When looking over the other suggestions, take some of these ideas with you:
- Database Normalization - a way to assure the "purity" of your tables so that when you have to make comparisons, it is always "apples" to "apples"
- Relationships - in the database sense of the word, a description of HOW two entities relate to each other. Here is where you have dependent or independent entities.
- Junction Tables - a database term for a common way to implement something called a many-to-many relationship.
You can use a web search on those terms, or you can use the SEARCH function of this forum, which is third from the right in the thin blue menu-item ribbon near the top of each page.
NOTE: If you search the forum, you don't need to specify "database normalization" but if you search the web, you need to qualify it. "Normalization" in general can include political, mathematical, database, and several other topics.
