Append question

[Guest]

Hi,

I'm creating a database and have two tables, one for members details (ID is
primary key) and one for session reports (session ID is primary key). The
session report table is set up one member/one session report BUT now we are
doing sessions with multiple members. I'm looking for a way to enter just one
session report but put multiple members on it - without having to retype the
session report for each member. I need to able to do this via a form rather
than in the background. The reason I want all the session reports on one
table is to make reporting easier later. I'm fairly new to access so please
speak slowly! Many thanks

 

[Guest]

What you have is a many-to-many relationship between members and sessions.
This type of relationship is modelled by another table MemberSessions say
which has two foreign key columns Member ID and Session ID say which
reference the primary keys of the other two tables. These two columns form
the composite primary key of the MemberSessions table. Don't use autonumbers
for these columns, just normal long integer numbers. The primary keys of the
Sessions and Members tables can be autonumbers however.

For data entry you'd a form based on the Sessions table and a subform
within it bound to the MemberSessions table. The subform control's
LinkChildFields and its LinkMasterFields properties would be Session ID.

Make the subform a continuous form and put a combo box on it bound to the
Member ID column. Set the RowSource of the combo box so it shows something
meaningful (a Member Name field for instance) like so:

SELECT [Member ID], [Member Name]
FROM [Members]
ORDER BY [Member Name];

Set its BoundColumn property to 1 its ColumnCount to 2 and its ColumnWidths
to 0cm;8cm (or rough equivalent in inches, but the first dimension must be
zero to hide the Member ID column).

To assign members to the parent form's current session is simply a case of
inserting rows in the subform by selecting the relevant names from the combo
box's drop down list.

Ken Sheridan
Stafford, England

 

[Guest]

Hi,

Thank you very much for that. I think I know what you mean and am going to
try it tonight. It actually seems quite straightforward the way you've
explained it although having never used a subform before.

Thanks again.

What you have is a many-to-many relationship between members and sessions.
This type of relationship is modelled by another table MemberSessions say
which has two foreign key columns Member ID and Session ID say which
reference the primary keys of the other two tables. These two columns form
the composite primary key of the MemberSessions table. Don't use autonumbers
for these columns, just normal long integer numbers. The primary keys of the
Sessions and Members tables can be autonumbers however.

For data entry you'd a form based on the Sessions table and a subform
within it bound to the MemberSessions table. The subform control's
LinkChildFields and its LinkMasterFields properties would be Session ID.

Make the subform a continuous form and put a combo box on it bound to the
Member ID column. Set the RowSource of the combo box so it shows something
meaningful (a Member Name field for instance) like so:

SELECT [Member ID], [Member Name]
FROM [Members]
ORDER BY [Member Name];

Set its BoundColumn property to 1 its ColumnCount to 2 and its ColumnWidths
to 0cm;8cm (or rough equivalent in inches, but the first dimension must be
zero to hide the Member ID column).

To assign members to the parent form's current session is simply a case of
inserting rows in the subform by selecting the relevant names from the combo
box's drop down list.

Ken Sheridan
Stafford, England

Hi,

I'm creating a database and have two tables, one for members details (ID is
primary key) and one for session reports (session ID is primary key). The
session report table is set up one member/one session report BUT now we are
doing sessions with multiple members. I'm looking for a way to enter just one
session report but put multiple members on it - without having to retype the
session report for each member. I need to able to do this via a form rather
than in the background. The reason I want all the session reports on one
table is to make reporting easier later. I'm fairly new to access so please
speak slowly! Many thanks