Combining field results

[domino3]

I have a table with fields showing when an event is going to be attended. Each delegate may be attending on either or both days, and may be attneding seminars in one of two rooms on either day with several seminars in each room. I have set up a table with a delegate name field, a session one date, a session one room, a session two date and a session 2 room.

This then produces a query which shows the delegate, and several columns showing each of the days and rooms for that delegate.

Is there any way of producing one long list of any one room on any one day and which delegate will be attending that session.

I've tried concatenating the fields, but that just combines the fields, rather tham producing a list showing that a delegate may be in the same room twice on one day.

Thank you.

 

[ZmrAbdulla]

You need to show the table set-up. If it is only one table then you need to split into four for normalization.
[ul]
[li]TableSeminars[/li]
[ul]
[li] Seminar Date [/li]
[li] Seminar ID [/li]
[li] Room ID [/li]
[li] Information 1 [/li]
[/ul]
[li] TableRooms[/li]
[ul]
[li] Room ID [/li]
[li] Information 1 [/li]
[li] Information 2 [/li]
[/ul]
[li]TableDelegates[/li]
[ul]
[li] Delegate ID [/li]
[li] Delegate Name [/li]
[li] information 1 [/li]
[/ul]
[li] TableSeminarDelegates [/li]
[ul]
[li] Seminar ID [/li]
[li] Delegate ID [/li]
[li] Information 1[/li]
[/ul]
[/ul]
Then you can create a query pulling all the required informations from all the tables.
Hope this helps

________________________________________
Zameer Abdulla
Visit Me
Minds are like parachutes. They only function when they are open. -Sir James Dewar (1877-1925)

 

[domino3]

I've tried splitting the table into two, just to see how it would work. I now have the rooms on one table and the names on another. However, when I try querying the two tables in one query I still only get the names appearing once, rather than once each for every time they are attending.

For example, is Mr Smith was going to both rooms, I still only one row on the query for Mr Smith rather than Mr Smith Room 1 on one row and Mr Smith Room 2 on a separarte row.

I've tried all three join property options, and none of them give the result I need.

 

[ZmrAbdulla]

I am moving from the office now, will come back tomorrow to this thread if you didn't get the desired result. Meanwhile you try to create the tables as I mapped. And try to create a query.

________________________________________
Zameer Abdulla
Visit Me
Minds are like parachutes. They only function when they are open. -Sir James Dewar (1877-1925)

 

[ZmrAbdulla]

Here is the table setup I have created for testing.
[ul]
[li] tblDelegates [/li]
[ul]
[li] DelegateID(AutoNumber) [/li]
[li] DelegateName(text) [/li]
[li] DeleInfo1(text) [/li]
[/ul]
[li] tblRooms [/li]
[ul]
[li] RoomID (AutoNumber)[/li]
[li] RoomName (text)[/li]
[li] RmInfo1 (text)[/li]
[/ul]
[li] tblSeminars [/li]
[ul]
[li] SeminarID (AutoNumber) [/li]
[li] SeminarDate (Date/Time)[/li]
[li] SeminarName (Text) [/li]
[li] SemRoomID (Number)Lookup tblRooms RoomID[/li]
[li] SemInfo1 (Text) [/li]
[/ul]
[li] tblSeminarDelegates[/li]
[ul]
[li] SemDele (AutoNumber) [/li]
[li] SDSeminarID(Number) Lookup tblSeminars SeminarID[/li]
[li] SDDelegateID Lookup tblDelegates DelegateID[/li]
[li] SDInfo1(text) [/li]
[/ul]
[/ul]
Here is the query that pulls all the records. You can filter it by any type (Rooms, Delegate,Seminars,Date)

SELECT tblDelegates.DelegateID, tblDelegates.DelegateName, tblRooms.RoomID, tblSeminars.SeminarID, tblSeminars.SeminarDate, tblSeminars.SeminarName
FROM (tblRooms INNER JOIN tblSeminars ON tblRooms.RoomID = tblSeminars.SemRoomID) INNER JOIN (tblDelegates INNER JOIN tblSeminarDelegates ON tblDelegates.DelegateID = tblSeminarDelegates.SDDelegateID) ON tblSeminars.SeminarID = tblSeminarDelegates.SDSeminarID;

Hope this helps

________________________________________
Zameer Abdulla
Visit Me
Minds are like parachutes. They only function when they are open. -Sir James Dewar (1877-1925)