8 Replies Latest reply on May 23, 2011 8:46 AM by philmodjunk

    "complex" query question / call for assistance

    JCMendler

      Title

      "complex" query question / call for assistance

      Post

      long term user of fmp, comfortable with relational db's, but no formal computer edu; still using fp7...

      A) I hope I can clearly explain my issue, B) I hope someone has a few minutes to help out... ;-)

      I am trying to figure out how to set up a data retrieval issue on a very large database (government issued nutritional info). All relationships set as instructed, data imported from the issued ASCII files, no problem. Simple (one match field) look ups working fine. BUT, I am having trouble pulling data from a large table (almost 500,000 records) that actually has two required fields for the look up to match; the problem is that I can only capture the first instance of the match. I do not seem to be able to figure out how to define a second field to pull from the next potential match. That is, I need the data from field "C" after it matches from fields "A" and "B" - a given value for A will have multiple records with unique B's and therefore C's. I am having trouble trying to pull that "next" record with the new"B" instance to get the new "C" data point into a new table.

      Field "A" represents a food item, field "B" represents a nutrient, and field "C" the value for that nutrient for that food. For each of almost 7000 food items (A) there are about 130 nutrients (B). I am trying to establish a "report" that would pull all the nutrient values (C) for each nutrient (B) when you plug in a single food item value (A). For each food item "A" there will be several (130) potential field "B's" (each with a unique value, 1-130) in the bulk data table (each A,B,C is a unique record in the source table), that is then associated with the result C. 

      I tried to do this directly by starting from the food item table A (only one instance of each value for A, but not with any of the potential B's and C's as part of that table) but I could only pull the first instance of C from the first B when I tried to place a related field (matched by A and B) in the layout. Then I tried to define a new table, with a unique field for each nutrient value (the "C" from the bulk dataset) by matching the food item (A) and the nutrient designation (B). But I still only get the first instance of B and thus C for a given A; I can not seem to get a new field in the same record of the new table to pull from the a different record with the same A value but a new B value to pull a new C data point from the bulk dataset. I then tried to use a portal and WAS able to get the full list of unique data points for C, BUT the only formatting option I could figure out was a scrolling list of portal rows which was NOT very helpful because I needed a second portal to pull the nutrient names and could not directly link the two portals onto one scroll, so I ended up with two independent scrolls - made associating the value with the label pretty much a guess - again there are 130 potential nutrients per food item. 

      So, my basic question is... Do I need to better configure / format the portal? Figure out an improved way to define the relationship? Or, in my simplistic view, do I need to go and define 130 new tables for each of the nutrients (B) to pull individually based on the food item A to et the nutrient value C? That just seems dumb and shows an apparent lack of db sophistication - I know it would work, just seems to defeat the point of streamlined data and minimal redundancy. 

      Any advice is appreciated. (also, kudos if you understood my question on the first read... ;-)

      Thx

      JC

        • 1. Re: "complex" query question / call for assistance
          LaRetta_1

          Nah, JC, I didn't understand it on the first read, nor the second.  You did an excellent job of explaining it except for one thing ... I see no table names and match fields indicated.  I also see no explanation of which of the tables A, B or C reside in.

          If you are attempting to produce a report from the one side to show the many then it will never work.  You must switch to the side with the many (nutrient values?).  But I cannot say more without seeing a screen shot of that portion of your relational graph which show what is connected, how, and the names of the table occurrences.

          "I can not seem to get a new field in the same record of the new table to pull from the a different record with the same A value but a new B value to pull a new C data point from the bulk dataset."

          You may also be asking for a self-join but certainly don't define 130 new tables for each of the nutrients (B).  Ideally, create a simple file showing the relationship and and a few records from each table so we can understand how the tables relate and what resides in the fields.  Upload the sample file somewhere and provide a link here. Smile

          • 2. Re: "complex" query question / call for assistance
            JCMendler

            Sorry. In a misguided attempt to avoid bogging down in detail I was way too vague. 

            The data set includes data for 10 tables and gives the relationships to set up, which has been done. The "central" table is called FoodDescription and the first field is called NBD_No. This field is the match field for several other tables, including one called NutrientData. In the NutrientData table is another field, Nutr_No. There are about 130 individual nutrients included for each of about 7000 foods. The FoodDescription table has the 7000 unique food records, each with it's own NBD_No to uniquely identify the food. There is another table (Nutrient Definition) that defines each of the 130 nutrients in question, and this links to the NutrientData table via the Nutr_No field. 

            Now, in the NutrientData table there are approximately 500,000 records, each with a unique combination of NBD_No and a Nutr_No that has a numeric value for the nutrient amount found in that food (field name Nutr_Val). (In my original "explanation" A = NDB_No, B = Nutr_No and C was the unique Nutr_Val for a given combination of A and B). (Oh, not every food item has a value for each nutrient, that is why the math does not work out...)

            What I am trying to do is create a report for each food (each unique NBD_No) with each nutrient and its value (why they just did not create such a table in the first place is one of those great scientific mysteries... ;-) by either adding nutrient fields to the FoodDescription table or creating a new table whereby each record holds all the nutrient info for a single food. Where I am having an utter brain freeze is properly pulling a single data point in a repeating fashion (the Nutr_Val field) from each of several records in the Nutrient_Data table into a single new record in the new table. I was originally trying to define the link to the new table via a two field match using both the NBD_No and the Nutr_No as this uniquely defines the "instance" of a given nutrient for a given food. But I was getting the Nutr_Val for the first instance of NDB_No and Nutr_No match for each subsequent field (a different field for each nutrient of the given food). It will properly pull each unique Nutrient Value into a portal with multiple rows (the parent view was the FoodDescription and I pulled the field Nutr_Val via the NBD_No relationship), but such a format is unfortunately pretty useless. Instead, I want to place each value into a unique field, not just an instance on a scrolling list of portal rows. 

            In essence, I need to pull from the same field (Nutr_Val or Nutrient Value) of several related records (related by the same NBD_No, but each with a different Nutr_No) in the NutrientData table into unique fields (one for each nutrient) of a new table. Each Nutr_Val data point will be different, and its nutrient name (say, iron, or fat) is defined by it's unique Nutr_No. Then, for the next record in the new table, I will need to pull a new set of Nutr_Val data points from a new group of related records with a new NBD_No, but the same list of Nutr_No. (almost like I want to first do a "Find" based on the NBD_No and then relate the tables via the Nutr_No instead, but only on the found set - that's what I need, but I don't think that is kosher...)

            (Oh, by the way, the other 7 tables of data are easily ignored for this issue. They relate to footnotes, data source comments, food group definitions and such.)

            The authors of the data presume folks are going to dump the info in an MS Access DB, so I am not sure if the relationships work differently in that program...

            Anyway, if anyone can show me how to do this, I would appreciate it. (and if the answer is ridiculously simple, I can accept the truth that my brain is aging... ;-)

            Thx

            • 3. Re: "complex" query question / call for assistance
              philmodjunk

              FoodDescription------<NutrientData>------NutrientDefinition

              FoodDescription::NBD_NO = NutrientData::NBD_NO
              NutrientDefinition::Nutr_No = NutrientData::Nutr_No

              If I interepreted everything correctly, that's the relationships you should have between these three tables. If so, it looks like you can create a summary report based on the NutrientData table with fields from FoodDescription added to either the header (If you only want this for one food at a time) or a sub summary layout part (If you want this for multiple foods in the same report). If needed, you can also include fields from NutrientDefinition in the body.

              This can produce a report that looks like this:

              Food: 7 oz Steak
                 Protein:  xx grams
                 Fat:        xx grams
                 Carbs:    xx grams

              The first line would be a field or fields from the FoodDescription table and would be placed in the header or a sub summary part "when sorted by NBD_No".

              The subsequent lines would be fields from NutrientData and would be placed in the body. (Example shows three Nutrient Data records all related to the same Food Description record.)

              • 4. Re: "complex" query question / call for assistance
                JCMendler

                Ok, I see that you understand what I am after, but maybe I'm just thick as a brick today.

                My problem is that each of those "xx grams" you used, one for protein, one for fat, one for carbs, they are each in the same field "Nutr_Val" but in a different record in the series. I can't figure out how to get the summary to list anything after the first in the list (in this case, protein). I had tried to use the portal to do this, and sort the Nutr_Val by Nutr_No, the problem I was having with that was twofold: layout is limited (to my understanding) to a scrolling list format, but also as not every food has all Nutrient Values, so the list length and content varies for each food.

                I understand the basic concept of using summary parts gathering totals of a given field, say in a shopping receipt for total cost, but I am having trouble figuring out how to use it to list different values from the same field in a group of related records.

                Is there a tutorial for using a summary like this you can point me to? I don't see (or don't recognize it as such) any examples in the e-documentation that comes "built in" using a summary in this manner, though it would make sense. Also, I can get a scrolling list of different values for that first field of Nutr_Val (actually it is protein... ;-) for different foods (NBD_No) when I switch from Form view to List view, but I can't get different nutrients (Nutr_Val) to come up for a single food (NBD_No). 

                Suffice it to say, I think my skills at using summary parts are a bit weak. Tips on a tutorial?

                Thx again,

                JC

                • 5. Re: "complex" query question / call for assistance
                  LaRetta_1

                  It would really help to see a sample file as I suggested originally which shows the pertinent tables involved in the issue.  You and us will continue to waste a lot of time and effort otherwise trying to understand what you truly have and want.

                  • 6. Re: "complex" query question / call for assistance
                    JCMendler

                    I took a few screen shots of the relations and the field definitions...

                    but...

                    Um, not to sound like an idiot (ok, not any more of an idiot...) but how do I include an image in this post without having it hosted on a server somewhere?

                    When I used the handy little icon above "insert/edit image" it is pasting the file path (ie a text string, not the image) to the screen shot on my desktop, not the actual image. When I tried the old drag and drop, I just got a nice little blue box with a question mark inside. Not very encouraging. There the basic .png images from the built in screen shot key strokes on the Mac, and each is pretty darn small (<100 kb).

                    Sorry, clearly a forum newbie... :-(

                    JC

                    • 7. Re: "complex" query question / call for assistance
                      LaRetta_1

                      Nah, this forum is not very user-friendly.  Register somewhere like 4shared.com.  It is free and very very easy to use.  You can then upload your files there.  After you upload them, it will give you a link.  Paste the link into a new response on this thread and we can grab it from there. :^)

                      • 8. Re: "complex" query question / call for assistance
                        philmodjunk

                        "My problem is that each of those "xx grams" you used, one for protein, one for fat, one for carbs, they are each in the same field "Nutr_Val" but in a different record in the series"

                        That's exactly what you should have. Each value should be in a different related Nutirent Data record linkded to a given FoodDescription record. The key is to base your layout on the correct table. Instead of using a portal to Nutrient Data from a layout based on the FoodDescription table, create a layout based on the Nutrient Data table. Now each nutrient data record can be listed in a list view report to get the results that you want with added fields from FoodDescription serving as the header or sub header for the listed Nutrient Data.