I want to have multiple values in one field.

[Guest]

I am setting up a databas that references construction drawings I have a
column for the facility that the drawing pertains too, some drawings pertain
to multiple facilities and therefore when I query all of the construction
done on a particular building I want to be able to list the projects that
have had an impact even on multiple facilities.

 

[Jeff Boyce]

In a relational database (e.g., Access), you really DON'T want to store more
than one fact in one field. Instead, create two tables, one with the
"parent" ("one")information, and a second which holds the "child" (many)
information. The child table includes a field into which the "parent" ID
gets entered, so you (and Access) know which parent the child record belongs
to -- this is called a foreign key.

Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

A typical setup that Jeff is describing would be something like this:

tblFacility
-----------
FacilityID (Primary Key)
FacilityName
Address
etc.....

tblDrawings
--------------
DrawingID (Primary Key)
DrawingName
DrawingType
etc....

tblFacilityDrawings
---------------------
FacilityID (Foreign Key)
DrawingID (Foreign Key)

Now you have table that stores an unlimited number of drawing per facility
that can be readily queried. You can query all drawings that pertain to a
specific facility or you can also query which drawings pertain to multiple
locations.

 

[Guest]

Thank you that makes sense. Kinda like ARCGIS

A typical setup that Jeff is describing would be something like this:

tblFacility
-----------
FacilityID (Primary Key)
FacilityName
Address
etc.....

tblDrawings
--------------
DrawingID (Primary Key)
DrawingName
DrawingType
etc....

tblFacilityDrawings
---------------------
FacilityID (Foreign Key)
DrawingID (Foreign Key)

Now you have table that stores an unlimited number of drawing per facility
that can be readily queried. You can query all drawings that pertain to a
specific facility or you can also query which drawings pertain to multiple
locations.