1 Reply Latest reply on Apr 23, 2015 2:12 PM by philmodjunk

    Merging/linking data from 2 tables

    JacquiProut

      Title

      Merging/linking data from 2 tables

      Post

      I am pushing my luck now with a big challenge. 
      I am trying to merge to seperate databases/tables but I cant fathom how to create the report I need.

      Table A 
      Is generated by a device that records  my blood sugars there are at least 4 readings a day but sometimes more. I export this into excel and then into Filemaker.  I only do this once a month.  

      I test my sugars before every meal, at bedtime and if I feel that my sugars are too low.  The device just records this as a time.  I have (thanks to PhilMod) created a calulation that populates a field with a more meaniful description of Breafast, lunch dinnner, bedtime based on time blocks and Hypo if the sugars read below 4

      Table B
      Much Simpler its just  has 5 fields, 
      ID, Date, Breakfast, Lunch, Dinner.  Each day I record what I have had to eat.

      THE REPORT I NEED
      To show all the data from the device on one day with a row for each meal time but link it to the food database so what I have eaten is shown next to the corresponding data in a list view.  ie 

      These would be the headers, CAPS come from table A (The ones prefxed CALC are the calculation result mentioned above The Lowecase headers are from table B

      DATE - CARBS - SUGARS - INSULIN - CALC BREAKFAST - Breakfast food
      DATE - CARBS - SUGARS - INSULIN - CALC LUNCH -     Lunch food
      DATE - CARBS - SUGARS - INSULIN - CALC DINNER -    Dinner food
      DATE - CARBS - SUGARS - INSULIN - CALC BEDTIME -  
      DATE - CARBS - SUGARS - INSULIN - CALC HYPO* - 
      *only when they happen

      Summary to display a list table of the date, the mealtime when I tested my sugars, the sugar levels, how many carbohydrteas I eat, how much insulin I inject and the specfic food eaten.

      I await ideas

      Thanks

        • 1. Re: Merging/linking data from 2 tables
          philmodjunk

          I would recommend a design change here:

          Table B
          Much Simpler its just  has 5 fields, 
          ID, Date, Breakfast, Lunch, Dinner.  Each day I record what I have had to eat.

          I would split that up so that your meals table has one record for each meal instead of one record for each day. This gives you 

          ID, Date, MealID

          This makes linking both the foods that you log as having eaten and your relevant blood sugar reading to a specific record in meals.

          You might define a new table with this structure and when you are confident that it is working correctly, you can do three Import Records operations to move the data from your current table into the new, but splitting them up by meal. (Import once for Breakfast, once for Lunch, ...)