Results 1 to 2 of 2
  1. #1
    Join Date
    May 2011
    Omaha, NE

    Unanswered: Can I make columns on same table rec return as separate recs on rs?

    I've been lurking for a while just reading things when I had time but have finally got a real question so this is my first post.

    Have a very simple 97 DB with a table used to track attendees & their guests for a banquet. This table is linked to a production SQL Server DB to “preload” employee/spouse info. As RSVP’s are returned, the EmpAttending and GuestAttending flags are set accordingly. When Name Badges are printed, I want to sort the results by Employee but if they have a guest, the guest Name Badge should print next so they stay paired for the check-in table as some guests could have a different last name. Name Badges are a single Report that prints 2 across X 4 down the page.

    AwardsBanquet table layout is roughly this:
    EmpLastName text
    EmpFirstName text
    EmpAttending yes/no
    GuestLastName text
    GuestFirstName text
    GuestAttending yes/no

    Used this SQL for a report that only prints the spouses name next to Employee if they are also attending:
    SELECT emplastname, empfirstname, RTRIM(empfirstname) & “ “ & RTRIM(emplastname) AS empfullname, SWITCH(guestattending=-1, RTRIM(guestfirstname) & “ “ & RTRIM(guestlastname)
    FROM awardsbanquet
    WHERE empattending=-1
    ORDER BY emplastname, empfirstname

    But I don’t have any idea how to write a select to deliver the data like I need for printing these labels sorted by Employee but keeping the guest paired IF they are taking one. Any suggestions would be greatly appreciated.

    Thanks guys/gals, Bryan

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 17
    Hello and welcome!

    Your main problem is the data structure. The table as you describe it will have records of variable length, according to whether an employee is bringing a guest or not. As well as not conforming to one of the normal forms (see here for more information), it causes problems such as the one that you describe.

    Assuming that you have a primary key defined for this table, the following could work:
    , GuestLastName
    , 'Employee' AS AttendeeType
    , RTRIM(EmpFirstName) & " " & RTRIM(EmpLastName) AS FullName
      EmpAttending = True
    , GuestLastName
    , 'Guest'
    , RTRIM(GuestFirstName) & " " & RTRIM(GuestLastName) AS FullName
      GuestAttending = True
    , GuestLastName
    , AttendeeType
    You will need to replace "[Key]" with the name of your key field. This will give you a list of all attendees, sorted by key, employee's surname and whether the attendee is an employee or a guest.

    To avoid this, you might want to revist the table design and arrange it so that there is one person to a record, which states whether they are an employee or a guest, with each record holding the relevant employee's key value.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

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