pruppert

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

Discussion created by pruppert on May 5, 2017
Latest reply on May 5, 2017 by philmodjunk

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. |

Outcomes