Unanswered: lookup multiple records from a table in a form
Hi, I have a problem with a form I am trying to create in MS Access.
I have multiple tables. In particular, the problem relates to two: one called 'tbl_event_details' which contains details about events and one called 'tbl_organisations' which contains details of organisations who are running events.
In the 'event details' table, it is possible to select multiple organisations who are running each event from the 'tbl_organisations' table. The field that controls this lookup is called 'fld_ent_organisation'. This all works fine. The problem occurs in the form.
In the form, I want to have a text box which shows me the full details of each organisation selected using the above operator. So if I select that organisation X and organisation Y are running an event, I want to be able to display on the form the name, ID, type and other attributes of each organisation selected.
I could do this for one organisation using a DLookUp command, but this does not work when I allow multiple organisations to be selected for each event.
In the Relationships screen, make a one-to-many relation between the two tables, with the organization table on the "one" side. The related field, of course, is the org's client number, name, or whatever is unique about the client.
Once you save it - you have to index it first, and you may have to fix some records because of dupes, missing info, etc. - make a subform with the same common field. Put the subform somewhere in the lower portion of the form, in the detail section, and have it display what you want.
After all is said and done, I'm not convinced that Access will allow you to show the details of multiple selections even in such a setup...
Thanks for replying, Sam, I shall give this a go tomorrow.
I'm still designing the database at the moment (it replaces one I made last year as I went along and which is therefore not normalised at all - it is mainly based upon one unwieldy table) which means it is still pretty simple to change things without worrying about duplicate data (there isn't any yet, and no events will take place before October, so I have some time).
If I wanted to be able to do things like search which events are hosted by which organisation, as well as the other way around, would I need to use a junction table?
I am pretty new to this as you may have guessed(!)
All you need to report on anything is a simple query, and it can contain both tables easily. As long as your relationships are intact - and you must keep them so! - you can report on anything with the greatest of ease. Even things you can't display in the form, you can display easily in a query or report.