crosstab? whazzat? -----Original Message----- From: Colli, Anthony G [mailto:Anthony.Colli@xxxxxxx] Sent: Tuesday, June 25, 2002 1:21 PM To: 'mso@xxxxxxxxxxxxx' Subject: [mso] Re: Access ??? Glenda- Now I understand perfectly. A Crosstab query is what you are looking for. It's like a pivot table in Excel. -Anthony -----Original Message----- From: Glenda Wells [mailto:gwells@xxxxxxxxxxx] Sent: Tuesday, June 25, 2002 1:16 PM To: 'mso@xxxxxxxxxxxxx' Subject: [mso] Re: Access ??? Just in case anyone is interested, here's an example of the result. Assignment is a concatenate of Building and Room. Roomie 1 is FIRST and Roomie2 is LAST in the aggregate query below where I ended up with 625 records with 2 unique ID per Assignment. Assignment Roomie1 Roomie2 BEST 0001 16 207 BEST 0004 448 922 CARSON 3101 617 544 CARSON 3102 2813 5796 SELECT tblRoomAssignmentByPIDM.Assignment, First(tblRoomAssignmentByPIDM_1.SLRRASG_PIDM) AS Roomie1, Last(tblRoomAssignmentByPIDM_1.SLRRASG_PIDM) AS Roomie2 INTO tblRoomie1 FROM tblRoomAssignmentByPIDM INNER JOIN tblRoomAssignmentByPIDM AS tblRoomAssignmentByPIDM_1 ON tblRoomAssignmentByPIDM.SLRRASG_PIDM = tblRoomAssignmentByPIDM_1.SLRRASG_PIDM GROUP BY tblRoomAssignmentByPIDM.Assignment HAVING (((Count(tblRoomAssignmentByPIDM.Number))=2)) ORDER BY tblRoomAssignmentByPIDM.Assignment; Hi All. Our email system was out of commission for a while so just getting back and want to catch up on my roommate table problem. I have 1,391 persons assigned to 762 rooms: 135 Singles, 625 Doubles, 2 Triples. The original list consists of the person ID, the building code and the room number. What I did was this for the double rooms, twice. The result is 2 temporary tables with 625 records each. SELECT tblRoomAssignmentByPIDM.Assignment, First(tblRoomAssignmentByPIDM_1.SLRRASG_PIDM) AS Roomie1, Last(tblRoomAssignmentByPIDM_1.SLRRASG_PIDM) AS Roomie2 INTO tblRoomie1 FROM tblRoomAssignmentByPIDM INNER JOIN tblRoomAssignmentByPIDM AS tblRoomAssignmentByPIDM_1 ON tblRoomAssignmentByPIDM.SLRRASG_PIDM = tblRoomAssignmentByPIDM_1.SLRRASG_PIDM GROUP BY tblRoomAssignmentByPIDM.Assignment HAVING (((Count(tblRoomAssignmentByPIDM.Number))=2)) ORDER BY tblRoomAssignmentByPIDM.Assignment; I appended the result of the 2 queries to a table structure that gives Roomie1 and Roomie2 ID's the title of Main which is then linked via quires by the ID's to other tables for name and address information. Kludgy but it works. But there has to be a programmatic was to accomplish the task and include the singles and triples as well. The single rooms are simple, and the 2 triples, I'll do by hand. ************************************************************* You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or MicrosoftOffice@xxxxxxxxxxxxxxxx To send mail to the group, simply address it to mso@xxxxxxxxxxxxx To Unsubscribe from this group, send an email to mso-request@xxxxxxxxxxxxx?Subject=unsubscribe Or, visit the group's homepage and use the dropdown menu. This will also allow you to change your email settings to digest or vacation (no mail). //www.freelists.org/webpage/mso To be able to use the files section for sharing files with the group, send a request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with instructions. Once you are a member of the files group, you can go here to upload/download files: http://www.smartgroups.com/vault/msofiles ************************************************************* ************************************************************* You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or MicrosoftOffice@xxxxxxxxxxxxxxxx To send mail to the group, simply address it to mso@xxxxxxxxxxxxx To Unsubscribe from this group, send an email to mso-request@xxxxxxxxxxxxx?Subject=unsubscribe Or, visit the group's homepage and use the dropdown menu. This will also allow you to change your email settings to digest or vacation (no mail). //www.freelists.org/webpage/mso To be able to use the files section for sharing files with the group, send a request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with instructions. Once you are a member of the files group, you can go here to upload/download files: http://www.smartgroups.com/vault/msofiles ************************************************************* ************************************************************* You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or MicrosoftOffice@xxxxxxxxxxxxxxxx To send mail to the group, simply address it to mso@xxxxxxxxxxxxx To Unsubscribe from this group, send an email to mso-request@xxxxxxxxxxxxx?Subject=unsubscribe Or, visit the group's homepage and use the dropdown menu. This will also allow you to change your email settings to digest or vacation (no mail). //www.freelists.org/webpage/mso To be able to use the files section for sharing files with the group, send a request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with instructions. Once you are a member of the files group, you can go here to upload/download files: http://www.smartgroups.com/vault/msofiles ************************************************************* ************************************************************* You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or MicrosoftOffice@xxxxxxxxxxxxxxxx To send mail to the group, simply address it to mso@xxxxxxxxxxxxx To Unsubscribe from this group, send an email to mso-request@xxxxxxxxxxxxx?Subject=unsubscribe Or, visit the group's homepage and use the dropdown menu. This will also allow you to change your email settings to digest or vacation (no mail). //www.freelists.org/webpage/mso To be able to use the files section for sharing files with the group, send a request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with instructions. Once you are a member of the files group, you can go here to upload/download files: http://www.smartgroups.com/vault/msofiles *************************************************************