repetitive data entry form design

[dave]

Hello,

I'm trying to create a form (if possible) that allows the user to perform
series of data entry - max of about 50 items at once. (I've posted a question
on this but I guess I didn't explain well enough what my problems are and how
the db is set up right now so I'm trying again, sorry for the repost).

The way the tables are set up is;
I have a main Project table that has a unique autonumber field for all
project items, as well as project name, engineer ID, impacted regions ID,
switch ID, due dates.

The engineer ID, region ID, switch ID are FK from the Engineer(has list of
13 engineers), Region (4 regions), Switch (list of about 50 swithces) tables.
Region and Switch table are also linked together, to determine which switches
belong to which region.

Currently, the user is able to enter data in the Project table using a
"create a project" form that has all of those fields listed above - and if
the user creates a project it means 1 unique project that impacts 1 region, 1
switch, 1 due date and 1 assigned engineer
///////////////////////////////////////////////////////////////////////////////////////
The problem is that sometimes there are nationwide projects and it involves
all/some of the switches, engineers, and possibly with different due dates.
For instance:

Project: Example Project XYZ (Nation Wide)
Impacted Region:
SW-Due date 10/24
PNW-Due date 11/2
South Cali-Due Date 11/4
All Switches, All Engineers

SW region has, say, 15 switches. But I cannot just put "All SW region
switches" and "multiple engineers assigned" in the switch ID and Engineer ID
fields - It needs to have 15 single project items under the same project name
"Example Project XYZ", for each switch in the region because the user wants
to be able to check the progress for each switch as time goes on, not as in
region-wide. And if you want to put 15 single project items with the same
project name, then it means (currently) you have to open up the "create a
project" form 15 times and manually copy paste most of the overlapping
information of the project. It gets worse if you get 50 switches.
I was wondering if there is a way to facilitate this process. Is there a way
to parse multiple arguments from the list and auto populate a continuous form
or something? This was the only thing I could think of. Oh, also multiple
instance forms? but I'm not sure if it would be a good thing since opening 50
forms would slow things down (it's already a little bit slow because the db
resides in a remote network server).

I hope the description of the problem is clearer than the last time,
and I really thank you for your time and help.

Dave

 

[tina]

sounds like your problem is actually the tables design:

if one project may impact many regions, and one region may be impacted by
many projects, that's a many-to-many relationship. you need to move the
regions data into a child table, using the project primary key as a foreign
key in the child table AND using the region pk as a fk in the child table
also. name the table something like tblProjectRegions. include fields in the
projects table that *only* describe the project as a whole, such as the
project name. include fields in tblProjectRegions that *only* describe each
project region as a whole, such as a due date. with this setup, you can
assign as many regions as needed to each project.

if one regional project may involve many switches, and one switch may be
involved in many regional projects, that is another many-to-many
relationship. again, you need a child table, linked to tblProjectRegions by
its' primary key acting as a foreign key. the name might be
tblProjectRegionSwitches (or an abbreviated version, if you don't like long
object names). and again, include fields in this table that *only* describe
a specific switch in a specific region involved in a specific project.

your post wasn't clear on how engineers are involved in a project. if
engineers are assigned to switches in a project region, then analyze that
relationship to see if you need a child table linked to
tblProjectRegionSwitches, or simply a field in that table. if instead
engineers are assigned to regions, then you'll look at tblProjectRegions to
determine, again, whether a field in that table or a child table is needed.
but if engineers are assigned to projects as a whole, then you'll need a
child table related to tblProjects.

suggest you read up, or read more, on relational design principles. once the
tables are set up correctly, you can create data entry forms to enter the
data without the (non-normalized) repetition that caused problems in the
previous "incarnation" of your database. for some good normalization links,
see
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101.

hth

 

[dave]

hm, I didn't see it as a table dsgn problem till now but you helped me see a
bigger picture. Thank you!
Well, it's not that I don't try to normalize the db.. my supervisor never
informed me that there were going to be nation wide projects and now he wants
the db to support this.
Anyways, I DO understand what you are trying to tell me, so that's good...
I'm gonna go try normalizing-

Thanks again.

dave

 

[tina]

well, it's never fun to be blind-sided by undeclared information about the
process, and no matter how diligent you are in your process analysis, it can
happen. sounds like it's early enough in development that restructuring is a
viable option, though. good luck with it!