Attached is an Access 2007 database file which illustrates a puzzle: there is a solution included, but it seems very ugly and I can't help feeling there should be a much better way, if anyone can help I'd be very grateful.
This DB is about people and events: I want to record invitations and attendances. There are tables for people, event locations, event types and event dates.
I did consider having separate tables for the "invited" and "attended" relationships, but this seems to cause more trouble than it is worth. It is inherent in the business process that one person can only attend one meeting of each type, so I've put the "invited" and "attended" data in the "Person" table.
There is a simple Form for entering data, which is all fine.
PROBLEM: I need to export the data to Excel: to get this the way I want it, I need a query which flattens out the data: one row per person, with all of the attributes of the events (such as Location). I can't just pick the "Location" item from the Events query as a column, because there are several events being referred to in one Person row, and I don't know how to disambiguate.
NASTY SOLUTION: In the query that I'm going to use for Exporting, I can have a column where the source is:
I'm not sure to understand the logic behing the conditional expressions yielding [I1Location] and [I2Location], however using DLookUp in a query is far from being an ideal solution. You can replace it with a subquery, like this:
IIf(IsNull(InfoInvite1Date),"", (SELECT LocationShort FROM tblLocations WHERE LocationID = InfoInvite1Date)) AS I1Location,
IIf(IsNull(InfoInvite2Date),"", (SELECT LocationShort FROM tblLocations WHERE LocationID = InfoInvite2Date)) AS I2Location,
FROM (tblPerson LEFT JOIN qryEventsInfo ON (tblPerson.InfoAttendDate = qryEventsInfo.EventID) AND
(tblPerson.InfoInvite2Date = qryEventsInfo.EventID) AND
(tblPerson.InfoInvite1Date = qryEventsInfo.EventID))
LEFT JOIN qryEventsSelection ON (tblPerson.SelEventAttendDate = qryEventsSelection.EventID) AND
(tblPerson.SelEventInviteDate = qryEventsSelection.EventID);
Thanks, Sinndho, that's exactly the kind of suggestion I was looking for. Much cleaner and hopefully more efficient. Easy when you know how! But it hadn't occurred to me to put a SELECT in there.
The logic behind the iif test, BTW, was that the DLookup will fail (and give an error) if the match criteria are null (in my case, if no date has been entered) - so the iif (isnull...) was to avoid that case.
I note that your SELECT solution is better behaved and doesn't need that construct.