Now how did I know you were going to say that?

Right.  I've got most of it sorted out I think.  Not quite polished off but
let's see what you think so far.  Again, this really is quite nasty.

Your problem is, pretty much, that you're attempting to take a normalised
set of data and push it back into a completely non-normalised format.
Nothing wrong with that, but it does make things complicated.

I don't think there's any way of getting at what you're after without some
serious playing around with the data.  It might be possible to do it by
using subreports but I think that looks at least as messy.

So...

First thing that I've done is set up a query that I've called DatesCT.  SQL
is

TRANSFORM Max('x') AS Expr1 SELECT 1 AS Staff FROM Weeks GROUP BY 1
PIVOT Weeks.WeekID;

...basically using a crosstab query to create a structure with weeks as
column headings.

Next...another query that is a make table in order to permanently create a
structure that I'm going to use to store the data...

SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>1));

Note that I want the Staff field (which I really should have called StaffID
again) to be numeric (so that I can put StaffIDs into it) which is where the
"1" in DatesCT comes from - to ensure that it comes out that way.
Similarly, I want the data type of the 1, 2, 3 fields to be text (so that I
can drop the names of jobs into them) - hence the Max('x').  The <>1
criterion is just so that the existing row doesn't get added to the new
table.

OK.  Next bit.  Take the normalised version of your data and push it into
this new table.  To do this I have another query called ReportData...

SELECT JobsInProgressAssigned.JIPAssignment, Weeks.WeekID, Staff.StaffID
FROM Weeks INNER JOIN (Staff INNER JOIN JobsInProgressAssigned ON
Staff.StaffID = JobsInProgressAssigned.JIPStaffID) ON Weeks.WeekID =
JobsInProgressAssigned.JIPWeekID;

To take this data and push it into the ReportBasis table I've needed to
resort to code...

Private Sub SetupData()
Dim db As Database
Dim rs As DAO.Recordset
Dim rsoutput As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("ReportData")
Set rsoutput = db.OpenRecordset("ReportBasis", dbOpenDynaset)
rs.MoveFirst
'Loop through source records
Do While Not rs.EOF
   With rsoutput
       'Check for a row that belongs to our staff member, but where the
WeekID field
       'hasn't been completed yet
       .FindFirst ("[Staff]=" + CStr(rs!staffid) + _
       " and isnull([" + CStr(rs!WeekID) + "])")
       'If there isn't one, then we need a new row
       If .NoMatch Then
           'and complete the information
           rsoutput.AddNew
           rsoutput!Staff = rs!staffid
           rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
           rsoutput.Update
       Else
           'otherwise, just fill in the blank
           rsoutput.Edit
           rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
           rsoutput.Update
       End If
   End With
   'and move to the next record
   rs.MoveNext
Loop
rs.Close
rsoutput.Close
Set db = Nothing
End Sub

You could obviously apply some kind of date criteria to this process as you
require.

You can now create a report based on this new table... but...

The need to keep the flexibility to print out the report for 52 weeks is the
last complication, and the one that I haven't dug into in detail as yet.
Obviously, there's no way in the world you're going to be able to fit 52
weeks across a single piece of paper so...

My idea would be to create the report with the maximum number of fields
across the page... say five.  Given an outside parameter - i.e. the first
week number required in the report - then you can do something like this in
the Open event of the report...

Me.[1_label].Caption = 'look up the outside number in the weeks table to
figure out what the date should say
Me.[1].ControlSource = "2" 'or similar... the control (by default, using my
methodology) is called 1 but you can remap it to something different by
doing this.

I think you should, therefore, be able to loop through values in a form..

For i = 1 To 11 Step 5
me.whatever=i
docmd.openreport "ReportName"
next

...and each time the report opens it references forms!formname!whatever to
see the first week it should be working with.

Simple, isn't it?

We do seem to have got a little way away from where this thread started out,
but I think it's going to be tricky to come up with an automated solution no
matter how you try and do it (if you use the pivottable approach, then
that's going to be equally useless when you have 52 weeks to cope with).
The simple approach would be to just push the Excel data out into a
spreadsheet, or just create a new spreadsheet linked in to the database, but
that's always going to require the user to be able to manipulate the
spreadsheet in order to get the required output.

I'm going for a nice lie down now anyway....