same criteia over three fields

[tigerstripes]

I have a database which records project details such as location, date, size etc and also the service area working on the project. This can be up to three different area on each project.

I have three tables with the same area names in with three flds on my main form linked to them.

I need to run a report which shows all projects worked on by a service area, whether recorded as service area 1, 2, or 3

Is there a way of creating a query which will search for one criteria from three different fields so I can run the report from it.

so my main form looks like this:
project ref - service area 1 - SA2 - SA3
1 - P&R - SS - PI
2 - PI - P&R - SSK
3 - ES - SSK -NA

And I need my query / report to show:

SA - Project Ref
P&R - 1,2
SS - 1
PI - 1,2
SSK - 2,3
ES - 3

Hope that makes sense.

 

[Guus2005]

... and leopard spots...

Use a crosstab query to transpose cols and rows.