Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2004
    Posts
    3

    Unanswered: Newbie - Stuck in DB

    Greetings All!

    I'm quite new to Access rarely figure out successfully how to insert code I find on the forums and for it to work, but I keep trying...

    My project deals with reworking the example template Microsoft releases for "Event Management". What I have is a dance studio where everyone signed up for a class offered is tracked, their birthday (still can't get THAT to work, down to years and months, anyway...only years), etc. What I'm trying to add to this database is the function of not only collecting attendee registration information, but also who their partner is for that specific class. Partners change so the relation here would be to the RegistrationID number in the Registration table. I'm stumbling with figuring out how many additional tables I need to create, and what to put on a form that allows selections of two different Attendees to be saved as one couple. *sigh*

    Any help would be greatly appreciated. This function is the major reason for creating a new database altogether, because partners change over the course of time.


    Thank you.

    JT
    Raleigh, NC

  2. #2
    Join Date
    Aug 2004
    Location
    Steamboat Springs, CO
    Posts
    37
    Do you need to keep a history of these partners? In that as they change, would you still like to keep that history?

    This is just my first thought. One table seems to me to be enough. This table would have three fields GroupNumber, Dancer1, and Dancer2. There are several ways you could then assign them to a group. But I think the best strategy is to tie each dancer to a group rather than attempting to tie two dancers to each other.

    Jeremy Brooks
    PSIA-RM

  3. #3
    Join Date
    Oct 2004
    Posts
    3
    Quote Originally Posted by meremyb
    Do you need to keep a history of these partners? In that as they change, would you still like to keep that history?
    - Yes, history of couple combinations per class registration is what initiated this whole project. Right now the main table that contains all dance members is titled "ATTENDEES". This table currently tracks registered dancers, and then there is a REGISTRATION table that tracks who signed up and paid for what. I want to add to functionality to the REGISTRATION to track dance couples per their registered class, but for that dancer pairing to display in a subform when viewing each members data sheet.

    This is just my first thought. One table seems to me to be enough. This table would have three fields GroupNumber, Dancer1, and Dancer2. There are several ways you could then assign them to a group. But I think the best strategy is to tie each dancer to a group rather than attempting to tie two dancers to each other.

    Jeremy Brooks
    PSIA-RM
    - I've created another table called COUPLES in an attempt to collect the wanted dancer pairings here for a specific class they registered for. I don't know which relationships to create (or how, I'm still working on learning that one)...and then there is also the Form to use to select two different dancers, and have that information saved to the COUPLES table. Did that help?


  4. #4
    Join Date
    Feb 2004
    Posts
    533
    >Do you need to keep a history of these partners? In that as they change, would you still like to keep that history?

    Having the history would important to be able to schedule dances in advance and show the history.

    >This is just my first thought. One table seems to me to be enough. This table would have three fields GroupNumber, Dancer1, and Dancer2.

    The Group is a good idea this could add the ability of grouping two or more persons for a dance, sometimes this may the case. I think you would want to use three fields 'DanceID', 'GroupNumber', 'Dancer' you would use a combined index of DanceID and Group number to look like this.

    'DanceID' | 'GroupNumber' | 'Dancer'
    1 | 1 | 1
    1 | 1 | 2
    1 | 1 | 3
    1 | 2 | 4
    1 | 2 | 5
    1 | 2 | 6
    2 | 1 | 1
    2 | 1 | 2
    2 | 2 | 3
    2 | 2 | 5

    With this say you want to query the partners for Dancer 1 in Dance 2: You would do a query or lookup to determine the Group number 'where dancer = 1 and Dance = 2 Then use this in a query to obtain the lists of dancers in the returned Group/Dance number (group 2)

    One Group Table works alright to track this. You'll also have a 'tbleDance' to enter each dance and scheduled dance, and a 'tblePerson' to list the Dancers info and ID numbers.

    .
    ~

    Bill

  5. #5
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    JT

    I find myself somewhat at variance with my fellow posters. If you are going to set this up from scratch then you might as well do it according to best practices and accepted standards.

    You need at least three tables (or entities): Dancer, Class and Couple. Note that all names are singular as this is the convention when dealing with entities and entity relationships; plural names are used for Object Oriented Programming collections.

    There is one relationship between Class and Couple: each Class may have zero to many Couples and each Couple must have one and only one Class.

    The relationships between Dancer and Class is: each Dancer may be a member of zero to many Couples and a Couple member must be one and only one Dancer.

    This second relationship is implemented twice, once for each member of the couple. Can we call these the Male and Female relationships?

    The implementation of the Dancer and Class tables are quite straightforward. Assign each a unique key column and I would recommend that you use an autonumber for this. The sole purpose of the value in this column is to uniquely identify a row - nothing more nothing less.

    The Couple table is more interesting. You need to include one foreign key to the Class table and two foreign keys (male and female?) to the Dancer table. You may be tempted to then form a unique compound key by concatenating all three foreign keys. May I recommend you do not do so but simply include an autonumber column as in the other two tables. When you specify the Couple table you can make the foreign key values lookups from the related Class and Dancer tables. You can build in the other validation such as both dancers are required, this dancer must be male, this dancer must be female, etc.

    Once you have specified your tables you can then use the graphical relationships window to specify the relationships and enforce relational integrity.

    Just a further note about the Couple table. The set up I describe will allow for the fact that a couple may change during a class. Perhaps the class starts with couples A-B and C-D but during the class this changes to A-D and C-E. All that is needed is to include some dates on the Couple table. The set up also allows for overlapping classes and for different pairings for these overlapping classes. For example it may be A-B for class 1 but A-D for class 2.

    If you ever need to keep some notes about each couple as a pairing then implement a fourth table as a child table to the Couple table. You can then have zero to many notes or whatever for each Couple.
    Last edited by Rod; 10-12-04 at 07:26.
    Rod

    fe_rod@hotmail.com

  6. #6
    Join Date
    Oct 2004
    Posts
    3

    more relations, good in db only.

    DATABASE FILE IS ATTACHED AS .ZIP
    Virus free. - JT
    _______________________________________________

    Rod,
    Thanks for responding to my post. Well laid out though I found myself scrambling for how it related to my project. That's a newbie struggle though and I appreciated the explanations. I've never posted to a forum before and thought this reply to be a bit long, but feel it's more important to include all things relative to finding a solution. Based on your response I think I've already two of the tables you explained, but I didn't know how best to associate your CLASS table with one of mine.

    Using Microsoft's example database as I've done, the Event Management Database comes by default with tables having plural names. The names as they are now in plural form happen to best describe each table's purpose:
    Attendees (all persons signing up for an Event provided by the studio)
    Employees (self-explanatory)
    Event Types (Class, Demonstration, or Social are the types here)
    Events (Dance classes and their inclusive types of dances taught)
    Registration (tracks who signed up for what and when, paid amount, etc.)
    Couples (new, and topic of discussion now)

    I've created an additional table called Couples (3 fields: CouplesID (autonumber), Partner1, Partner2) to contain pairings between two different Attendees. While it's easy to create a form that mates two different records from the Attendees table into one "CouplesID" inclusively, I'm wondering if it can be done from the Attendee form. I've tried inserting two ComboBoxes but they don't collapse upon selection of an Attendee record, only when done as a separate Form. Which other table(s) will this one relate to in order to best pull the Couples pairing throughout my database.

    Future interests have the studio wanting to track couples as they register for particular Events. Current interest, however, has them only wanting to see who is paired with who while viewing the Attendee form. Is it possible to build this database based on what they would use it for later and still support present wishes?

    Things they want to do with the database are:
    (1) Collect Attendees signing up for Events as individual Attendees, with the data being entered into the database at a later time. There is not a secretary to dynamically apply Attendee info at time of Event Registration; possibly in the future though.

    (2) View who each event Attendee is paired with just by looking at the Attendee form, and possibly a printout of event Attendees as well.

    (3) Query the database for which Attendee is having a birthday that month based on zodiac sign (and, identify whether they are single or not). A dated usage, sure, but it has been something fun and different that the Attendees have enjoyed.

    (4) Monthly socials are common. Every month the studio opens its doors to the public for a social event; it turns into a dance club to primarily provide its dance students with the opportunity to practice what they've been learning. From those that registered, they want to be able to query for who is paired up and with who, are they single, and if so, display their sex to better accomodate pairings with the opposite sex also single.

    I'm thinking that most of the reporting (M/F, single) could be provided by simple check boxes on the Attendee form to populate table fields with a Yes No to better identify what the query is looking for. The biggest thing is this Couples pairing and it's relationship within the database as a whole. And then of course there is the whole issue of extracting that data so it displays properly. If Joe and Mary Smith are married and enter as a couple, if I'm viewing Joe via the Attendee form will it display only Mary's name or does it have to show both?

    -------- Your Post -----------
    You need at least three tables (or entities): Dancer, Class and Couple. Note that all names are singular as this is the convention when dealing with entities and entity relationships; plural names are used for Object Oriented Programming collections.---
    Dancer=Attendees?
    Couple=Couples?
    ...and I'm guessing that Class would be my...Registration table? - to best mate the Couple to the class for which they registered for? An autonumbered CoupleID mated to a autonumbered RegistrationID would retain Couple integrity in future registrations for different studio Events offered....no?

    There is one relationship between Class and Couple: each Class may have zero to many Couples and each Couple must have one and only one Class.
    --- Not so sure about that latter part. While each registration may have 0 to many couples, each Couple may in fact be registered and attending more than one dance Class under different RegistrationID's.

    The relationships between Dancer and Class is: each Dancer may be a member of zero to many Couples and a Couple member must be one and only one Dancer.
    --- Am I understanding correctly that the AttendeeID is to be related in a one-to-many CoupleID relationship?

    This second relationship is implemented twice, once for each member of the couple. Can we call these the Male and Female relationships?
    --- I'm lost with the "implemented twice" reference. How is that done? Male and Female? Sounds good, but we'll probably only use the Male / Female identifier to assist in pairing singles with other singles - moreso from a Report approach. Maybe just a toggle button or ComboList here to populate a field report query later?


    Thanks!
    Attached Files Attached Files
    Last edited by JTOckert; 10-12-04 at 15:38. Reason: Information concerning attached .zip file.

  7. #7
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by Rod
    JT

    I find myself somewhat at variance with my fellow posters.
    Rod, That is really an odd way to start out your post, about your fellow posters who you've never met. What does that say about yourself? I think Jeremy asked some pertinent questions and made a good suggestion. I don't see anything posted by the other posters in this thread that runs counter to good convention or rdbms design concepts.

    There is always more than one way of doing things in Access. Not everyone has the free time to write lengthy responses, and explain conventions and design concepts to people who may not even respond to their original request for help. (which is often the case in other threads, not in this case)

    You have excellent suggestions and a very well written response. I fully agree with your suggestions. Aside from the first sentence I couldn't have written it better myself.


    .
    ~

    Bill

  8. #8
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Bill,

    It wasn't meant in a sinister way. You yourself say that there is more than one way of doing things, that's where the variance comes in. It is often difficult to anticipate the tone and inflection that readers will put on your words.

    Rod
    Rod

    fe_rod@hotmail.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •