432,441 Members | 996 Online
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