8 Replies Latest reply on Dec 22, 2009 2:10 PM by LeoB

    Relationships -- am I this dumb?



      Relationships -- am I this dumb?


      I had a previous posting where some folks tried to help me sort out some relationship issues, which I thought I had done, but then somehow lost it.


      Maybe if I explain in detail somebody can point out what I'm doing wrong.  I have used Access quite a bit, but primarily for a huge flat-field database.  I'm trialing FM 10 to use for this relational database I'm trying to develop as a number of people recommended it as being much more user friendly.  It also has user access controls built in, which I need and is now gone from Access 2007.


      I have three tables that have data that will not change much, and that data would ONLY be changed by the administrator.  I've called these static tables.  Users will be able to view/print these tables to look up information, but not change them.


      There are six tables that are designed for user input to create and update records for one type of unique activity each, which I've called user tables.  Each of these tables are independent of the others; I can see no cross-reference needs between them.  In those six user tables I want to have fields that use drop-down lists to select one, and only one, option from fields within the three static tables.  This is so that subsequent searches and reports can be done consistently right now.  In the future, I have an idea of pulling information from both static and user tables into reports, but haven't gotten that far yet.  Right now I can't even create a record!


      In those six user tables I have primary keys as well as foreign keys for the three tables with static data.  I have linked the primary keys from the three static tables to their corresponding foreign key value in the six user tables.  From all the information I've found, it seems that's the appropriate way as the static tables have a one to many relationship with the user tables.


      When I go to the field in one of the six user tables needing a look-up (this word seems to confuse things) from one of the static tables, I can see the option in Field/Control Setup to display the related tables.  I select the field that I want and in the layout it shows :: and the name of the appropriate field.  In Layout view, that field is displayed in the box with the :: and the name of the appropriate field.  However, when I try and enter a new record in the user table in Browse mode, tabbing through the entries just skips that field entirely.


      Then I went back to Field/Control Setup in the box "Display values from," then to "Manage Value Lists," created a new value list with an appropriate name, selected the field from the same static table, and all seems fine as I OK out of it.  BUT, when I go back to enter a new record, the same thing happens.  In Browse mode it acts as if the field isn't there at all -- it just bypasses it.  I cannot click into it or tab into it.


      I went into Edit Relationships, which shows the foreign key in the user table linked to the primary key in the static table, and added a link from the specific field in the user table to the specific field in the static table.  As they used to say when I lived in London, no joy.


      I've also tried reversing the primary-foreign key relationships -- no change.


      I've re-read the stuff from The Missing Manual and the tuturials on Lynda.com, but they always seem to be linking multiple tables together.  I think I'm doing what they say, but nothing seems to work.


      Somehow I think it has to do with the relationship link, but I've tried almost all possible permutations and nothing changes.


      Anybody have an idea of where I'm going wrong?


      Thanks for listening/reading all this.



        • 1. Re: Relationships -- am I this dumb?

          First things first. You need to take a look at what table your layout is set to refer to. " :: and the name of the appropriate field" tells me that this field is not from the same table as your layout's table of reference. Thus your layout is referring to a table occurrence in layout setup... (show records from) and your specified field is from either a different table or table occurrence. Since you are attempting to enter data in a record in a related table, the relationship is likely controlling whether or not you can edit the data in that field.


          If you describe the two table occurrences involved and their relationship, we might be able to help you out.


          Many folks get confused over the terms Table and Table Occurrence. To learn more, click the following link:

          Table vs. Table Occurrence (Tutorial)

          • 2. Re: Relationships -- am I this dumb?



            I just printed out the post you pointed me to, and the 16-page paper referred to in that post.


            I'll read those first before bothering everyone.



            • 3. Re: Relationships -- am I this dumb?

              I just read all that material, and identified one basic mistake I was making.  I was trying to enter data from one table into an existing field in another table.  However, when I fixed that, it still didn't work.  So, I created a test file to see if I could make it work from scratch, using the video off of Lynda.com as an example.  I think I followed all the steps -- but it still does not work!


              If anybody has time to read this, let me spell out what I did.


              • Created new file/database

              • Created two tables: Audit and Client

              • In the Client table I created four fields and later populated them with two complete sample test records:

                • ClientIDpk (primary key with auto-serial number and nothing else checked)
                • ClientName
                • ClientStatus
                • ClientLocation

              • In the Audit table I created five fields:

                • AuditName
                • AuditFocus
                • StartDate
                • FinishDate
                • ClientIDfk (foreign key without auto-serial number)

              • In Relationships I linked ClientIDpk in the Client table with ClientIDfk in the Audit table. I see Client as the parent table and Audit as the child table as multiple audits could apply to one client, but no two clients are the same.

              • In Layout view for the Audit table I used the field tool and created a new field and selected ClientLocation it from the Client table. It inserted a field in the layout labeled ClientLocation and inside the data box is ::ClientLocation.  Double clicking in the new data box brought me to Field/Control Setup.  I selected Drop-down List and on the right in "Display data from" it has the Client table and ::ClientLocation highlighted.  I did NOT enter anything in "Display valued from."  Saved the layout and went to Browse and insert new record.

              • The new record field in the Audit table pops up with five lines, including Client Location.  Now, I can enter data fine in the top four fields, but when it comes to Client Location -- I cannot click in the field or enter data.  It's the exact same problem I was having before.  The data entry box has the arrow on the right side, but I cannot click on it.  There are two sample records in that table that should come up to select from.

              • In the Relationships graph there are only two boxes: Audit and Client.  The only link between them is Client:ClientIDpk = Audit:ClientIDfk.  Clicking on the = sign on the single line shows only one entry: Client:ClientIDpk = Audit:ClientIDfk.

              Obviously I'm doing something wrong -- but hours of trying everything I can think of seems to make no difference at all.  This is exactly what was happening in the larger data base I'm trying to create.


              Any help is greatly appreciated.



              • 4. Re: Relationships -- am I this dumb?

                The first 7 points are without a fault - then comes the trouble...


                You must enter a value into the ClientIDfkfield in the Audits table, in order to make the audit related to one of your clients. This can be done by:

                a) defining a value list using values from Clients:: ClientIDpk (also show ClientName);

                b) attaching this value list to the ClientIDfkfield (on a layout of Audits).


                Once you select a client from the list, the ClientLocation field will show the selected client's location. It would be best to make this field non-enterable on this layout, since it shows an attribute of a client, not of the audit.


                If that's not your purpose, then elaborate further.

                • 5. Re: Relationships -- am I this dumb?

                  A foreign key should not be auto entered with serial ## (point 6)


                  My $ 0.02



                  • 6. Re: Relationships -- am I this dumb?

                    In none of the tutorials I've seen or read did they mention that Value Lists had to be created for this direct parent-child relationship, although I had tried them earlier.


                    In the ClientIDfk field in the Audit layout I created a Value List from the Client table with both ClientIDpk and ClientName (which I believe is a mis-type as I am trying to pull in ClientLocation).  No change.  I changed ClientName to ClientLocation.  No change.


                    I tried adding that Value List to the field I am trying to pull in (ClientLocation).  No change.


                    No matter what I do, inputting a new record in Browse mode ignores those fields (ClientLocation and ClientIDfk).


                    (For hbrendel:  I double-checked, and the ClientIDfk in the Audits table was set to not auto-insert a serial number.)


                    FM 10 sees the relationship between the two tables, has no problem inserting a field from the Client table into the Audit table, but then nothing happens.


                    Thanks for the help.



                    • 7. Re: Relationships -- am I this dumb?

                      LeoB wrote:

                      In none of the tutorials I've seen or read did they mention that Value Lists had to be created for this direct parent-child relationship, although I had tried them earlier.

                      You don't need a value list for this to work. You can enter a client's ID into the ClientIDfk field manually: if the Clients table has a client with matching ID, that client's record wil become related - and the ClientLocation field should show their location. The value list is merely an aid to select from existing clients, instead of typing their ID.



                      LeoB wrote:
                      I created a Value List from the Client table with both ClientIDpk and ClientName (which I believe is a mis-type as I am trying to pull in ClientLocation). 

                      No, it's not a mis-type: I assume you would want to select the client by name - otherwise you'd need to remember their ID's.



                      LeoB wrote:

                      No matter what I do, inputting a new record in Browse mode ignores those fields (ClientLocation and ClientIDfk).

                      I am not sure what you mean by that. A newly created audit record has no related client until you select one.

                      • 8. Re: Relationships -- am I this dumb?



                        NOW I see what I was doing wrong!  Instead of doing the Value List from ClientIDfk and then linking it to ClientIDpk and second to ClientLocation -- I created a direct ClientIDpk in the layout and was trying to do it from there.


                        I've got another issue to work on, but at least now I can see the values!