Need help with db structure / relationships

[vangogh228]

Hello all, and thanks for your help.

I am having difficulty getting through the logic of structuring my database for the following situation:

My company works in many different geographies and is going through a data conversion process that can involve as many as 21 sets of tasks. Not all geographies need all the task sets, though.

I would like to build a form that indicates all 21 sets of tasks with date fields for start and finish. I also want to have, for each set of tasks, a "Not Applicable to This Geography" checkbox that, when checked, would make the date fields for that task set grayed out and not usable.

Is this possible... and, more importantly, is there a better way of doing this?

Thanks again for your help.

Tom

 

[Mile-O]

There's a better way.

A task table, a geographies table, and a junction table to simulate what is an evident many-to-many relationship.

Structure's something like this:

tblTasks
TaskID (autonumber, PK)
Task

tblGeographies
GeographyID (autonumber, PK)
Geography

tblGeographiesToTasks
GeographyID (number, CK)
TaskID (number, CK)

PK: Primary Key, CK: Composite Primary Key


Now, in the relationships window join each ID field to its namesake i.e. TaskID to TaskID, enforce referential integrity and allow cascade delete.

You can now create a form/subform combination where the user need only select the relevant tasks per geography. Remember only to use queries when working with forms; and not the tables directly.

 

[vangogh228]

Ok. That seems to work well. Thanks.

Now, I have to allow for iterations of activities, so I need to include a subform for notes and data, which is not a problem. However... let's say a programmer is doing conversion number "123" for the geography "Germany." I'd like to make it so the programmer encounters an initial form that asks for Conversion and Geography, with combo boxes, then he hits a button that takes him to a form that allows him to enter his activity for that conversion-geography combination only. I picture that entry form as having his entries for conversion and geography showing at the top of the form with those fields locked, and a continuous-form subform below, allowing him to enter his information each time.

I can build the forms, but how do I build the queries to make this happen? I'm a pretty smart guy but, sometimes, Access makes me feel SO stupid !!!