[mso] Re: Access ???
- From: Glenda Wells <gwells@xxxxxxxxxxx>
- To: "'mso@xxxxxxxxxxxxx'" <mso@xxxxxxxxxxxxx>
- Date: Tue, 25 Jun 2002 13:09:07 -0400
Hi Ken.
Thanks. I concatenated Building and Room for a unique record and the Person
ID is already unique. I then used Anthony's suggestion to get a number of
occupants per room. If you're interested, I sent an earlier email with a
bit more detail about what I did. I'm not happy with the process but the
result works.
-----Original Message-----
From: Ken Grubb [mailto:KGRUBB@xxxxxxxxxxxx]
Sent: Monday, June 24, 2002 10:07 PM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Access ???
Glenda,
I'm a little confused. Most logical course is to make one table for Rooms
and another table for Assignments.
- Primary Key on your Rooms table should be the room number as that would
most likely be unique.
- Primary Key on the Assignments table can be whatever you like, even an
AutoNumber, just so long as it's unique and it refers to the Occupant.
- The Room Assignment field in your Assignments table should be a foreign
key to the Rooms table. This way you'll only ever have valid room
assignments. After all, the room must REALLY exist for it to be assigned.
- Finally, use Anthony's suggestion in a Query to determine how many folks
are in each room. As he suggested, you can turn your Select Query into a
Make Table Query and you'll have a table showing how many people are in each
room.
If I haven't completely perplexed you, this should keep you busy.
HTH,
Ken Grubb
Burlington, NC
> -----Original Message-----
> From: Glenda Wells
> Sent: Monday, June 24, 2002 16:40
> Subject: [mso] Re: Access ???
>
>
> I have no idea what you're talking about Anthony.
> -----Original Message-----
> From: Colli, Anthony G
> Sent: Monday, June 24, 2002 2:14 PM
> Subject: [mso] Re: Access ???
>
>
> Do a count of Assignment >=2 in your case it might be something like
>
> SELECT Assignment, COUNT(Assignment) FROM MyTable
> HAVING Count(Assignment) >=2
>
> You can make a table from that query and then use it on the one side
> of a one -> many relationship.
> -----Original Message-----
> From: Glenda Wells
> Sent: Monday, June 24, 2002 1:27 PM
> Subject: [mso] Access ???
>
>
> I have a list of people in rooms. 1, 2 or 3 persons
> per room. If more than one person, then the persons
> are roommates. How can I tell Access in a query that
> if there are 2 or more ID's in 1 room, that they are
> the roommates. My objective is to send a letter to
> these people telling them that the other person or
> persons in the room are his/her roomie.
>
> Here's what I have
> Query2
> Assignment ID
> RADM 0001 16
> RADM 0002 95
> RADM 0002 792
> RADM 0002 758
> RADM 0003 23
> RADM 0003 247
> RADM 0004 448
> RADM 0004 5292
*************************************************************
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
*************************************************************
Other related posts:
