How to capture paired member records within same table?

[jean.ockert]

Greetings.

Access version 2003 on an XP system
I am using the template "Event Management" database as the primary
database and need additional functionality added. I have a dance
studio. Dance "events" do not change, only the months they occur. I
have Attendee, EventTypes, Registration, and Classes tables. I need
to add the following...

To be able to track who is registering for particular dance classes
with whom as their partner. Both dancers would be separate records
within the same ATTENDEES table, but dance couples may change per
class they register for. This couple would need to be asssociated with
the Registration table per signup for that particular class. That
couple would also need to be visible on the data form showing
information for a particular dance attendee. My thought is that
another table needs to be created (maybe "Couples"), and linked to
both the Attendee and Registration tables. I don't know how many
fields for the new table are required (I'm thinking 4. CouplesID
(autonumber), Pairing (autonumber?), Dancer1, Dancer2. I could be
waaay off.) or which relationships are required.

I'm very green (1yr) and self-taught up til now, most of the code I
see that could be beneficial I can't even figure out how to apply to
my project half the time..but I'm still chugging along. The challenge
is nice.
Thanks in advance!
JT

[Phil Stanton]

I am struggling to understand what you want. Perhaps you could give some
examples.
for example are the dance "events" something like Waltz, Rock & Roll etc
Assuming you have a table of Attendees containing among other things
AttendeeID Autonumber
AttendeeName Text
and a table of Classes containing
ClassID Autonumber
ClassName Text

To sort out your couples, you need a table linking the above 2 table say
JoinAttendeeClass.
This will contain
AttendeeID1
AttendeeID2
ClassID

If you can clarify your thoughts with examples, more help may be forthcoming

HTH

Phil
<je*********@mindspring.com> wrote in message
news:63**************************@posting.google.c om...

Greetings.

Access version 2003 on an XP system
I am using the template "Event Management" database as the primary
database and need additional functionality added. I have a dance
studio. Dance "events" do not change, only the months they occur. I
have Attendee, EventTypes, Registration, and Classes tables. I need
to add the following...

To be able to track who is registering for particular dance classes
with whom as their partner. Both dancers would be separate records
within the same ATTENDEES table, but dance couples may change per
class they register for. This couple would need to be asssociated with
the Registration table per signup for that particular class. That
couple would also need to be visible on the data form showing
information for a particular dance attendee. My thought is that
another table needs to be created (maybe "Couples"), and linked to
both the Attendee and Registration tables. I don't know how many
fields for the new table are required (I'm thinking 4. CouplesID
(autonumber), Pairing (autonumber?), Dancer1, Dancer2. I could be
waaay off.) or which relationships are required.

I'm very green (1yr) and self-taught up til now, most of the code I
see that could be beneficial I can't even figure out how to apply to
my project half the time..but I'm still chugging along. The challenge
is nice.
Thanks in advance!
JT

[JT O]

Hi. Yes, you're correct on the tables I have, they are similar.

Tables I have (each having an autonumber field) that may be important to
this are...
Attendees (data concerning anyone registering for an Event)
Event Types (Social, Demonstration, and Class)
Events (these list the dance classes themselves and their contents)
Registration (tracks who registers for which Event, payments, etc.)
Couples (my new table for this feature I'm trying to add; having fields
"CouplesID"(autonumber), "Partner1"(text), "Partner2"(text))

-- There are a couple other minor tables, but I believe what I'm trying
to do rests somewhere here.

--- Where would table Couples be best tied to and having what
relationship?

What the studio wants of this database:
1) to enter each event Attendee as an individual for each Event they
register for.
2) To be able to assign Attendees as Couples (two Partners, usually
married) and to be able to view the partner of any Attendee upon which
screen they may be viewing/editing
3) to, later, track which Couples registered for Events. (I'm thinking
this process would best be tracked by the Registration table, no?)
4) of those Attendees registered for an event, to be able to query for
those Attendees not coupled, and to be able to identify which are Male
and Female for better pairing during Social events.

I hope that helped. I could send you a copy of the db (it's very small)
so you can see what I'm talking about to better help. let me know.

JT
jeanDOTockertATmindspringDOTcom

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Phil Stanton]

OK Jean, a bit clearer

