tblProductions one to many to tblEvents
tblEvents contains StartDate
I want a report where the data are grouped by tblProductions.ProdID, and
sorted by the earliest date in each Production.
So this as a query (I've stripped out surplus fields):
SELECT qryEvent.EventName, qryProduction.ProdName, qryEvent.ProdID,
qryEvent.StartDate, qryEvent.StartTime, (SELECT First(qryEvent.StartDate)
FROM qryEvent WHERE qryEvent.ProdID = qryProduction.ProdID) AS ProdDate
FROM qryEvent INNER JOIN qryProduction ON qryEvent.ProdID =
qryProduction.ProdID
WHERE (((qryEvent.StartDate)>=Date() Or (qryEvent.StartDate) Is Null));
This gives me what I want, nearly.
1. I can't set an Order By on ProdDate, I get Syntax Error if I try.
2. Presumably related, if in a report based on this (WITHOUT an order by in
the query) I get a crash if I try to do anything useful in Sorting and
Grouping with ProdDate. Basically when I move from design to preview I get a
'this will reset break mode...' or whatever that message is. Can't see a VBA
module open or anywhere.
Any ideas why this is happening, or a better approach (not that I can really
see what's wrong with mine!)?
Cheers, Mike MacSween