Spreadsheet Style Report? Column and Row headers (1 Viewer)

[NBRJ]

Someone has asked me today to manipluate some data on a worksheet (from one of many spreadsheets the database I'm currently trying to create will be replacing) for a report. The data is not normalised in any way (so one massive worksheet). I've quickly imported the data and tried to split it into useful tables to normalise as far as is needed for what they want me to do - which is a report. See attached ERD to show the data.

BUT they want a report as follows:

[B]BUILD[/B]: i.e ANIMATION
    [B]SOFTWARE    [/B]SW1    SW2    SW3    SW4    SW5
[B]ROOM   [/B]
R1               V      L            L       V    
R2               V      L            L
R3               L      V            L       V
R4               V      V     V      L       L

[B]BUILD[/B]: i.e FINE ART
    [B]SOFTWARE    [/B]SW1    SW2    SW3    SW4    SW5
[B]ROOM   [/B]
R5                      L            L       V    
R6                      L      L      
R7               V      L      L             V
R8               L      V      V     L       L

Essentially, they want by Build (Build is a designation of type of room) a grid that lists the assoicated rooms against the software and if there is a relationship (i.e. a entry in the RoomSoftware tbl). However, even though they just want a true indication in "cells" to indicate a presense of a record in the Junction table, I can't see how access can do that in a report.

I then thought because there is a additonal field in the junction table (Delivery - indicated by the Vs and Ls above), that is something to report on?

Is this even possible? I'm actually kind of curious now. It's kind of like a finance style report where you could have:

DEPT
Month ------>
Salesperson (rows)

 

[MarkK]

Check out a crosstab query. The problem with that, however, is that the field names are drawn from your data, so there is no guarantee that a crosstab will contain the same fields as what the controls in the report are bound too.

But check out the crosstab first. One step at a time.

 

[NBRJ]

I've just found that after doing the work in Excel using pivot. I then hit on Cross tab in a search and https://blogs.office.com/2012/05/23/using-crosstab-queries-in-reports/ Hurruh. Why is it I google and google and get nowhere. Post a question and then find the answer!