Quick advice on table setup

[Beemerang]

Hi All

I need to incorporate this form into a database which I'm creating. I realize that I am in way over my head so any help would really be appreciated!

My aim is to create a user form which will display the to group of fields in the main form, and then all the related activities for this report in a subform.

It seems to make sense to have the top group of fields in one table and the activity fields in another, am I correct? If so, how do I relate the two tables? Do I need a composite primary key and if so, how is this linked to the foreign key in the activities table?

Name of Employee/ Supervisor:	Name of Supervisor
Month of : Year :	Date:

<tbody>
</tbody>

Activity

# Planned

# Realised

# of Institutions

Code Institution

# of beneficiaries

Type of beneficiaries

Comments

<tbody>
</tbody>

I may be heading in totally the wrong direction, so any help would be welcome!

Beem

 

[Rijnsent]

Hi Beem,

it looks like you would need two tables for that, your main table:

ID - Name of Employee - Date - Month - Year
1 - John - 20140124 - 1 -2014
2 - Peter - 20140112 - 1 - 2014

And a subtable:
ID - Activity - Planned - Realised - etc.
1 - Sign contracts - 10 - 5 -
1 - Draft contracts - 2 - 4 -
2 - Count books - 100 - 50 -
2 - Seal envelopes - 200 - 5 -
2 - Drink coffee - 5 - 5 -

If you want to do it in 1 table you get quite some "double"/redundant information, but it's easier if you want to make a pivot table
ID - Employee - Date - Activity - Planned - Realised - etc.
10 - John - 20140124 - Sign contracts - 10 - 5 -
11 - John - 20140124 - Draft contracts - 2 - 4 -
12 - Peter - 20140112 - Count books - 100 - 50 -
13 - Peter - 20140112 -Seal envelopes - 200 - 5 -
14 - Peter - 20140112 -Drink coffee - 5 - 5 -

You will probably need some macros to get it in and out of your form, whatever table structure you choose. Hope this helps?

Cheers,

Koen

 

[Beemerang]

Hi Koen,

Thank you so much for taking the time to write a detailed reply! I'll work through it and I may come back if I need to clarify.

Have a great weekend and thanks!

Beem