lookup from one table to two fields

[Russyfoot]

Hi all.

I have table; tblBuildings one field of which is Supervisor which looks up a field in tblSupervisor which contains contact details etc.. Then a query gives me a report listing the contact details for each building.Note, one supervisor can be responsible for more than one building. Simple. I've now been asked to provide a secondary contact which will come from the same tblSupervisor. Again, simple. Or so I thought. I can add another lookup field , deputy, but any queries I've tried so far will only give me contact details of either the supervisor or the deputy. Obviously I need both. I've tried searching this forum but haven't found the right keywords yet. Hope you can help.

 

[vbaInet]

In your case I would imagine that it will be best to have another field in the tblBuildings table which would indicate whether it's a Supervisor or a Deputy.

 

[ted.martin]

One other thing that may help is that the Query Join in the QBE grid has 3 different connection formats. If you double-click the join line, you will see what I mean.