Thank you all for your responses and time given - very helpful and much appreciated.
I have abandoned the multi-value field. Your advice echoes what I have read elsewhere on the internet – they are bad!
I do need the Drawing-to-Change relationship to be effectively many-to-many, so I have added a linking table, which seems to achieve what I need. The Crosstab then looks like this:
Code:
TRANSFORM First(DCount("*","tblDrwChg","DrwID=" & [tblDrw].[DrwID] & " AND DrwChgID<" & [DrwChgID])+1) AS GrpSeqSELECT tblDrw.DrwNo, tblDrw.DrwName, tblDrw.DrwScale
FROM tblChg INNER JOIN (tblDrw INNER JOIN tblDrwChg ON tblDrw.DrwID = tblDrwChg.DrwID) ON tblChg.ChgID = tblDrwChg.ChgID
GROUP BY tblDrw.DrwNo, tblDrw.DrwName, tblDrw.DrwScale
PIVOT tblChg.ChgDate;
Regarding updating number of columns on a crosstab. I think I will set up the report with something like 20 columns. If a project goes beyond 20 changes, then perhaps a prompt for user to select a range to display is the way to go?
I will apply what I have learnt to my actual DB and report back with my progress...!