Access web app union query RRS feed

  • Question

  • I am creating an access web app to help plan plantings in a vegie garden. Part of the app will list companion plants of a particular plant.


    Table: Plants                              Table: Companion Plants

    ID PlantName                              ID     PlantNameID                        CompanionPlant

                                                              (lookup Plants.PlantName)    (lookup Plants.PlantName)

    1   Tomato                                  1       Tomato                               Basil

    2   Basil                                      2       Tomato                               Asparagus

    3   Asparagus                              3       Tomato                               Beets

    4   Beets                                     4       Asparagus                           Basil

    I can only seem to make this relationship only work for one direction.

    eg. when you view the plant tomato you see the companion plants Basil, Asparagus, and Beets.

    However when you view Basil you don't see tomato.


    (Main View)

    Plant Name: Tomato                                              Plant Name: Basil


    Companion Plants:                                                 Companion Plants:

        Basil                                                                      (none)



    Looking forward to your help.

    Thanks in advance

    • Edited by Andrew1a Sunday, September 25, 2016 4:13 AM
    Tuesday, September 20, 2016 5:05 AM

All replies

  • Hi,

    I think the most reasonable structure of the plants table should have two fields. One field stores plants, the other stores companion plants.

    Wednesday, September 21, 2016 7:35 AM
  • Thanks for the suggestion, however, need to be able to relate multiple companion plants.

    Ive edited my questions to make it clearer.


    • Edited by Andrew1a Sunday, September 25, 2016 6:19 AM
    Sunday, September 25, 2016 6:15 AM
  • You on the right track, in that the CompanionPlants table is an 'adjacency list' with two long integer number foreign key columns only, each referencing the (presumably) autonumber primary key of Plants.  But do not use the evil 'lookup field' wizard when assigning the data types to the columns in the CompanionPlants table.  For reasons why see:


    So the two tables would be like this:

    ….PlantID  (PK)

    ….PlantID  (FK)
    ….CompanionPlantID  (FK)

    The primary key of the latter table is a composite one made up of the two foreign key columns.

    You can then return the companionships in both directions by means of a UNION ALL operation:

    SELECT P1.PlantName AS Plant, P2.PlantName AS CompanionPlant
    FROM (CompanionPlants INNER JOIN Plants AS P1
    ON CompanionPlants.PlantID =P1.PlantID)
    INNER JOIN Plants AS P2
    ON CompanionPlants.CompanionPlantID = P2.PlantID;
    SELECT P2.PlantName, P1.PlantName
    FROM (CompanionPlants INNER JOIN Plants AS P1
    ON CompanionPlants.PlantID =P1.PlantID)
    INNER JOIN Plants AS P2
    ON CompanionPlants.CompanionPlantID = P2.PlantID
    ORDER BY Plant;

    A couple of supplementary points:

    1.  Do not use the generic ID as a column name, but a name which describes the entity type of which the column is a key, e.g. PlantID.

    2.  Do not include spaces or other special characters in object names.  Either use CamelCase as above, or represent a space by an underscore character like_this.

    Ken Sheridan, Stafford, England

    Sunday, September 25, 2016 10:24 PM
  • Hi Ken,

    Thanks for the detailed reply. I originally thought a union query would work too, however, could not see how to do this with a web app. I understand the theory behind it but can't see where in access to actually create it.

    Thanks also for the points about the 'lookup field' and other 'gramatical' bits. Have gone back and corrected!


    Monday, September 26, 2016 12:38 AM
  • I don't use Access for browser based databases, so I'm unable to help on that score, but if you cannot use one of the original eight algebraic operations of the database relational model it only serves to confirm my worst fears about so called web databases in Access.  Your only other option would be to double enter each relationship, e.g.

    Tomato        Basil
    Basil           Tomato

    It would be a simple task in a desktop database to automate this so that once the first row had been inserted into the table the second would be inserted by executing an INSERT INTO statement in code, but I can't say how you'd do so in a web database.

    Ken Sheridan, Stafford, England

    Monday, September 26, 2016 11:21 AM
  • Hi,

    How do you create and set the form?

    In your main view, are you selecting the Plant Name by a combobox or the list?

    How do you set "Companion Plants" ? How do you bound this?


    Do you refer to the sub view or Related Items Control?

    Tuesday, September 27, 2016 10:42 AM