One-to-Many???

[TraciAnn]

I know spreadsheets are designed to be linear (or flat) recordsources and a
relational database is needed for this solution; but that isn't practical in
this situation.

I need to design and distribute an Excel workbook that will contain two
types of information, populated by the user and then returned to me to be
imported into a database.

I could use guidance on providing the best solution possible.

The data I will be gathering will be related to Location and Resource.

Some of the data will be pre-populated in the initial distribution but field
agents will be adding records to both types of information as well as
"linking" the two.

For Example:
Location information will contain typical columns such as: LocationID, Name,
Address, etc.

Resource information will contain ResourceID, Name, Address, Phone, Email,
etc.

Locations need to be assigned multiple resources for a variety of tasks -
which, creates a third type of information being gathered - let's call this
"Schedule".

Schedule will contain: LocationID, TaskName, Date, Time, Resource1,
Resource1 Role, Resource2, Resource2 Role, etc.

A Schedule for any given location can have anywhere from two to eight
resources.

A Resource can be given multiple Schedules (Go to more than one location).

Challenges:
1. I need to layout the sheets so the formatting will not be changed. I plan
on doing this through protecting the workbook. (in case this becomes a factor
in the suggested solution).

2. I need to avoid duplicate entries of information. This wont be so much a
problem on Location, but for Resource, agents will inadvertently enter the
same resource twice. To compound the situation, the database to which the
data is imported, uses an unconventional method of creating the ResourceID
(FirstInitial + LastName) which, sometimes, produces duplicates. In this
event, through user intervention, records with duplicate ID's are reviewed to
determine whether the records are duplicate or only the ID's. If only the
ID's are duplicated then sequential numbers are added to the end of the ID.
(e.g. John Smith = jsmith; Jane Smith = jsmith; Duplicate ID's so Jane Smith
= jsmith1 I know...I know...not the best design but I didn't do it.

3. Once I import data provided by a field agent, they often submit changes
(additional copies of the spreadsheet) without any indication of the change.
This requires huge amounts of user intervention to comb through the data to
assure nothing gets duplicated in the import.

Thank you SO MUCH for your help! Please ask whatever questions needed to
fill in the details I left out.

Gratefully!!!

 

[GMorris]

Well, that's part of the beauty of relational
databases, that you DON'T get any data
duplication. There are ways to import XL
data into a real relational database, but as
for avoiding the duplicate entries in the one
sheet, that's something that you can't easily
control with XL. Now, when you talk about
"linking" the two, that would be controlled
by the relational database setup and what
kind of indexes it uses. In a one-to-many
it's best to keep track of things with a sort
of "hidden" key index, which is not easy to
implement in Excel itself, but is standard
using the normal database formats. If you can
find some way of automating the import (like
with code or an Addin maybe), it would be a
lot easier to keep track of, and usually the
software for the database will help with that,
it just all depends on exactly what you are
trying to accomplish! If it's not possible to use
a real relational DB, you might be able to
squeeze by using the database tools that
Office provides like Microsoft Query, I just
haven't had to use it on too many occasions.
A little more info on what you are doing may
help too, like do you have fields already that
could join the data and anything else that may
be pertinent to your situation.