02-19-13, 14:50 #1Registered User
- 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:
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)
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
02-20-13, 05:15 #2Grumpy old man (training)
Provided Answers: 11
- Join Date
- Sep 2006
- Surrey, UK
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:
SELECT [Key] , GuestLastName , 'Employee' AS AttendeeType , RTRIM(EmpFirstName) & " " & RTRIM(EmpLastName) AS FullName FROM AwardsBanquet WHERE EmpAttending = True UNION SELECT [Key] , GuestLastName , 'Guest' , RTRIM(GuestFirstName) & " " & RTRIM(GuestLastName) AS FullName FROM AwardsBanquet WHERE GuestAttending = True ORDER BY [Key] , GuestLastName , AttendeeType
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