Ok I am extremely new at making databases. Right now I have an access database that I created to track several things I need to know for my job. The problem I am having is that I am trying to take my design to others to get this to be a universal template for all to use, but when I take it to our "Database people" they say that its a mess and not made correctly.
I am going to attach my main table that I use. Now the issue I am having is that I understand for correct design these all need to be split up, but when i do that I have no way to enter the data that I want to enter correctly.
I am using a query to calculate a goal for each hour based on the time available and the time it takes to build the product. I also use a Data Access Page to input all this information into the database. Now I have had no issues running it this way, but I know in terms of design its garbage.
Ok going to try attaching screen shots of it then. The Logs image is of my table. The query1 image shows the start of my query. Now the Lineload and product repeats for all 8 hours, but i didnt inclue them all. I then have query2 and query3 for each of the 8 hours as well. This is all populated by the data access page. Does that make sense?
The database guys don't like it because the data is not Normalized. In your original post you say your "main table", do you have more then one table? To help normalize your table you need to remove any fields called HourX. You don't want to be limited to 8 hour fileds in case you end up doing 9 hour shifts. Your table should probably look more like:
With this design you will be able to have as many hours as you need. You also might consider using another table to define a shift (or some way of defining the hours in a day)
tblShift or tblHours
8:00 a.m.-------8:59 a.m.
9:00 a.m.-------9:59 a.m.
The tblShift table can be used with the current date to display the page you have. I am not sure if my advice is useful without knowing more about your objectives for this database. What are you trying to track? How will you use it? Daily, at the end of the day? Hourly as you are working?
its used to track production each hour. The basic criteria for each hour is
Lineload = How many people are working on the product
Product = What they are building that hour
Production Amount = How many they built
Goal = How many they could have built
Failures = Number of bad units in that hour
Barriers = Anything that may have stopped them from reaching goal
These have to be tracked each hour, for every team, every day.
I also have to know for each of those days that this information is being recorded who is tracking it and at what station.
I have tried splitting it into several tables.
Since the goal is a calculation I haven't tried to make a table out of it. Its always calculated but not stored as record. When I have tried this though I have no way of entering information correctly because there is no reference point across those tables to link them together. They are also all entered at the relatively same time.
At the start of a day the operator will enter in the number of people working and what product is being built into the data access page. This then populated the goal so they know what they are trying to reach for. This is done at 7:15am and then so at 8:00 is their first cutoff. They then record in how many they produced, how many failures, and any barriers that came up. They then log in the same information for the next hour and continue the process.