Solved Yes/No in Tables and Option Group in Forms (1 Viewer)

[wmix]

Hi Everyone,
Not sure if I did this correctly but I could really use some help ,thank you in advance! I apologize for writing a "book" but I think I need to explain what I'm doing in order for you to provide me with the help I need.

I am using access to try to automate a vending machine business. It's a small company but big enough that doing everything using Excel and manually is no longer an option. So I've started using Access. I've been able to do some things that have really helped my daily work routine but now I'm getting into more complicated tasks and I need assistance.

Part 1
I have a table called Machines. This table keeps track of multiple things.
1. Machine ID a unique number assigned to every single vending machine.
2. Truck (1, 2, 3) - we have three trucks out filling machines.
3. Fill Week/Day - this is where things get complicated and I need help.
We operate on a week/day system (A Week, B Week) / (Day 1, 2, 3, 4). Most machines are straight forward, they are filled either A or B week and on a particular day. However, we have some machines that are more complicated. For example some are filled both A and B week others are filled both A and B Week and on Day 1 and Day 4.

So I had to figure out a way to keep track of all this. I wrote out multiple ways to do this and finally I came up with a system of A1, A2, A3, A4, B1, B2, B3, B4. This is now how I identify the week and day the machine is being filled. My next problem was how to track it in access. Because it's a complicated system I cannot just have one column to track the "week" or the "day" that a machine is filled because some are filled more often. I tried a couple of different ways but ended up using Yes/No boxes. So I now have eight columns of Yes/No boxes in my Machines table and I have put a check in the box if the machine is filled that week/day.

Based on all this info, I was able to query by truck each day they filled a machine. So, though not "pretty" it worked.

Next I created a new table called Route Order. I would query by truck and then by the week/day that the machine was filled. I then sat and entered the order (numeric value 1, 2, 3 etc..) that we fill each machine. Again - I have no idea if I did this correctly, but it works.

But now I'm stuck. And before I go any further I need to figure out if I need to start over/redo the things above or if going forward is reasonable.

I need to now start keeping track of the amount of money each machine brings in. I figured I could created a basic form that can gather information easily from the user to pass to a query. So I created a basic form using option boxes:

Truck 1, Truck 2, Truck 3
A Week - Day 1, A Week - Day 2, etc..

Now I am really stuck. I cannot "pass" the information to a query because they are Yes/No boxes and I have 8 columns.

HELP - what would you suggest? I really appreciate all ideas. Thank you!

 

[The_Doc_Man]

Your first "task" in developing this correctly is to study "Normalization" using the SEARCH function of this forum, or "Database Normalization" for a general web search. NOTE that without "Database" in the general search, you will also see articles on mathematicial, political, and ambassadorial normalization. The latter are interesting - but at this time are useless to you. When you get some articles on a web search, read the ones from the universities and colleges, who often put study guides on the college web sites because the professors don't know of any books that say what they thought needed to be said. But there will be a pot-load of articles. If you feel that one isn't right for you, read others. It happens that sometimes a writer is too dry and you just aren't feeling it. But you will have plenty of good choices, trust me on that point.

