Correct poor table design

[Michael Walsh]

I want to correct what I'm pretty sure is the wrong design
in a table. While the design is perfect for the
spreadsheet, it's poor for a database. Currently the
table has a column for ID, date, value and a separate
column for each of 15 locations. 18 Columns in all.

The data goes across the table rather than down.

What I want to do is have it so it ends up with 4 columns
in all by eliminating the 15 separate location columns and
replacing them with 1 location column that would contain
the column heading as the stored data.

The table contains data, so I'd like to do this in way of
make table query that I could use to create the new table
to replace the old one later manually.

 

[Armen Stein]

I want to correct what I'm pretty sure is the wrong design
in a table. While the design is perfect for the
spreadsheet, it's poor for a database. Currently the
table has a column for ID, date, value and a separate
column for each of 15 locations. 18 Columns in all.

The data goes across the table rather than down.

What I want to do is have it so it ends up with 4 columns
in all by eliminating the 15 separate location columns and
replacing them with 1 location column that would contain
the column heading as the stored data.

The table contains data, so I'd like to do this in way of
make table query that I could use to create the new table
to replace the old one later manually.

Hi Michael,

You're absolutely correct that this is a poor table design. And your
new table design is also correct.

However, I don't recommend maketable queries. They'll set the
properties of the fields for you, whereas you should do that yourself.
Instead of a maketable, create the new table exactly the way you want
it. Then, when you are ready to convert the data, create 15 Append
queries (one for each location) to load the locations from each column
into the new table.

Hope this helps,

 

[Tim Ferguson]

The table contains data, so I'd like to do this in way of
make table query that I could use to create the new table
to replace the old one later manually.

You can make either a series of _append_ queries, which is probably simpler
in the one-off situation, or you can create a single query with a bunch of
UNIONs, which would be awful to time, ponderous to run, but suitable if you
had to repeat the process often.

The first one looks like this:

INSERT INTO NewTable(IDNum, StartDate, Location, StoreValue)
SELECT IDNum,
StartDate,
"SanFrancisco",
[SanFrancisco]
FROM OldTable


and then keep substituting the location names into the string constant and
the field name to be used. I don't _think_ you can use a PARAMETER to pick
the field name, but you can always try. You could always use a couple of
lines of VBA to string-slice the names in instead.

The union query is similar

SELECT IDNum, StartDate, "SanFrancisco" AS Location, SanFrancisco
FROM OldTable

UNION

SELECT IdNum, StartDate, "NewYork", NewYork
FROM OldTable

UNION

SELECT IDNum, StartDate, "Idaho", Idaho
FROM OldTable

UNION etc. etc.


Hope that helps


Tim F

 

[John Vinson]

I want to correct what I'm pretty sure is the wrong design
in a table. While the design is perfect for the
spreadsheet, it's poor for a database. Currently the
table has a column for ID, date, value and a separate
column for each of 15 locations. 18 Columns in all.

The data goes across the table rather than down.

What I want to do is have it so it ends up with 4 columns
in all by eliminating the 15 separate location columns and
replacing them with 1 location column that would contain
the column heading as the stored data.

The table contains data, so I'd like to do this in way of
make table query that I could use to create the new table
to replace the old one later manually.

A Normalizing Union Query is the trick here: you'll need to go into
the SQL window to create it. It will be something like

SELECT [ID], [Date], [Value], [Location1] AS Location
FROM yourtable
WHERE [Location1] IS NOT NULL
UNION ALL
SELECT [ID], [Date], [Value], [Location2] AS Location
FROM yourtable
WHERE [Location2] IS NOT NULL
UNION ALL
SELECT [ID], [Date], [Value], [Location3] AS Location
FROM yourtable
WHERE [Location3] IS NOT NULL
UNION ALL
<etc, for all 15 fields>

Save this query; check its output to be sure it's got the right
information, and then base an Append query upon this stored query to
populate your normalized table.