Access web app union query

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.

EG

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.

eg.

(Main View)

Plant Name: Tomato                                              Plant Name: Basil

(Subview)

Companion Plants:                                                 Companion Plants:

    Basil                                                                      (none)

    Asparagus

    Beets

Looking forward to your help.

Thanks in advance

Answer 1

Hi,

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

Answer 2

Thanks for the suggestion, however, need to be able to relate multiple companion plants.

Ive edited my questions to make it clearer.

Thanks

Answer 3

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:

http://www.mvps.org/access/lookupfields.htm

So the two tables would be like this:

Plants
….PlantID  (PK)
….PlantName

CompanionPlants
….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;
UNION ALL
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

Answer 4

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!

Andrew

Answer 5

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

Answer 6

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?