Multiple foreign key in table

[pmstirling]

Hi -

I'm designing a DB for a conference. Only 2 tables (so far): "Participants" and "Round Tables".

Participants is a list of everyone registered, with personal info and a registration type - "delegate", "moderator", "speaker", etc. The main key is "ParticipantID"

Round Tables is a list of 14 Round Tables, each of which has a Moderator and 4 Speakers.

The obvious (to me at least) way to have this is to use the ParticipantIDs of the moderator and speakers in the Round Tables table. I've tried both a simple number and a combo box via the lookup wizard. The combo box works, but when I create the report it does not show the data I want. I set the combo box to show last name, first name, title, of coordinators only for the coordinator field, or speakers only for those fields.

When I create the Round Table report, I just get the last name (ie the first field of the combo box). I want "[last name] & ", " & [first name] & " " & [title].

any suggestions on how to do this? I haven't been able to get a join to work with more than one foreign key per row.

cheers
patrick

[RuralGuy]

I suspect you are just creating a report from your form. Try creating the report with the Report wizard instead.

[pmstirling]

I suspect you are just creating a report from your form. Try creating the report with the Report wizard instead.

I've tried the Report Wizard several times! It shows just the Last Name field from the other table, although the data source is a select statement getting all 3 fields:

SELECT Coordinators.[Last Name], Coordinators.[First Name], Coordinators.Title, Coordinators.[Participant ID] FROM Coordinators ORDER BY [Last Name];

- how do I get it to show all of the fields on the report?
patrick

[RuralGuy]

Do you have all of the fields defined on your report? When you look at the RecordSource of the Report in Datasheet view, are your fields there?

[pmstirling]

Do you have all of the fields defined on your report? When you look at the RecordSource of the Report in Datasheet view, are your fields there?

Thanks for the help! The RecodrSource of the Report is a Table. When I look at that in datasheet view, it shows only the [Last Name] field - I guess this explains why that's all that shows on the report.

When I look at the Table in Design View, that field is of datatype 'Number', Display Control is combo box. Row Source is a SELECT statement that gets the [Last Name], [First Name], [Title], and [ParticipantID] (key) from the other table. Bound Column is 4, which I believe means that ParticipantID is stored in this table.

So I guess my question is, how do I access the combo box fields from a report? Or more generally, if I store a foreign key in a table, when generating a report from that table how do I retrieve arbitrary fields of the record in the other table? Further, how can I then format them neatly?

cheers
patrick

[RuralGuy]

You should really have queries as the RecordSource of Forms and Reports for a number of reasons. Create a Query that joins the tables you need and then select the fields you want. If you have the relationship defined in the Relationship form then simply adding the other table to the query will show the relationship join.

[pmstirling]

You should really have queries as the RecordSource of Forms and Reports for a number of reasons.

Sorry, I'm missing something here! It's just not working the way I want. One Table, "RT" contains the keys of 7 records in the 2nd Table, "Participants". I've created the relationships, so when I have both tables in a query, the 7 foreign key fields on "RT" all have lines to the "ID" field of the Participants table.

I want the report to show the fields in the RT table, except that for each field thats a key into the Participants table I want the first & last name fields to show . That is, each line of the report should show the name field of the RT record followed by 7 "last name,first name" pairs from the Participants table.

I found that with a single foreign key it works fine, but with multiple keys it fails.
Any ideas?
patrick

[RuralGuy]

You lost me a bit: 7 keys in the RT table? Is this in the same record? Maybe a brief description of the relevant field in each table would be useful.

[pmstirling]

You lost me a bit: 7 keys in the RT table? Is this in the same record? Maybe a brief description of the relevant field in each table would be useful.

Sure:

Participants table - relevant fields are ParticipantID (the key), First Name, Last Name, Title

Round Tables table - fields are ID (key), Name, Coordinator1, Coordinator2, Reporter, Speaker1, Speaker2, Speaker3, Speaker4. ID is numeric, Name is text, the other 7 are numeric and hold a ParticipantID from the Participants table.

I used the lookup wizard in the table design view to show a list of Participants to select the Coordinators etc -- this works fine.

I want to create a report that lists all round tables with the names of the coordinators, reporter & speakers. Preferably these names would be formatted to look like "lastname, firstname, title".

hope this clarifies! I'm at the point of giving up and just using text fields and typing the names directly into the Round Tables table.
patrick

[RuralGuy]

That is what I was afraid of. You have a normalization problem to deal with. You may want to revisit the design. BTW, most developers avoid LookupFields like the plague.
http://www.mvps.org/access/lookupfields.htm

[pmstirling]

That is what I was afraid of. You have a normalization problem to deal with.

OK. Well, thanks for all your help!
patrick