The couples situation is obviously very varied (dare I suggest wife
swapping)
You do NOT want a CoupleID and the text of the partners names. Basically you
select 2 attendees from the list that you already have (use a Combo Box) and
add them to your registration table.
So the registration table should look something like
RegistrationID AutoNumver
EventID Long ' Points to EventsTable
Partner1 Long ' = AttendeeID Male or butch
Partner2 Long ' = AttendeeID Female?
Date Date
FeePaid Currency

Somehow I feel that the event table and registration table are the same
thing.
The attendee table will contain details of their sex

If you want to send me your Db I may find time to have a look, but no
promises. Please Name the Db Dance.xxx

Phil

"JT O" <je*********@mindspring.com> wrote in message
news:41**********************@news.newsgroups.ws.. .

Hi. Yes, you're correct on the tables I have, they are similar.

Tables I have (each having an autonumber field) that may be important to
this are...
Attendees (data concerning anyone registering for an Event)
Event Types (Social, Demonstration, and Class)
Events (these list the dance classes themselves and their contents)
Registration (tracks who registers for which Event, payments, etc.)
Couples (my new table for this feature I'm trying to add; having fields
"CouplesID"(autonumber), "Partner1"(text), "Partner2"(text))

-- There are a couple other minor tables, but I believe what I'm trying
to do rests somewhere here.

--- Where would table Couples be best tied to and having what
relationship?

What the studio wants of this database:
1) to enter each event Attendee as an individual for each Event they
register for.
2) To be able to assign Attendees as Couples (two Partners, usually
married) and to be able to view the partner of any Attendee upon which
screen they may be viewing/editing
3) to, later, track which Couples registered for Events. (I'm thinking
this process would best be tracked by the Registration table, no?)
4) of those Attendees registered for an event, to be able to query for
those Attendees not coupled, and to be able to identify which are Male
and Female for better pairing during Social events.

I hope that helped. I could send you a copy of the db (it's very small)
so you can see what I'm talking about to better help. let me know.

JT
jeanDOTockertATmindspringDOTcom

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[JT O]

Phil
Hi Phil,
I'd send the DB but am unable to pull an address for you from this site.
The following link will take you to another posting concerning my
database and the database is attached there as a file.

DBForums posting with attached DB file for Dance Studio.
http://www.dbforums.com/t1041867.html

Personal address is je*********@mindspring.com

The Couples situation does vary from time to time, but it's not
often. Swapping is not encouraged. lol
The Event and Registration tables aren't the same. The Event table
only lists about 4 events; those being "Group Class", "Private Class",
"Social", and "Demonstration". There is a separate Fee Schedule table
that separately holds the types of Events such as Country/Line Dancing,
Waltz/Ballroom, Swing/Lindy Hop, etc. The Registration table is the
meat of the workings here in that it tracks who did what, when, for how
much, paid, etc.
Wouldn't we want to keep the sex of the Attendee with the Attendee
record itself? It's static information that applies to each Attendee
whereas the Registration table is constantly changing each time an Event
is registered for. What's your thought behind tracking sex of the
Attendee in the Registration table?
Couples registering at the studio rarely change; the majority are
married. It is, however, realistic to think that in some instances
couples may change over the course of time, such as a
boyfriend/girlfriend duo. Tracking one Attendee with another as a couple
is independent of what their sex is so it could very well be a
mother/daughter couple that just requires us to pair them up temporarily
with a single male during a social or group class Event.

However we design this it just needs to allow the following...
- to track couples
- to query within Attendees registered for an event who is coupled with
a partner already versus who is single. The Male/Female field will
assist here in that we want to be able to pair up as many as possible
for group classes and social events to have minimal single dancers left.
- to view an Attendee record and see whom they are paired with. (I'm
thinking this is what removes the whole Couple tracking from being
integrated into the Registration table, but that it instead be a
separate table related TO the Registration table. And this is where I
stop; not knowing what's possible versus what is required to accomplish
this. blah. LOL

If you go to dbforums.com / PC Based Database Applications / Microsoft
Access you'll see other ideas concerning this solution as well as see my
posting with the database attached. This is only a few days old and I
don't really have anything to work with at this point.
JT

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!