Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2002
    Posts
    139

    Unanswered: Update many-to-many relation in matrix form?

    Hi all,

    Developing a system for an analytical laboratory, I have 2 tables:
    1) A set of analyses the client has preselected, f.e.: Cr, Pb, H2S04, pH, etc;
    2) A set of samples from the client, f.e.: Amsterdam1, Amsterdam2, Rotterdam2, Rotterdam2c, etc;

    The client needs to choose which analysis will be done on which sample.
    A many-to-many relation is present, so I have an intermediate tabel joining AnalysisID with SampleID and a boolean to select.

    Through a cross-tab query I can view this intermediate table the way I need:
    SampleID-------------Cr-------Pb------H2S04 -------pH-----etc.
    Amsterdam1----------X---------O----------X-----------X
    Amsterdam2----------X---------O----------0-----------X
    Rotterdam2-----------X---------O----------0-----------X
    Rotterdam2c----------X---------O----------X-----------X

    The list of analyses is preselected from a long list (>100 items), and may vary in length from only 1 (just pH) to all 100+.
    Also the number of samples brought in by the client may vary from just 1 jar to 100 samples for the same lab-order.

    My question is:
    How do I present a view like above, allowing update of the boolean fields, and with a flexible number of elements both X and Y-ways?
    My cross tab query does not allow the update.

    Up to now I normally updated these kind of relation row by row, but this time I really need a matrix like update.
    I feel that this is a general input topic, since many-to-many's are all around us.

    Hope to receive new insights from you...

  2. #2
    Join Date
    Apr 2003
    Location
    Evansville, Indiana
    Posts
    76

    Lightbulb

    I believe I know what you are wanting to do, but I need some more info to visualize it better...

    Can you provide a sample of the tables that are in the many-to-many relationship as well as a sample of the table serving as the junction (intermediate) table. Also, I'd need to see a sample of the table you are wanting to update as well as how you want to update it.

    You are right, you cannot update any fields directly returned by the cross join since the result set is an aggregate. An idea is to return a result set that looks like what is returned by a cross join, but actually it will be a regular table. Then in a form, maybe you would double-click a cell, capture the intersection meta-data (column and row heading info) and then programmatically do the UPDATE query. I'll be able to elaborate when I see more details...

    Kael
    Last edited by kaeldowdy; 04-05-03 at 18:30.

  3. #3
    Join Date
    Apr 2002
    Posts
    139
    Dear Kael,

    "Can you provide a sample of the tables":

    TblAnalysis:
    AnalysisID---------AnalysisName------------AnalysisOtherFields
    AutoNumber---------Text-------------------Whatever I Need else
    -----1---------------Cr-----------------------Metallic
    -----2---------------Pb-----------------------Metallic
    -----3--------------H2SO4-------------------Anorganic
    -----4---------------pH-----------------------General

    TblSamples:
    SampleID---------SampleNr--------------SampleOtherFields
    AutoNumber---------Text------------------Whatever I Need else
    ----11-------------Amsterdam1------------Cold
    ----12-------------Amsterdam2------------Frozen
    ----13-------------Rotterdam2-------------Warm
    ----14-------------Rotterdam2c------------Hot

    Intermediate Table (normal, updateable record-by-record layout):
    AnalysisID--------SampleID-----------Selected
    -----1-----------------11-----------------Y
    -----1-----------------12-----------------Y
    -----1-----------------13-----------------N
    -----1-----------------14-----------------Y
    -----2-----------------11-----------------Y
    -----2-----------------12-----------------Y
    -----2-----------------13-----------------Y
    -----2-----------------14-----------------Y
    -----3-----------------11-----------------Y
    -----3-----------------12-----------------Y
    -----3-----------------13-----------------N
    -----3-----------------14-----------------Y
    -----4-----------------11-----------------Y
    -----4-----------------12-----------------Y
    -----4-----------------13-----------------Y
    -----4-----------------14-----------------Y

    Desired presentation of intermediate table (updateable):
    AnalysisID------------SampleID----------------
    -----------------11-----12-----13-----14--
    -----1-----------Y-------Y------N------Y---
    -----2-----------Y-------Y------Y------Y---
    -----3-----------Y-------Y------N------Y---
    -----4-----------Y-------Y------Y------Y---

    Hope this clearifies my needs a bit.

    Marion

  4. #4
    Join Date
    Apr 2002
    Posts
    139
    For record purposes, I have found the trick I needed:

    => With every new lab-order, I fill two tables with new records;
    => Table 1 is the normalised intermediate table in the many-to-many relationship;
    => Table 2 is my Analysis-table, expanded with 100 yes/no Sample fields;
    => The user-form is based on Table 2;
    => Any updates in the user form (=Table 2) result in an Update of Table 1, thus keeping the normalized Table 1 in good shape;
    => Any sample field that is not needed in the user form is hidden on Form Open, thus creating the interface the user expects based on his input for number of samples and analysis.

    See attached screen shots for an idea.
    Attached Thumbnails Attached Thumbnails 13 analysis x 18 samples.bmp  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •