Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Same person, 2 time periods (2003)

    I thought I had this little database fixed up all tidy and neat, and then they went and threw an odd-ball case at me. The database tracks participants attending a two-week conference. Most participants arrive on a given date (Sunday or Monday of week 1) and stay till a certain date. Today I learned that one participant (just one!) will arrive on day 1, depart on day 4, return on day 8 and depart on day 13. I'd built my lodging report around a table that includes one arrival date and one departure date. The only solution I've come up with is to have two records for this participant. Is there a better solution?

    This solution creates a small problem with one of my reports. Each participant will work in one of seven small groups. I've matched each name with the proper group for the report. Now the listing for this one work group will show the same person twice. This report includes arrival and departure dates (so we'll know we have enough people for a group every day).

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Same person, 2 time periods (2003)

    I'm assuming that the DateArrive and DateDeparted fields are currently part of the table that includes the participant name, etc. You need to create another table (tblStays, perhaps?), and move these dates to this table. This table will the on the many side of a one-to-many relationship with participants table, and can have more than 1 record per participant. This will, of course, probably change your data entry screens, all your queries, and your reports, but that's like in the fast lane!

    Or, you could just treat this person as 2 separate people! Depends on who is paying for all the changes required.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Same person, 2 time periods (2003)

    Lucas,

    This makes for an interesting problem. I have to assume you setup your database such that you have conferences linked to partipants in a one to many relationship so one conference can have many participants. In addition you say that you have a table that stores arrival and departure dates and that this new twist requires more then one entry that is effecting the report.

    Rather then put in two dates as you are doing that causes report problems, leave the original arrival and departure dates so only one entry per person is required. Then in a separate table, you could store the conference ID, Participant ID, and dates the participant is Not available. You would have to incorporate this data into your report for lodging and daily groupings of people. This way your original design and concepts are not changed as this may not be a common occurrence.

    ... I know, this is easy to say in writing but without seeing the database I can't offer much more. Hope this gives you some ideas.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Same person, 2 time periods (2003)

    You assumed correctly: DateArr and DateDep are simply fields in tblParticipants. It occurred to me that another table--as you recommend--could fix the problem, but I couldn't visualize how to set it up. You've given me some ideas. Thank you!

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Same person, 2 time periods (2003)

    Actually, I do not have a separate table for arrival and departure dates. That's what I need, as Mark pointed out. Thanks for the input!

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Same person, 2 time periods (2003)

    Your new table, at a minimum, would have 3 fields: ParticipantID (matching to your participant table), DateArr, and DateDep. I also like to include an autonumber field to provide for a primary key field. You could, of course, add other fields as needed (maybe a yes/no field for "Confirmed", etc.).

    On your data entry form, you would need to add a subform, in which you would enter the DateArr and DateDep. It is linked via the ParticipantID fields in both the primary and new table.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Same person, 2 time periods (2003)

    Great suggestion! Thank you!

Posting Permissions

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