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

Flattening relationship tables

P: n/a
Consider first, table "A" :

Plate Make Model
=====================
ABC123 Ford F150
XYZ789 Dodge 1500
IJK444 Chev Silverado

Then consider, table "B";

Plate City Start End Person
================================================== =======
ABC123 New York 5-May-99 12-Dec-00 DOE, John
ABC123 Seattle 17-Dec-00 31-Mar-01 SMITH, Frank
ABC123 Pittsburgh 4-Apr-01 5-May-03 JONES, Hank
XYZ789 Los Angeles 3-Mar-03 17-Apr-04 SIMPSON, Bart
XYZ789 Detroit 21-Apr-04 31-Dec-05 RODMAN, Dennis
IJK444 Miami 14-Jul-06 17-Jul-06 SIMPSON, OJ
IJK444 Chigaco 29-Aug-06 2-Oct-06 GROUCH, Oscar
IJK444 Cincinatti 4-Oct-06 12-Dec-06 DAHMER, Jeffrey
IJK444 Wichita 25-Dec-06 1-May-07 DOBBS, Bob (Jr)

Tables A and B are relationship tables, joined-up by the plate
column.

Now assume that there are only nine different places that the vehicles
in our database could possibly be. That is, New York, Seattle,
Pittsburgh, Los Angeles, Detroit, Miami, Chicago, Cincinatti, and
Wichita.

Question:

How does one 'flatten' these two relationship tables to obtain the
following query output?:

Plate Make Model a b c d e f g h i j k l m n o p q r s t u v w x
y z z'
================================================== ==========================
ABC123 Ford F150
XYZ789 Dodge 1500
IJK444 Chev Silverado

ie) Only one record in the new table for each plate. (I didn't fill-
in the 'guts' content of the table obviously).

For the sake of fitting this on the screen, here are the new fields:

NewYorkStart a
NewYorkEnd b
NewYorkPerson c
SeattleStart d
SeattleEnd e
SeattlePerson f
PittsburghStart g
PittsburghEnd h
PittsburghPerson i
LAStart j
LAEnd k
LAPerson l
DetroitStart m
DetroitEnd n
DetroietPerson o
MiamiStart p
MiamiEnd q
MiamiPerson r
ChicagoStart s
ChicagoEnd t
ChicagoPerson u
CincinattiStart v
CincinattiEnd w
CincinattiPerson x
WichitaStart y
WichitaEnd z
WichitaPerson z'

I know that it's crazy to flatten a perfectly-good relationship table,
but here's my rationale. I am using a front-end for the interface
that can only handle a single table at a time. I need to get a few
thousand records of legacy data loaded into the new database. I don't
need realtime/dynamic feed-over. Just a one-time feedover method.

I am open to any solutions using SQL, MS Access, or MS Excel.

Thanks.

May 8 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On May 8, 1:14 am, cspow...@shaw.ca wrote:
Consider first, table "A" :

Plate Make Model
=====================
ABC123 Ford F150
XYZ789 Dodge 1500
IJK444 Chev Silverado

Then consider, table "B";

Plate City Start End Person
================================================== =======
ABC123 New York 5-May-99 12-Dec-00 DOE, John
ABC123 Seattle 17-Dec-00 31-Mar-01 SMITH, Frank
ABC123 Pittsburgh 4-Apr-01 5-May-03 JONES, Hank
XYZ789 Los Angeles 3-Mar-03 17-Apr-04 SIMPSON, Bart
XYZ789 Detroit 21-Apr-04 31-Dec-05 RODMAN, Dennis
IJK444 Miami 14-Jul-06 17-Jul-06 SIMPSON, OJ
IJK444 Chigaco 29-Aug-06 2-Oct-06 GROUCH, Oscar
IJK444 Cincinatti 4-Oct-06 12-Dec-06 DAHMER, Jeffrey
IJK444 Wichita 25-Dec-06 1-May-07 DOBBS, Bob (Jr)

Tables A and B are relationship tables, joined-up by the plate
column.

Now assume that there are only nine different places that the vehicles
in our database could possibly be. That is, New York, Seattle,
Pittsburgh, Los Angeles, Detroit, Miami, Chicago, Cincinatti, and
Wichita.

Question:

How does one 'flatten' these two relationship tables to obtain the
following query output?:

Plate Make Model a b c d e f g h i j k l m n o p q r s t u v w x
y z z'
================================================== =========================*=
ABC123 Ford F150
XYZ789 Dodge 1500
IJK444 Chev Silverado

ie) Only one record in the new table for each plate. (I didn't fill-
in the 'guts' content of the table obviously).

For the sake of fitting this on the screen, here are the new fields:

NewYorkStart a
NewYorkEnd b
NewYorkPerson c
SeattleStart d
SeattleEnd e
SeattlePerson f
PittsburghStart g
PittsburghEnd h
PittsburghPerson i
LAStart j
LAEnd k
LAPerson l
DetroitStart m
DetroitEnd n
DetroietPerson o
MiamiStart p
MiamiEnd q
MiamiPerson r
ChicagoStart s
ChicagoEnd t
ChicagoPerson u
CincinattiStart v
CincinattiEnd w
CincinattiPerson x
WichitaStart y
WichitaEnd z
WichitaPerson z'

I know that it's crazy to flatten a perfectly-good relationship table,
but here's my rationale. I am using a front-end for the interface
that can only handle a single table at a time. I need to get a few
thousand records of legacy data loaded into the new database. I don't
need realtime/dynamic feed-over. Just a one-time feedover method.

I am open to any solutions using SQL, MS Access, or MS Excel.

Thanks.
You need to redesign your front end to properly use a relational
database. Hint, subforms are your friend!

May 9 '07 #2

P: n/a

<cs******@shaw.cawrote in message
news:11**********************@y5g2000hsa.googlegro ups.com...
Consider first, table "A" :

Plate Make Model
=====================
ABC123 Ford F150
XYZ789 Dodge 1500
IJK444 Chev Silverado

Then consider, table "B";

Plate City Start End Person
================================================== =======
ABC123 New York 5-May-99 12-Dec-00 DOE, John
ABC123 Seattle 17-Dec-00 31-Mar-01 SMITH, Frank
ABC123 Pittsburgh 4-Apr-01 5-May-03 JONES, Hank
XYZ789 Los Angeles 3-Mar-03 17-Apr-04 SIMPSON, Bart
XYZ789 Detroit 21-Apr-04 31-Dec-05 RODMAN, Dennis
IJK444 Miami 14-Jul-06 17-Jul-06 SIMPSON, OJ
IJK444 Chigaco 29-Aug-06 2-Oct-06 GROUCH, Oscar
IJK444 Cincinatti 4-Oct-06 12-Dec-06 DAHMER, Jeffrey
IJK444 Wichita 25-Dec-06 1-May-07 DOBBS, Bob (Jr)

Tables A and B are relationship tables, joined-up by the plate
column.

Now assume that there are only nine different places that the vehicles
in our database could possibly be. That is, New York, Seattle,
Pittsburgh, Los Angeles, Detroit, Miami, Chicago, Cincinatti, and
Wichita.

Question:

How does one 'flatten' these two relationship tables to obtain the
following query output?:

Plate Make Model a b c d e f g h i j k l m n o p q r s t u v w x
y z z'
================================================== ==========================
ABC123 Ford F150
XYZ789 Dodge 1500
IJK444 Chev Silverado

ie) Only one record in the new table for each plate. (I didn't fill-
in the 'guts' content of the table obviously).

For the sake of fitting this on the screen, here are the new fields:

NewYorkStart a
NewYorkEnd b
NewYorkPerson c
SeattleStart d
SeattleEnd e
SeattlePerson f
PittsburghStart g
PittsburghEnd h
PittsburghPerson i
LAStart j
LAEnd k
LAPerson l
DetroitStart m
DetroitEnd n
DetroietPerson o
MiamiStart p
MiamiEnd q
MiamiPerson r
ChicagoStart s
ChicagoEnd t
ChicagoPerson u
CincinattiStart v
CincinattiEnd w
CincinattiPerson x
WichitaStart y
WichitaEnd z
WichitaPerson z'

I know that it's crazy to flatten a perfectly-good relationship table,
but here's my rationale. I am using a front-end for the interface
that can only handle a single table at a time. I need to get a few
thousand records of legacy data loaded into the new database. I don't
need realtime/dynamic feed-over. Just a one-time feedover method.

I am open to any solutions using SQL, MS Access, or MS Excel.

Thanks.
Your should be able to build a query to join the 2 tables and format the
data for your other program. Then use that query to export the data.
May 9 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.