"denormalizing" data for easy analysis in excel

[Kozbot]

Hello

I have a quality control database that has a QCEntry table that contains information about each sample the QC technician takes from production. This table has a one to many relationship with the TestResults table, where the tests performed on the sample and their results are stored.
QCEntry table is structured like

EntryID Product  Lot Number  Day    Time
            1       AB-500   121323    12/23    5:00

TestResults table is like

ResultID Entry ID  TestName    TestResult
   1          1         Carbonblack      50  
   2          1          MFI                10

My question is: Is there a way modify large amounts of data like this using a query or some other method to look like this? Kind of denormalizing the tables?

Product  Lot Number    Day    Time   Carbonblack  MFI 
AB-500    12323         12/23  5:00      50             10

Thank you!

 

[way2bord]

Make your query so you have your standard normalized query.

Convert Query to a pivot-view. Move your [TestName] field to the Columns section of the pivot-view.

 

[Kozbot]

Make your query so you have your standard normalized query.

Convert Query to a pivot-view. Move your [TestName] field to the Columns section of the pivot-view.

oh this is great thanks. I had no idea you could do this.

 

[Pat Hartman]

Queries and Tables are interchangeable for most things in Access. That's why you can't ever have a query and a table of the same name.