You need to clearly understand the concept of parent/child relationships because right now you are describing single-table thinking for some of your tables. You have already discovered that queries in that format become a pure-D b**ch to manage. (That's actually one of the symptoms of tables built with flat-file thinking.)

I'm going to give you some design advice. You need to think along the lines of my two "Old Programmer's Rules." I'll explain.

#1 - If you can't do it on paper, you can't do it in Access.

You must lay out your problem on paper first because that is how you are going to identify data flow, trackable entities, and operational procedures that have to be simulated in the "business model" that you are building using Access. If you don't know how something works enough to put it on paper, then you are nowhere near ready to program it. Further, that documentation will help you later if you need to remember why you did something. Nothing will drive you crazier than looking at some "midnight oil" code and asking yourself... "Now what the hell was I thinking - or drinking - when I wrote this?"

#2 - Access won't tell you anything you didn't tell it first (or at least tell it HOW to compute it for you).

Access will not invent anything. It cannot independently search the web. It's not even that good on understanding what you want done. It can do math, but in the final analysis, it only knows what you told it, either in raw data or in procedures. So if you need to see reports of X, Y, and Z, then you need ways of either inputting X, Y, and Z, or inputting something else that can be computed to create X, Y, and Z. This can sometimes mean working backwards to ask questions like, "If I want to see X, what inputs will will I need in order to do that?"

Here is a hint to contemplate. If a machine is to be serviced, you make a machine table and a separate ServiceSchedule table (call it something shorter if you don't like typing.) Then the machine's unique identifier, whatever it might be, becomes the Machine table's prime key - and becomes a foreign key in the schedule table. Access can then do some useful things to build lists of days on which a machine should be serviced. And you only need to have one entry per schedule table to have a machine serviced once per service cycle. But if it is a special, you have two days for the one machine in the service list.

A query can easily handle such things using GROUP BY or ORDER BY. How you actually encode a particular day is up to you, and if you want to use Week A, Week B and Day 1, Day 2, Day 3, Day 4 - then OK there too. But here's the catch. You have to always, ALWAYS, ALWAYS let the data drive the design. Otherwise you risk having the problem of "the tail wagging the dog." This is why you need to research/define your requirements FIRST. Don't pick a design representation because you think you can do it. Pick one that you think conforms to the reality of your problem.

I know you are in a hurry to get things up and running and I have been there often enough to sympathize. HOWEVER, it is exactly at this point in your design process that you make mistakes - by allowing yourself to be rushed into action before you are ready.

 

[wmix]

The_Doc_Man, thank you for your reply.

I actually started this project about nine months ago. It's a new industry for me and I realized very quickly I was going to have to work slowly on this project. I started with one table to solve one issue; how the company identifies the machines they own. It started as a basic table with two fields, machine name and unique identifier (primary key). Then I moved on to the next step and created a table that identifies every single location where a machine is located (name, address, city, state, phone, etc.). Within this table each property has it's own unique property ID. I then added the property ID into the Machine table, building a relationship that has been quite useful. Since that time my Machines table has grown, I can now identify machine type (pop, candy, changer, coffee, etc.) and which truck is assigned to servicing that machine.

Because I needed results quickly for information the owner was asking for, I found that adding the A1, A2, A3, etc. to the Machines table worked just fine for what he was asking me for. However, now that I need the information to fulfill more complex tasks, like starting to track the money the machines are bringing in, I need to look at the correct way to track this information.

I had thought about using different tables to track each route/week/truck. However, this would create 22 different tables based on the current route. All the research and reading I have been doing online for the past nine months seems to discourage creating multiple tables and instead using queries to pull the information from one table. Maybe I am thinking about it incorrectly and I should use 22 different tables?

So far I have not been able to find any online articles, forums or videos that have helped me solve this problem. This forum has a lot of great information, so hopefully I’ve come to the right place for assistance. I will look up the “normalization” as you have suggested and see if that helps too. Thank you.

 

[The_Doc_Man]

If you need 22 tables based on 22 routes, again you are doing something in a flat-file manner. I don't know what goes into a route. But say for the sake of discussion that a route names locations rather than the detailed street navigation. Then you would the potential to do something like this:

Location table - has LocID (PK), street address, info
Machine table - has MachID (PK), make, model, type, LocID(foreign key or FK), other data

Then a query can be made to join these tables to make a list of each machine and where it is located. Queries can drive reports as easily as tables could, and if you establish the relationship between Machines and Locations on the LocID field using the Relationships panel, your queries will automatically use that declared relationship as needed.

Route table - has RouteID (PK), general information about the route, perhaps the Week and Day numbers as to when this route is run.

RouteStep table - has RouteID (FK), LocID, MachID. You have one entry for each machine and each location on the route. If the order of visiting locations is important, you can include a number that says for route #1, step #1 is Location X, step #2 is Location Y, etc. In other words, a per-route order number that is not unique across the table - but IS unique for the same route ID. If a machine needs to be serviced more often than once per two-week cycle, it just appears in this list twice, once under each RouteID. Again, you can build queries to join the tables for reports.

What's in your machines? You probably have a products table with a ProdID (PK). You can make lists based on MachID and ProdID. Here, it is going to depend on your precise problem as to whether you want each rack listed along with the number of items held in each rack for the nominal "full load."

As to who runs the route? That depends on whether routes are assigned on a fixed (or at least rarely changed basis) or fluid (assigned on some rotation of duties scheme). But there, you would have a ServiceTech table with a TechID (PK) that you could assign via some form. If you are keeping histories, using a RouteAssign table with a date, RouteID, and TechID. Then when each tech reports money and inventory data, you keep that in a RouteResults table where you have the date, route, tech, location, machine, money extracted, and a child table listing how many of each product had to be replaced by your service tech.

If you try to draw this out on paper, you might see all sorts of other ways to exploit these relationships. Just remember to maintain an awareness of the difference between an entity and its attributes (which go in the entity's table) vs. its enumerable contents (which go in a child table for that entity).

If this confuses you, I'm serious about trying to draw this out and see if it makes sense to you. Queries can be written to help you see anything you need for a report (as long as you capture what is needed to drive the report.)

 

[wmix]

The_Doc_Man, I was able to get this to work. Thank you for your suggestions. Now on to more tasks. Baby Steps!