sorting on two fields together

[Kazlou]

I have to put a 'responsible area' field into a database. There are 10
records in the table I have linked it to (eg Fred, Mary, Jane etc). The
responsible area for any record could be one or two of the possible ten.
Examples of some of the possibilities are - just Fred, just Mary or Mary &
Jane.
Hope this is making sense so far!

I then need to sort out all records for each responsible area eg everything
that Fred is responsible for.
If I set up fields of 'responsible area 1' and 'responsible area 2' and link
them both to the 'responsible area' table, that allows me to record one or
two areas but how do I then do a query and finally a report for each
'responsible area'

Is there a way of getting Access to sort both at the same time and pull out
the information needed.

Thanks in advance for help.
karen

 

[Dale Fye]

Don't setup fields for ResponsibleArea1 and ResponsibleArea2. What you need
is another table (tbl_RespAreas) that contains a field that will link it to
your main table (this is usually the same as the ID field in the main table),
and a second field Resp_Emp]. using this technique, you will be able to add
as many people to the responsible area as is needed, not just the 2 mentioned
above. The best way to enter data into a configuration that is setup this
way is to use a subform to enter the names of the people that are responsible
for a particular area.

The, to write a query that gives you all of the individuals, and their
responsible areas, you might write a query that looks like:

SELECT tbl_RespArea.Resp_Emp, yourMainTable.*
FROM yourMainTable
INNER JOIN tbl_RespAreas
ON yourMainTable.ID = tbl_RespAreas.ID
ORDER BY tbl_RespArea.Resp_Emp

HTH
Dale