Combining and Organizing separate fields into one report

[Guest]

Okay...I have a database to keep track of the attendees registered for our
seminars. They are entered and listed by Company Name. Each Company has
space for up to nine attendees. Therefore, my form, table, and query have
Attendee1, Attendee2....Attendee9 as the field names. However, I need to be
able to list all the attendees not organized by Company, but attendee name
alphabetically. I would like to keep the company to the side as a reference.
Since some companies send one attendee and others send three, it is
difficult to list the attendees without listing all the 1's, all the 2's,
etc. Can anyone help?

 

[kingston via AccessMonster.com]

The data should be better normalized...
Given what you've got, a union query will do the trick:

SELECT Attendee1 AS Name, Company FROM SeminarTable
UNION SELECT Attendee2 AS Name, Company FROM SeminarTable
UNION SELECT Attendee3 AS Name, Company FROM SeminarTable
UNION SELECT Attendee4 AS Name, Company FROM SeminarTable
UNION SELECT Attendee5 AS Name, Company FROM SeminarTable
UNION SELECT Attendee6 AS Name, Company FROM SeminarTable
UNION SELECT Attendee7 AS Name, Company FROM SeminarTable
UNION SELECT Attendee8 AS Name, Company FROM SeminarTable
UNION SELECT Attendee9 AS Name, Company FROM SeminarTable;

 

[tina]

what happens when your seminars are expanded to allow each company to send
TEN attendees? you'll have to change the table, and all queries, forms,
reports, and code that work with that table. better to nip the problem in
the bud by restructuring your table *now* to follow normalization rules. not
only will it save you time and massive headaches over the life of the
database, but it will solve your immediate problem...immediately. suggest
the following table structure, as

tblCompanies
CompanyID (primary key)
CompanyName
(other fields that describe a specific *company*)

tblSeminars
SeminarID (primary key)
SeminarName
(other fields that describe a specific seminar)

tblSeminarAttendees
AttendeesID (primary key)
FirstName
LastName
CompanyID (foreign key from tblCompanies)
SeminarID (foreign key from tblSeminars)
(other fields that describe a seminar attendee)

if companies send the same people to many seminars, and if you want to track
the attendees as people, separate from the data gathered in regards to a
specific seminar attendance, then suggest you include a separate table of
CompanyRepresentatives, with fields that describe the person, including a
link to the company they represent. then use the primary key field from that
table as a foreign key in tblSeminarAttendees above, and remove the
CompanyID field from that table.

recommend you read up on normalization principles. once you understand the
topic, you're the best person to determine how your tables should be
structured within those principles, because you know more about your
business process than you can hope to tell anyone in this forum. for more
information on normalization, see
http://home.att.net/~california.db/tips.html#aTip1.

hth

 

[Guest]

Hey thanks! That helped. I did that; however, I must have done something
wrong...For each Company there is one blank cell in the attendee name column
followed by the actual attendees. How do I remove these empty cells?

 

[Guest]

Thanks! I formed the database out of necessity originally and didn't take
too much time to develop my tables fully before I started generating my
reports. Had I thought on it a bit more, I probably could have come up with
a more comprehensive system. Thank you very much for your suggestions!

 

[kingston via AccessMonster.com]

You can add a criteria to each of the select statements to eliminate the
empty entries:

WHERE (IsNull([Attendee1])=False)

Hey thanks! That helped. I did that; however, I must have done something
wrong...For each Company there is one blank cell in the attendee name column
followed by the actual attendees. How do I remove these empty cells?

The data should be better normalized...
Given what you've got, a union query will do the trick:

[quoted text clipped - 18 lines]

 

[tina]

you're welcome

Thanks! I formed the database out of necessity originally and didn't take
too much time to develop my tables fully before I started generating my
reports. Had I thought on it a bit more, I probably could have come up with
a more comprehensive system. Thank you very much for your suggestions!