13 Replies Latest reply on Jul 8, 2014 4:30 AM by GuyStevens

    1st time FM12 Adv user trying to create a WW2 Bomb group database

    b24mike

      Title

      1st time FM12 Adv user trying to create a WW2 Bomb group database

      Post

      I am th eunit historian for the 445th Bomb Group, a B-24 unit that flew out of England during World War II.  I'm wanting to create a relational database that will allow me to lookup an individual and generate a report that will become a Personal Mission History of the missions he flew, the crew he flew with, the plane he flew in and the target that they bombed.

      The data I have comes from the National Archives in College Park, MD and the Air Force Historical Research Agency.  I have the crew load lists which consists of the names and crew positions for each of the 280 missions flown.  Each load list also identifies the tail number and radio call letter of the plane each crew flew in that day.

      The bomb group was comprised of 4 bomb squadrons; each with 16 assigned aircraft and approximately 20 crews (10 men to a crew).  Crews were assigned to a particular bomb group but may have been transferred to another squadron during their tour of duty.

      I have Excel spreadsheets of the following:
      1.  Aircraft assigned to the unit - make, model, block number, tail number, call letter, name, squadron assigned to, Missing Air Crew Report number (if shot down)
      2.  Master Crew Roster - 4,500+ names (last, first, MI), rank, serial number, military specialty code (MOS), duty position
      3.  Master Target List - all 280 missions flown with date, mission number, target city, country, target description, bomb load (per aircraft), bomb damage scoring, bombing altitude, # aircraft takeoff, # aircraft over target, # aircraft lost, flying time.
      4.  I would like to be able to add a PDF file with the Tactical Mission Report for each mission.

      I'm currently using a flat file Access database with tables for each of the four squadrons and the crews listed by the pilot they flew with.  I have queries written which allows me to enter an individual's name and find what missions he flew.  I then have to go to another set of queries to see each mission's crew listing.

      If anyone has experience in setting up a similar structured database, all suggestions would be appreciated.  Direct contact to answer questions or provide more detail can be arranged by email contact at Mike@445BG.org.

        • 1. Re: 1st time FM12 Adv user trying to create a WW2 Bomb group database
          philmodjunk

          There seems to be key information missing. How, just from the records you list, can you tell who was crew for a given aircraft on a given mission?

          It appears you need  table that lists the crew for each aircraft for each mission and I don't see such a table listed.

          • 2. Re: 1st time FM12 Adv user trying to create a WW2 Bomb group database
            b24mike

            Sorry - that was part of the Microsoft Access database.  It was originally a series of Lotus 1-2-3 spreadsheets designed by a former member of the bomb group.  He created 8 tables - 2 for each of the 4 bomb squadrons.  Due to the limit of 256 columns in 1-2-3, he broke the listing up by years.  In 1943/1944, the unit flew 210 missions.  In 1945 they flew the remaining 70 missions.

            Each row in the table for a squadron were arranged like this:
              1.  Pilot (in alphabetical order)
              2.  Under each pilot were all of the men who flew with him


            The columns contained the following (L to R):
                    Name
                    Crew position
                    Mission number (1-210 for the first squadron table) (211-280 for the second squadron table)

            So, for the 700th Bomb Squadron, there are 2 spreadsheets; 1 for 1943/44 and 1 for 1945.  This was repeated for the other 3 squadrons (701st, 702nd, and 703rd).  When the 445th went to England in late 1943, Jimmy Stewart was the 703rd Squadron Commanding Officer.

            I envision the primary key being the mission number (1-280) and possibly a secondary key of a pilot's name.  From the mission number and pilot's name we get the aircraft assigned to that pilot on a given mission.  Whenever possible they allowed the pilots to fly the same plane unless it was down for maintenance or repair.  Then they would get another plane (possibly not from their own squadron).  Aircraft availability was presented by the Maintenance Chief to Group Operations and pilots were assigned at that time.

            Each pilot had a crew that he deployed with, but sometimes injuries and illness required a replacement crewman.  Replacements always came from within their own squadrons.  Early on in 1944, the 8th Air Force shifted bombing strategy and went with "area" bombing.  This meant that not every plane needed a bombardier.  There were lead and deputy lead crews assigned to each squadron and they always flew with at least a 10-man crew.  Sometimes they would have an extra navigator who would ride in the nose turret with a map sheet (known as a pilotage navigator).  During the summer of 1944, each squadron was equipped with several radar equipped aircraft that the lead crews flew.  In February, 1945 all lead crews were transferred to the 701st Bomb Squadron with their aircraft and excess personnel/aircraft from the 701st went to the other 3 squadrons as fillers.

            • 3. Re: 1st time FM12 Adv user trying to create a WW2 Bomb group database
              philmodjunk

              Managing crew assignments is really a many to many relationship. It could be set up via a time line to note crew changes or for each mission flown.

              Either way, it's a many to many relationship between Aircraft and Crew, but the details in how you set up the data differ.

              Crew---<Aircraft_Crew>-----Aircraft

              Crew::CrewID = Aircraft_Crew::CrewID
              Aircraft::AircraftID = Aircraft_Crew::AircraftID

              If you use the "time line" approach, you include date fields in Aircraft_Crew that record when a crew member joined and left that aircraft.

              If you use a mission oriented approach, you'd have a missionID field in aircraft_Crew and you'd need a complete set of records in it for each mission the aircraft flew.

              With either approach, you can include a field in aircraft_crew that identifies the "pilot", "navigator", etc--but such data may work better from a mission oriented approach than a time line approach given that a crew member's role may have been mission specific.

              The more I think about the final paragraph in your last post, the more I think a Mission Oriented aproach to setting up this "join" table will be the best option for your data.

              • 4. Re: 1st time FM12 Adv user trying to create a WW2 Bomb group database
                GuyStevens

                If you are new to filemaker and need a beginners screencast to find your way around in filemaker you could watch this:

                http://www.youtube.com/watch?v=5v2EzvaWyBk

                This could help you with what should be your first step:

                Making a table to store your pilots and making a nice layout.

                • 5. Re: 1st time FM12 Adv user trying to create a WW2 Bomb group database
                  b24mike

                  Folks:  I'd like to thank those responders for some great ideas.  From DaSaint's answer, I went to the YouTube link he posted, which in turn lead me to Lunda.com where I purchased a training DVD on relational databases using FM Pro 12.  I'd like to float my design by you and would appreciate any and all comments, suggestions, or thoughts.

                  This is a fairly complex issue I've been struggling with for about 12 years.  I would like to be able to set up a search where I could enter a crewman's name and be able to generate a report of the missions he flew on; who he flew with; where they went on each mission; how many planes were sent and how many planes were lost.

                  Givens:

                     1.  A total of 280 missions were flown between 13 Dec 43 and 25 Apr 45
                     2.  Each mission would have many aircraft/crews assigned to fly
                     3.  Each crew member flew with a specific pilot on a given mission.
                     4.  Each plane carried a variable number of crewmen in various crew positions (between 8 to 13 personnel)

                  My plan is to use 4 tables (Mission_Table, Pilot_Table, Aircraft_Table, and Crewman_Table).

                  Mission_Table:  Indexed from 1 - 280 with all the details relating to the target, bomb load, target description, # aircraft sent, losses.

                  Aircraft_Table:  First quandry - 1 mission = many aircraft   and   1 aircraft = many missions.  Would I need something like a join table to show multiple aircraft on a given mission or one aircraft flying many missions?

                  Pilot_Table:  1 pilot to a plane (not counting the copilot).  1 pilot = many missions   and   1 pilot = many aircraft.  How do I indicate a pilot flying on a particular mission in a particular aircraft?

                  Crewman_Table:  Another quandry - each crewman flew with a specific pilot on a given aircraft on a given mission.  They didn't always fly with the same pilot.  To make matters worse, they didn't always fly in the same crew position (mostly the gunners).

                  I'm attaching a screen capture of my first draft and would appreciate comments.

                  • 6. Re: 1st time FM12 Adv user trying to create a WW2 Bomb group database
                    GuyStevens

                    Is it so that you need 3 levels?

                    Like Every mission (level 1) has one or multiple airplanes (level 2)

                    And every airplane has one pilot and multiple crew (level 3)

                    There is a way to do this on a layout with two portals (lists)

                    Where you can select something from the first list (an airplane) and then you see in the second list the crew related to this airplane.

                    Also: Can a pilot also be a crewmember?

                    Or be a crewmember and become a pilot?

                    I ask this because you have a seperate table for pilots.

                    I would also change the relationships a little (and maybe use some TO.s here and there) but I'll have to look at this again in a few days because I don't have time now.

                    • 7. Re: 1st time FM12 Adv user trying to create a WW2 Bomb group database
                      b24mike

                      DaSaint:
                      Thanks for the input.  Let me try and answer your questions.

                      3 levels?
                      To me that seems the easiest.  I have a fixed number of missions flown (280).  Each crew is organized around the pilot.  Each pilot flies a specific plane on each mission.

                      A pilot is considered to be a crew member, but not every crew member can be a pilot.  Not every pilot flies as a pilot.  Some pilots will fly as copilot for a couple missions, then be given his own crew and he flies as the pilot.

                      What is a TO?

                      • 8. Re: 1st time FM12 Adv user trying to create a WW2 Bomb group database
                        GuyStevens

                        A TO is a table occurrence.

                        But maybe that's running a few steps ahead of ourselves. Let's worry about that later.

                        I was thinking it might not be a bad idea to put pilots in the crew table. You could give them a checkbox "Pilot" that you check when a certain person is a pilot.

                        That way when a pilot is being a crewmember you don't have to save him in both the pilot and the crew table.

                        • 9. Re: 1st time FM12 Adv user trying to create a WW2 Bomb group database
                          GuyStevens

                          Someone else was asking about this technique of having 3 levels of data, or 3 related tables and showing two of them in a portal so I made a little screencast showing how I do this technique:

                          http://www.youtube.com/watch?v=VJqA03X33DQ

                          If you want to see everything clearly then set the video to play at a higher resolution using the controls on the bottom of the video window.

                          And there is a little demo file here:

                          https://dl.dropbox.com/u/18099008/Demo_Files_FMP12/TwoPortals_Billing_Deposits.fmp12

                          That's all the time I have for now but I do intend to give your situation a closer look so I can make a little bit of a suggestion of how I would do this but I'm pretty busy right now so it might have to wait a little.

                          • 10. Re: 1st time FM12 Adv user trying to create a WW2 Bomb group database
                            GuyStevens

                            You seem to have a few fields in your Mission table that are actually related to an aircrafts mission details.

                            They should be stored in the Join table.

                            But that might be a little hard for you to imagine because you don't have that table yet.

                            I'm trying to make a little demo file to show you how it could be done, according to me.

                            • 11. Re: 1st time FM12 Adv user trying to create a WW2 Bomb group database
                              GuyStevens

                                   OK, here is my attempt.

                                   It's still pretty basic, but I think a nice first stab at what it could become.

                              https://dl.dropbox.com/u/18099008/Demo_Files_FMP12/WW2_Bomb_Group.fmp12

                                   The idea is that you can create missions. In a mission you can add airplanes. Then you need to select an airplane!! And then you can add the crew for that selected airplane.

                                   Then when you go to the airplane and crew layouts you can also see what missions they participated in.

                              • 12. Re: 1st time FM12 Adv user trying to create a WW2 Bomb group database
                                b24mike

                                     Guy:

                                     I must apologize profusely for not getting back to you before now.  At the time I submitted my request and read your replies, things got really crazy at work.  We suddenly decided to downsize a considerable chunk of our workforce while increasing our workload. I went on 60 hour workweeks until April 2013 when I got my layoff notice.  Luckily I had already submitted my paperwork to retire and did so on April 30th, 2013.  Project around the house kept me busy until the start of this month when a friend asked me when we might be able to demo my database project at the 2015 8th Air Force Historical Society meeting.  That prompted me to start looking back at FileMaker and I remembered the forum so I thought I'd check it out.  Long story short, here's my reply.

                                     What you've shown me is a great start.  I think I can work with this, refining the tables and plunking in the additional fields.  I have a fellow board member who is fairly sharp using FileMaker, but he lives in the DC area so we communicate via Skype.  I'll show Brian what you've sent me and we'll take it from there.  When I mention fellow board member, I was elected as the Executive Vice President for the Heritage League of the 2nd Air Division (USAAF) last Fall.  We represent the 14 different B-24 bomb groups that made up the 2nd Air Division.  Not only will we demo this to our organization, but at the 2015 8th Air Force Historical Society reunion, we will demo this database for the researchers of the 8th Air Force.  I'm attaching a layout that my friend Brian came up with (relationship tab) showing the tables he came up with.

                                     Let me thank you for your time and effort and we'll make sure that you share in the credit for this development effort.

                                     Thanks again...

                                     Mike Simpson

                                     Exec VP, Heritage League of the 2nd Air Division (USAAF)

                                • 13. Re: 1st time FM12 Adv user trying to create a WW2 Bomb group database
                                  GuyStevens

                                       Hi Mike, nice to hear you are back on track.

                                       Good luck with your further developments.

                                       Greetings

                                       Guy