How do I create a normalized database from this Excel file? I have an Excel table with unit numbers across the top and the steps of the units listed down the left side. Right now people put an "x" in the cell when they are done with a step for a certain unit. Right now when we decide to do another unit we add one more number on the top.
How do I make this into a normalized Access database? I assume it needs at least two tables: "Steps" and "Units."
The bigger question, how do I present the form for data entry? A cross tab query cannot be used to enter data in Access. People are so used to entering their "x" in this Excel sheet with unit number at the top and pieces down the left side. How do I make something similar or just as easy to use in Access?
Units 1 2 3 4 5
Molded x x x x x
Bolted x x x
Painted x x x
Packed x
tblUnit
UnitID, UnitName
1, Unit1
2, Unit2
3, Unit3
4, Unit4
5, Unit5
tblPiece
PieceID,PieceName
1, Molded
2, Bolted
3, Painted
4, Packed
tblUnitPiece
UnitPieceID, UnitID, PieceID
1, 1, 1
2, 1, 2
3, 1, 3
4, 1, 4
5, 2, 1
6, 2, 2
7, 2, 3
8, 3, 1
9, 3, 3
10, 4, 1
11, 4, 2
12, 5, 1