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

[dkmort]

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:

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

 

[jrbarnett]

Use a union query to join the tables.
add an extra field into each section that indicates the type of appointment (so they can be separated out under the correct heading) and indicate NULL values for columns where not applicable.

eg

SELECT AppointmentDate, AppointmentTime, GroupName, TourGuideID, GroupSize, TourVehicleID, PrePaid, HasBeenHereBefore, 'TourAppointment' As Type
FROM tblGuidedToursAppointments
UNION
SELECT AppointmentDate, AppointmentTime, GroupName,    TeacherTeamID, GroupSize, ClassRoomID, NeedsSupplies, WantsVideo, 'Science Class' As Type
FROM tblScienceClassAppointments

YOu will need to add where clauses to filter for specific people and an overall ORDER BY to sort by overall start date/time. Editing will need to be a separate form and then refresh this list when they exit.

John

 

[TheAceMan1]

How are ya dkmort . . .

[blue]I have been asked to [purple]not change the layout[/purple] of the daily schedule.[/blue]

So you already know your working with your hands tied!

[blue]My question is simply, can this be done?[/blue]

Hard to say without getting deeper into your table design, table relationships and use of primary keys.

[blue]However, I'm really just formulating hypothesis ...[/blue]

Do have a look at [blue]Union Queries[/blue]! I see a great need for them ... espcially in the report.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dkmort]

Thanks to everyone for the replies. It has been very helpful. I have looked into Union Queries, and it appears that that is the way to go here. I've created a simple union query at this point with only a few of the fields, and it looks good. Now I'm working on creating the actual form that will display them, and the VBA code for the action button to edit them.

Thanks again! You guys are great.

-
Doug