[mso] Re: Access ???
- From: James LaBorde <jlaborde@xxxxxxxxx>
- To: "'mso@xxxxxxxxxxxxx'" <mso@xxxxxxxxxxxxx>
- Date: Wed, 26 Jun 2002 11:11:57 -0700
Glenda,
Either would work. and if you set it up correctly it would ensure that you
don't overbook a room.
James
-----Original Message-----
From: Glenda Wells [mailto:gwells@xxxxxxxxxxx]
Sent: Wednesday, June 26, 2002 10:41 AM
To: 'mso@xxxxxxxxxxxxx'
Subject: [mso] Re: Access ??? :VSMail MX2 :VSMail MX2
You mean like bed 1, bed 2, bed 3? or would an alpha character be better?
We don't use bed numbers so I didn't think of that.
-----Original Message-----
From: James LaBorde [mailto:jlaborde@xxxxxxxxx]
Sent: Wednesday, June 26, 2002 1:39 PM
To: 'mso@xxxxxxxxxxxxx'
Subject: [mso] Re: Access ??? :VSMail MX2
Glenda,
Could you add Bed Number? It would enable you to do your crosstab.
Otherwise you are going to be stuck doing it the long way.
James
-----Original Message-----
From: Glenda Wells [mailto:gwells@xxxxxxxxxxx]
Sent: Wednesday, June 26, 2002 10:20 AM
To: 'mso@xxxxxxxxxxxxx'
Subject: [mso] Re: Access ??? :VSMail MX2
Number is just number:1 in the query so I can get a count later. It's got
nothing to do with the actual data. The original columns are ID, Building
and Room. I concatenated Building and Room to get Assignment so I end up
with 2 columns, ID and Assignment. Makes it tough, don't it? /gl
-----Original Message-----
From: James LaBorde [mailto:jlaborde@xxxxxxxxx]
Sent: Wednesday, June 26, 2002 12:10 PM
To: 'mso@xxxxxxxxxxxxx'
Subject: [mso] Re: Access ???
Glenda,
What is the number you have listed? And do you have bed numbers assigned in
each room? That may be the answer to your crosstab query problem. It is
currently trying to give you a new column for ever patient Id you are using.
You would then sort with Assignment as your Row headings, BedNumber as your
Column headings and Hopefully you only have one of each assigned (you could
make it a multi-column primary Key) so you would use Last(id) in your data
field. This will return the data the way you are looking for it.
James La Borde
South Western Federal Credit Union
-----Original Message-----
From: Glenda Wells [mailto:gwells@xxxxxxxxxxx]
Sent: Tuesday, June 25, 2002 10:40 AM
To: 'mso@xxxxxxxxxxxxx'
Subject: [mso] Re: Access ??? :VSMail MX2
I know I'm missing a step somewhere but...
I tried doing a Pivot Table in Excel and that didn't work.
Here's a sample of what I started with. 3 ID for BEST OOO3, 2 ID for BEST
0004 and 1 ID for CARS 2000.
ID Bldg Room Assignment Number
757 BEST 0003 BEST 0003 1
6760 BEST 0003 BEST 0003 1
2356 BEST 0003 BEST 0003 1
4448 BEST 0004 BEST 0004 1
9922 BEST 0004 BEST 0004 1
7017 CARS 2000 CARS 2000 1
This is the result I need
Assignment Roomie1 Roomie2 Roomie3
BEST 0003 757 6760 2356
BEST 0004 448 9922
CARS 2000 7017
When I tried an Access Crosstab query just now, with Assignment as Row
Heading and ID as Column, got error too many crosstab column headers.
There's 762 assignments and 1,391 ID
-----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).
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
*************************************************************
*************************************************************
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
*************************************************************
*************************************************************
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
*************************************************************
*************************************************************
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
*************************************************************
*************************************************************
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: