I don't have a great deal of experience with Access so please excuse
my ignorance.

I have two tables: Asset details and Department details

More than one (up to five) departments may be responsible for one
asset. Some departments are responsible for more than one asset.

I want to be able to querie the data two ways:
1. list an asset and all the departments that are responsible for it
2. list the departments and the various assets they are responsible
for.

I created a third table, linking the asset to the departments
responsible but it's really clunky. To cover assets where more than
one department is involved, I have got
Column 1: Asset Name; Column 2: Department 1; Column 3: Department 2
etc

How do I have multiple 'answers' to the one object? Do I have to set
it up so that the various department names are the column headings and
the assets can have either yes or no?

I'm sorry, this is probably Access 101 but I'm not sure of the
terminology to enable a search of the help sites. Thanks

You're almost there. The idea of the 3rd table is correct, but the fields
are wrong.

Your Asset table will need a primary key such as AssetID (AutoNumber.)
Your Dept table will need a DeptID primary key too.

Your 3rd table (AssetDept) has only these 2 fields:
AssetID Number relates to Asset.AssetID
DeptID Number relates to Dept.DeptID

You interface this with a main form bound to the Asset table, and a subform
bound to the AssetDept table. The subform is in continuous view, so it shows
one department per line. You can use a combo for the DeptID, and the combo
can show the department name instead of the number. So now if there are 3
departments responsible for an asset, you just add 3 *rows* to the subform.

You can also view this the other way around, with departments in the main
form, and the assets they have responsibility for in the subform.

Technically, what you have done here is break a many-to-many relation (one
asset can belong to many depts, and one dept can have many assets) into a
pair of one-to-many relations (in the 3rd table.) We call this table a
junction table, and it's a very common solution. Here's another example:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html

In answer to your final question, you definately do *not* want to use a
bunch of yes/no fields. Here's why:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<mainlandenvironment (AT) yahoo (DOT) com.au> wrote