Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2010
    Posts
    2

    Unanswered: Form & query to make records from 2 different tables appear as coming from 1

    I have 2 tables with different kinds of appointments for a daily schedule. I need their records to display in a form with the appearance of being in a single table. And with that, the database user needs to be able to click on an "Edit" button to the left of any of these records, where they can either edit the appointment details within the form, or in a different form which would pop-up.

    The idea is that they have 6-8 different types of appointments with data that is so different, that different tables were used for each type of appointment.

    However, in their daily printed schedule (which is 2 printed pages long), they have 4 sections of appointments that show up as tables. But they actually have 6 appointment types (which means 6 different tables). They have been sharing 2 of the sections of the printed daily schedule, with 2 types of appointments. In other words, 4 types of appointments are being represented by only 2 tables on their appointment, while in the database it is 4 tables (1 for each appointment type).

    The formatting of these shared tables on the printed daily schedule is basically some shared columns. Each section of the daily schedule shows all relevant appointments in chronological order. However, there may be an appointment of "Type A" at 10am, then "Type B" at 11am. Because Type A & Type B appointments have different fields, the columns on the daily schedule are shared between the different field types that are used by these two types of appointments. Each column is always consistently displaying only 1 specific field for "Type A" appointments, and only 1 specific field for "Type B" appointments. So there is some logic there.

    Here's a simplified example of the tables & form/report that I am to produce:
    Code:
    tblGuidedToursAppointments
    ==========================
    AppointmentDate | AppointmentTime |     GroupName      | TourGuideID | GroupSize | TourVehicleID | PrePaid | HasBeenHereBefore
    ------------------------------------------------------------------------------------------------------------------------------
    6/9/2010          10:00am           Sunshine Elementary       1           12            4             1             0
    6/9/2010          11:30am           Brimhall Family           3           4             2             1             1
    6/9/2010          03:00pm           McKinley Elementary       1           8             4             0             1
    
    
    
    tblScienceClassAppointments
    ===========================
    AppointmentDate | AppointmentTime |     GroupName     | TeacherTeamID | GroupSize | ClassRoomID | NeedsSupplies | WantsVideo | 
    ------------------------------------------------------------------------------------------------------------------------------
    6/9/2010          09:00am           Eastern Tourists         2              12           2              1             0
    6/9/2010          10:00am           Brisbane Surfers         1              6            1              0             1
    6/9/2010          10:30am           Zulu Warriors            2              22           3              0             0
    
    
    
    frmDailySchedule
    ================
          |
           -frmSubformGuidedToursAndScienceClass
            ====================================
            ApptTime | ApptType |     GroupName       | Guide/Team | GroupSize | Vehicle/Room | PrePaid/NeedsSupplies | HasBeenHereBefore/WantsVideo
     ----   ----------------------------------------------------------------------------------------------------------------------------------------
    |Edit|  09:00am    SciClass    Eastern Tourists      Team B        12        Room 2          Supplies are needed      No
     ---- 
    |Edit|  10:00am    Tour        Sunshine Elementary   Bryan         12        Ford Bronco     Prepaid                  First time here
     ----
    |Edit|  10:00am    SciClass    Brisbane Surfers      Team A         6        Room 1          Bringing own supplies    Yes
     ----
    |Edit|  10:30am    SciClass    Zulu Warriors         Team B        22        Room 3          Bringing own supplies    No
     ----
    |Edit|  11:30am    Tour        Brimhall Family       Sally          4        Opel Corsa      Prepaid                  Previous visitor
     ----
    |Edit|  03:00pm    Tour        McKinley Elementary   Bryan          8        Ford Bronco     Needs to pay             Previous visitor
     ----
    Obviously, there are a few tables that I am not showing here (tblTourGuides, tblTourVehicles, tblTeacherTeams, tblClassRooms). And as indicated, this is just a simplified sample. There are really 4 sections/subforms, showing a total of 6 appointment types (from different tables). And there are more fields that what I'm showing here.

    I know that this would be much simpler to do, if we were to just have a separate table/subform in our report (as well as the printed daily schedule report) for each type of appointment. However, in creating this Access database system, I have been asked to not change the layout of the daily schedule. I am doing a project for a non-profit organization who has a lot of volunteer staff who are in and beyond their "golden years" (60s-80s). Retraining them on new processes is time consuming and is being avoided at all costs.

    My question is simply, can this be done? And if so, how? I was thinking of just basing the form off of a query that I build with some if/then logic in the shared fields, so that if it is a record from the Tour table, the Guide will be returned, and if it's a record from the Science Class table, the Teach Team will be returned. I think I know how to do this. However the challenge comes from the fact that they want an "Edit" button next to each appointment so that they can click it to either make that line on the form switch to edit mode, or simply bring up a different form to edit that specific appointment. However, since we're dealing with two different tables, I just don't know how I'd tell the button to edit that appointment. Because the form would be based on a query that would be based on 2 different tables, as well as calculated values (the if, then logic), I don't think I can simply edit a record in that form. If the query is uneditable (http://support.microsoft.com/kb/304473), then I'm sure the form would have the same problem.

    So then I was thinking of possibly assigning a GUID to each appointment. Then create some type of VBA code tied to the edit button to find a record in any table with the GUID of the current line in the form/query, and bring up the appropriate form to edit that type of record.

    However, I'm really just formulating hypothesis here over how I could accomplish this. Experienced Access programmers will definitely know the best way to accomplish this (assuming that it can be done).

    With that said, I think this post is long enough, and has probably gotten the point across. Please ask questions for more detail, and I will post answers. I hope that one of you can steer me in the right direction to accomplish this objective.

    Thanks in advance for your help! :-)

    Cheers!!
    -
    Doug

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Your idea of using a GUID is a good one. From the moment you are able to determine the origin of a line in the query, you can use VBA and SQL to perform INSERT, UPDATE and DELETE actions against the appropriate table. Don't forget that you'll have to requery the data afterwards.
    Have a nice day!

  3. #3
    Join Date
    Aug 2010
    Posts
    2

    Union Query

    Sinndho.

    Thanks for the thoughts. I have thought that I need to requery the data for the form after it is updated. So with your reinforcing that though, I'll definitely go that way.

    I did post this question to a few other forums and did get some more helpful info. I've decided that I probably don't need a GUID. Here's the route I'm planning to take to get the expected results:

    UNION query from the two tables in order to get the data formatted in the columns that I need. From there, create a Multiple Items form with an "Edit" button to the left of each record. The action on the edit button will be some VBA code that will read the AppointmentType field (which I'll manually force via the UNION query), and it will then determine which additional form to open, in order to let me edit the given appointment ID in the appropriate table.

    Then as you recommend, the table will be updated, and back to the daily schedule form, data will be refreshed, and we should have the objective captured!!

    Thanks again for your help!

    -
    Doug

Posting Permissions

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