8 Replies Latest reply on May 5, 2017 2:58 PM by philmodjunk

    How can I create a report for export of data from multiple tables to a flat file?

    pruppert

      I am wondering if someone can guide me on how to export data from related tables to a single flat file. Here is a simplified version of the database and my preferred export table.

       

      Basic Database Info

      I have 4 tables: Students, Tests, Items, and Tests-Items. This is analogous to the traditional Invoices database.

       

      Here is how the tables are related.

      Students >-- Tests >-- Tests-Items --< Items

       

      Each student can have multiple tests.

      Each test is made up of multiple items that aren't always the same from one test to another.

      The same item can appear on multiple tests.

       

      Table Info

      Students table contains student data.

       

      | Student_ID_pk | FirstName | LastName |

      | :-            | :-        | :-       |

      | 1             | John      | Doe      |

      | 2             | Jane      | Doe      |

      | 3             | Betty     | Smith    |

      | 4             | James     | Jones    |

       

       

      Tests table contains data pertaining to a specific test

       

      | Test_ID_pk | Student_ID_fk | TestDate   |

      | :-         | :-            | :-         |

      | 1          | 2             | 05/05/2017 |

      | 2          | 2             | 03/21/2016 |

      | 3          | 1             | 02/08/2015 |

      | 4          | 4             | 12/15/2017 |

       

       

      Items table contains data for various items/questions that may appear on a test

       

      | Item_ID_pk | ItemName  |

      | :-         | :-        |

      | 1          | Geography |

      | 2          | History   |

      | 3          | Math      |

      | 4          | Art       |

       

       

      Tests_Items table contains data for the specific items on a specific test. This resolves the many-to-many relationship that exists between Tests and Items since a single test has multiple items and a specific item can exist on multiple tests.

       

      | Test_ID_pk | Item_ID_pk | Score |

      | :-         | :-         | :-    |

      | 3          | 1          | 20    |

      | 3          | 2          | 13    |

      | 3          | 3          | 7     |

      | 3          | 4          | 48    |

       

       

      Desired Report

      How would I create a report like the following? The part that stumps me is how to get the test item names to be a column name and have the scores populate the rows. There obviously may not be a score for a given item on any test, and the cell would preferably be blank in this case.

       

      | FirstName | LastName | TestDate   | Geography | History | Math | Art  |

      | :-        | :-       | :-         | :-        | :-      | :-   | :-   |

      | John      | Doe      | 02/08/2015 | 20        | 13      | 7    | 48   |

      | etc.      | etc.     | etc.       | etc.      | etc.    | etc. | etc. |

      | etc.      | etc.     | etc.       | etc.      | etc.    | etc. | etc. |

      | etc.      | etc.     | etc.       | etc.      | etc.    | etc. | etc. |