By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 994 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Database to a table

P: n/a
Hello, I am working with a database that has the columns...

place_id - place_class - place2_class

Some instances of data are:

1 - 1001 - 1002
1 - 1001 - 1003
1 - 1001 - 1004
1 - 1010 - 1011
1 - 1020 - 1021
2 - 1001 - 1002
2 - 1001 - 1003
2 - 1010 - 1011
2 - 1010 - 1012
2 - 1020 - 1022

I need to take this database and create a table that has a listing of
all the place_class numbers, followed by a list of all the matching
place2_Class number for each place_id.

Example, the above would transform into this table:

-----(1)--(2)-
1001|1002|1002
1001|1003|1003
1001|1004|____
1010|1011|1011
1010|____|1012
1020|1021|1022

This table could be up to an undetermined number of columns wide.
Basically I am taking the first table and sliding it from rows into
columns depending on the place_id.

Ideas? You can not depend on the number sequences, those are only for
demonstration purposes. This is all in Access 2003.
Thank you very much.

Mike

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Sounds like another normalization problem. What's a place? What's a class?

"mike" <ru****@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
Hello, I am working with a database that has the columns...

place_id - place_class - place2_class

Some instances of data are:

1 - 1001 - 1002
1 - 1001 - 1003
1 - 1001 - 1004
1 - 1010 - 1011
1 - 1020 - 1021
2 - 1001 - 1002
2 - 1001 - 1003
2 - 1010 - 1011
2 - 1010 - 1012
2 - 1020 - 1022

I need to take this database and create a table that has a listing of
all the place_class numbers, followed by a list of all the matching
place2_Class number for each place_id.

Example, the above would transform into this table:

-----(1)--(2)-
1001|1002|1002
1001|1003|1003
1001|1004|____
1010|1011|1011
1010|____|1012
1020|1021|1022

This table could be up to an undetermined number of columns wide.
Basically I am taking the first table and sliding it from rows into
columns depending on the place_id.

Ideas? You can not depend on the number sequences, those are only for
demonstration purposes. This is all in Access 2003.
Thank you very much.

Mike

Nov 13 '05 #2

P: n/a
What does it represent in the physical world? A place is a school, and
a class is a class offered by a school. Each row represents a mapping
from a school with a class, to a class at another school. The entire
database table holds information for many school and their classes
mapping to classes a single school.

Looking at the instance data again:

1 - 1001 - 1002
1 - 1001 - 1003
1 - 1001 - 1004
1 - 1010 - 1011
1 - 1020 - 1021
2 - 1001 - 1002
2 - 1001 - 1003
2 - 1010 - 1011
2 - 1010 - 1012
2 - 1020 - 1022

School 1 has classes 1002, 1003 and 1004 that map to 1001. The output
table (the thing I am trying to create) will have a row for each thing
that is mapped to. So in this small case with School 1 there would be
three rows:
------------(1)--
1001 - 1002
1001 - 1003
1001 - 1004

The first row of the first column is empty, and the second column of
the first row has the school id (in this case 1). The table will grow
vertically when more classes are mapped to, and will grow horizontally
when there are more schools mapping to classes. So school 2 (in the
instance data) would be represented in a new column next to the second
column (with school 1).

The result table should be rather sparce, with many blanks cells)
because some school have many classes that map to one class (like many
classes all mapping to 1001) and some many have 1 or zero.
Thank you,
Mike

Nov 13 '05 #3

P: n/a
Hmmm...very interesting. Have you considered using a pivot table?

"mike" <ru****@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
What does it represent in the physical world? A place is a school, and
a class is a class offered by a school. Each row represents a mapping
from a school with a class, to a class at another school. The entire
database table holds information for many school and their classes
mapping to classes a single school.

Looking at the instance data again:

1 - 1001 - 1002
1 - 1001 - 1003
1 - 1001 - 1004
1 - 1010 - 1011
1 - 1020 - 1021
2 - 1001 - 1002
2 - 1001 - 1003
2 - 1010 - 1011
2 - 1010 - 1012
2 - 1020 - 1022

School 1 has classes 1002, 1003 and 1004 that map to 1001. The output
table (the thing I am trying to create) will have a row for each thing
that is mapped to. So in this small case with School 1 there would be
three rows:
------------(1)--
1001 - 1002
1001 - 1003
1001 - 1004

The first row of the first column is empty, and the second column of
the first row has the school id (in this case 1). The table will grow
vertically when more classes are mapped to, and will grow horizontally
when there are more schools mapping to classes. So school 2 (in the
instance data) would be represented in a new column next to the second
column (with school 1).

The result table should be rather sparce, with many blanks cells)
because some school have many classes that map to one class (like many
classes all mapping to 1001) and some many have 1 or zero.
Thank you,
Mike

Nov 13 '05 #4

P: n/a
Ahh, pivot tables. I have tried using this "feature" but I have had
very little luck. Do you have advice on how I would layout this in a
pivot table? I have played around, and ended up with a crosstab, which
is not sufficient for this task, as far as I know.

I was able to get this crosstable query to have the correct layout:

TRANSFORM tca_target.COURSE
SELECT [Complete Target Listing].tca_target.COURSE
FROM [Complete Target Listing]
GROUP BY [Complete Target Listing].tca_target.COURSE
PIVOT [Complete Target Listing].SOURCE_ID;

but it does not have some crucial features that I need, like duplicated
rows on the left for each class. The layout, however, is close. I
admittedly created this query with an Access wizard and am not familiar
with what it is actually "doing".
Advice on how Pivot Tables could be used to help here?

Mike

Nov 13 '05 #5

P: n/a
Thank you for all the help, you have helped me very much by acting as a
"wooden indian" :-)

I solved the problem by using the PivotTable Design view. I hadn't
solved it in the past becuase of the huge amount of data I was working
with.

Thanks again!

Mike

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.