Method for Transferring Data

[burgermeister01]

I should preface this message with the following:
1) I'm good with SQL in general, but terrible with Access.
2) I'm not sure this is the proper group to post to.

Assuming you made it past that, here is my question. I have a table
that contains an ID column, a description column, and then a long
series of location columns with counts under them for each of the
descriptions. Sonething like...

ID | description | location1 | location2 | location3| ... locationX
-------------------------------------------------------------------------
1 | blah blah | 3 | 0 | 2 | ...
....

Something like that.

I have a table set up called Descriptions with all the descriptions,
and IDs for each one. I also have a table set up with IDs for each of
the names of the location columns in the table described above.
Finally, I have a table called Count, which has the following columns:
an ID column,
a location id column,
a description id column,
and a count column

I want to link each location and description field to the count cross
referenced in the original table. Is there any easy way to manipuate
this data into populating the Count table?

Thank you!

 

[John W. Vinson]

I should preface this message with the following:
1) I'm good with SQL in general, but terrible with Access.
2) I'm not sure this is the proper group to post to.

Assuming you made it past that, here is my question. I have a table
that contains an ID column, a description column, and then a long
series of location columns with counts under them for each of the
descriptions. Sonething like...

ID | description | location1 | location2 | location3| ... locationX
-------------------------------------------------------------------------
1 | blah blah | 3 | 0 | 2 | ...
...

Something like that.
I have a table set up called Descriptions with all the descriptions,
and IDs for each one. I also have a table set up with IDs for each of
the names of the location columns in the table described above.
Finally, I have a table called Count, which has the following columns:
an ID column,
a location id column,
a description id column,
and a count column

THAT is the correct table structure. Your current table is a "spreadsheet",
and should be removed from the database as soon as you have the data migrated
into your new, correctly normalized three tables.

I want to link each location and description field to the count cross
referenced in the original table. Is there any easy way to manipuate
this data into populating the Count table?

Rename the Count table first - Count is a reserved word (it's an operation in
SQL). Call it ItemCount perhaps.

Then run a series of Append queries to migrate the data from your wide-flat
table into the new tables. First you would populate the Locations table
manually (since the translation from Location1 to "Charing Cross Station"
isn't available in your current table). Then create an Append query selectng
just the ID and Description, and append these fields into the "description"
table.

Finally, create a UNION query to migrate all the counts. Create a new Query,
but don't include any tables. Select View... SQL; you'll get a big textbox
with just SELECT; in the upper left corner. Edit this to

SELECT ID, (1) AS LocationID, Location1 AS ItemCount FROM yourtable
WHERE Location1 IS NOT NULL
UNION ALL
SELECT ID, (2) AS LocationID, Location2 AS ItemCount FROM yourtable
WHERE Location2 IS NOT NULL
UNION ALL
SELECT ID, (3) AS LocationID, Location3 AS ItemCount FROM yourtable
WHERE Location3 IS NOT NULL
UNION ALL
SELECT ID, (4) AS LocationID, Location4 AS ItemCount FROM yourtable
WHERE Location4 IS NOT NULL
UNION ALL
<etc through all the LocationX fields>

Save this query and then base an Append query upon it, putting the data into
your new ItemCount table.

 

[burgermeister01]

Just one additional detail. In the query, I had to amend it to the
following:

SELECT (1) AS location_id,Descriptions.id AS description_id, location1
AS loc_count FROM myTable LEFT JOIN Descriptions ON Descriptions.id =
temp.id UNION ALL....

The original query did not have a column to associate the others with
the descriptions. Thank you though, this information was exactly what
I needed to get started!