Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    88

    Unanswered: Flattening data for export to Excel

    Hello

    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:
    Code:
    I1Location: IIf(IsNull([InfoInvite1Date]),"",DLookUp("LocationShort","tblLocations","LocationID=" & [InfoInvite1Date]))
    There are a couple of these included in the sample: it works, but it feels inefficient and might get to be slow if the data volumes grow.

    But surely there is a better way? I feel there ought to be some way of stacking queries, perhaps, but I just can't see it. Or is there some syntax in the query definition that I am missing?

    Can anyone help?
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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:
    Code:
    SELECT tblPerson.PersonID, 
           tblPerson.PersonName, 
           tblPerson.InfoInvite1Date,
           IIf(IsNull(InfoInvite1Date),"", (SELECT LocationShort FROM tblLocations WHERE LocationID = InfoInvite1Date)) AS I1Location,
           tblPerson.InfoInvite2Date, 
           IIf(IsNull(InfoInvite2Date),"", (SELECT LocationShort FROM tblLocations WHERE LocationID = InfoInvite2Date)) AS I2Location,
           tblPerson.InfoAttendDate, 
           tblPerson.SelEventInviteDate, 
           tblPerson.SelEventAttendDate
    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);
    Have a nice day!

  3. #3
    Join Date
    Mar 2010
    Posts
    88
    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.

    Many thanks.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •