Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Form Record Matching (2K2)

    I have a form (frmSchedulerRN) that contains 2 subforms. The top subform (frmToBeStaffed) lists events that require staffing on a particular date and time. The bottom subform (frmRNMatcher)
    Easy Access

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Form Record Matching (2K2)

    What i would be looking to do is make the record source of frmRNMatcher a query that takes that day in frmToBeStaffed as a parameter, then requery frmRNMatcher in the oncurrent event of frmtobEStaffed.

    However the way you have set it up makes this difficult (or cumbersome) to do, because the staff availability info is contained in 14 different fields,

    I would create a separate StaffAvailability table, with an autonumber key, EmployeeID, DayNumber, and two boolean fields AvailableAM and AvailablePM.

    You can then use the DatePart function to return the DayofWeek of the event. The query can then join staff to staff availabilty, and return records where the day matches the Day of the event, where either of the two boolean fields are true.
    Regards
    John



  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Form Record Matching (2K2)

    I worked on a similar project once (matching casual staff to casual shifts) and we found that we needed to make it vastly more complicated to be useful.

    *It was not enough to know that someone was usually available on Wednesday afternoon. We needed to be sure that they were availabe on this specific Wednesday afternoon. You may need to capacity to cope with variations to the usual pattern: I can't work this Wednesday because of..., I could work on Thursday this week if there is anything available.

    We actually had a table where there was a separate record for each person and each day. So we needed quite a lot of code to create records in this table whenever they were needed.

    * There was no point in the system displaying staff as available, when they had already been allocated a shift for the time period in question.

    * We needed to be aware of rules about the minimum break required between shifts, max shifts in a week etc. I can't remember the detail fo these, but they were a major constraint on what people could do.
    Regards
    John



  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Form Record Matching (2K2)

    Thank you John for the advice. I had a feeling when I got this request that it would not be easy and you have confirmed this. I have a question about creating the new table StaffAvailability.

    1. What would the DayNumber field represent? I am assuming it would be a long integer?
    2. What relationship would the new table have to tblEmployee?
    3. Is the Dayof Week part of the DatePart Function? I am not familiar with this?

    If you could give me a little direction on this it would be appreciated. Thanks!
    Easy Access

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Form Record Matching (2K2)

    Lets start with DatePart. You can use it to inspect a date and return just a part of it. To return Dayofweek In a query) use this format:

    DayOfweek: DatePart("w",[datDate]) would return 1 for Sunday, 2 for Monday etc (this actually depends on a first day of week setting).
    (In my copy of access 2003, there is good help info on this. Go to help from the VBA Editor.)

    So in tblStaffAvailability I would have day numbers that correspond to the values returned by this function. Thye would represent day of the week. You could create a daysof week table to translate these numbers into words if you wanted.

    As they will never be larger than 7, I think you could use Integer rather than longInteger.

    The relationship with tblemployee is via lngEmployeeID.

    Have a look at Hans' recent <post#=485181>post 485181</post#> . It is about exactly this situation.

    I am not sure how cut down the db you posted is, but based on what you have posted I think major changes to your table structure are needed. for instance tblSchedule has lots of fields that need to be in a separate tblclinics, linked to tblschedule via a clientID. You have lots of repeated language fields in there as well. I am not sure what they represent, nor whether they relate to a clinic or a specific sdhedule, but I am sure they belong in a separate table.

    tblemployees has a number fo repeated fields: ysnYearworked01, 02 etc strChoice1, 2 3

    Other cases you use several fields when one field would be easier to manage. eg Glovesize should be one field taking values of small, medium or large.

    New or return should be one field taking either of these two values.
    Regards
    John



  6. #6
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Form Record Matching (2K2)

    Thank you for the detailed advice. Unfortunately, I have "inheirited" this database and have been tasked with beefing up its capabilities. I will give you suggestions a whirl and give you some feedback when I am done.
    Easy Access

  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Form Record Matching (2K2)

    Your suggestion did the trick... I appreciate your help. I have look at the the overall design of my tables and I will begin to "normalize" how they are formatted.
    Easy Access

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Record Matching (2K2)

    If I may add a small suggestion
    For your sub forms change your prefix to fsub or sfrm and for you main form leave as frm or change to fpri
    you'll find this useful down the road.
    threecrow

    Don&#39;t make excuses. It&#39;s what you do, not why you didn&#39;t.

  9. #9
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Form Record Matching (2K2)

    Thanks for the suggestion...I have some serious work to clean up this database. Your idea sounds like a good naming suggestion.
    Easy Access

Posting Permissions

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