Changing Database Format. Possible SQL Query?

[mswhiteside]

I am trying to change the database I've built a little bit, so it would be easier to add more unit's if we need to. Currently the table design is set up as below

Part Number--|--Part Name--|--Unit1ECD--|--Unit2ECD--|--Unit3ECD--|--etc.
----Part A-------|-----Name A---|----9/9/99-----|----9/9/99-----|----9/9/99------|--etc.
----Part B-------|-----Name B---|----9/9/99-----|----9/9/99-----|----9/9/99------|--etc.
----Part C-------|-----Name C---|----9/9/99-----|----9/9/99-----|----9/9/99------|--etc.

With it set up like this I can set up a form that let's people go in, and status all the unit's at one time, however if i have to add in more unit's, I have to go in, and add 5 to 6 columns for each unit.

I would like to set it up like the example below, but still be able to set up a form that looks like the format above

Part Number--|--Part Name--|--ECD--|--Unit--|--ect.
----Part A-------|-----Name A---| 9/9/99 |----1----|--etc.
----Part A-------|-----Name A---| 9/9/99 |----2----|--etc.
----Part A-------|-----Name A---| 9/9/99 |----3----|--etc.
----Part B-------|-----Name B---| 9/9/99 |----1----|--etc.
----Part B-------|-----Name B---| 9/9/99 |----2----|--etc.
----Part B-------|-----Name B---| 9/9/99 |----3----|--etc.
----Part C-------|-----Name C---| 9/9/99 |----1----|--etc.
----Part C-------|-----Name C---| 9/9/99 |----2----|--etc.
----Part C-------|-----Name C---| 9/9/99 |----3----|--etc.


I've tried to set up some crosstab query's, but you can't update the data in a crosstab query. Is there an SQL query or something of that nature that I would be able to create that would allow updates?

If this doesn't make sense, just let me know :-P

[Stewart Ross]

Hi. It is clear from what you have posted that your database is not relationally designed, as there are two different objects (the parts and the units) and repeating groups of data (the units) which would be separated into their own tables had the database been appropriately designed. The format shown is more like an Excel table.

It is impossible to resolve your 'adding units' issues as it stands; the tables are not yet in what is known as First Normal Form (the first of three database decompositions which separate out different objects into different tables).

The following article from the HowTo section of the forum may help:
Database Normalisation and Table Structures

-Stewart