[mso] Re: Access ??? :VSMail MX2

Glenda,

Its been a while since I have used one, but I think you may want to look
into a crosstab query.  It should let you group by room number and pull in
the patient information based on the room number, whether there are 1, 2 or
3 people in the room.  I think this may be what you are looking for.

James La Borde
South Western Federal Credit Union

-----Original Message-----
From: Glenda Wells [mailto:gwells@xxxxxxxxxxx]
Sent: Tuesday, June 25, 2002 10:07 AM
To: 'mso@xxxxxxxxxxxxx'
Subject: [mso] Re: Access ??? :VSMail MX2



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).
http://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).
http://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
*************************************************************

Other related posts: