"RudyR_Seattle" wrote in message
...
I had a project where I had to input a large amount of redundant info using
temp help. For instance, I need to say a a particular type of Media was CD
or
floppy. Another field required origin and so on. The fastest way we could
do
this was to Highlight the cells on a Excel datasheet and do a fill down
command. Is it possible to do this with Access? I came up with a solution
by
importing these spreadsheets, but I would like a more elegant solution and
eliminate the spreadsheets.

The first issue is that to use the spreadsheet paradigm to a database is a
mistake. The more "elegant" solution of course is to normalize the data. If
you don't approach this problem by normalizing data, then you get a database
that behaves like a spreadsheet. This means you waste precious developer
time, you loose all kinds of flexibility in building reports, and you have
all kinds of data duplicated over and over. This makes working with the data
very painful, and reduces flexibility. The key concept here is to not try
and automate the ability to repeat data, but in fact to eliminate the
repeating data!.

A hall mark of a good data design in a relational database is to eliminate
repeating data. For example, I might have a customer, and then have customer
payments made. So, if I have to enter 15 payments, then you could like ask
how do I repeat the customer name, phone number, address, customer number,
payment date, and amount. Right away, this approach is a warming bell, as
you need to enter repeating data over and over. There should be only ONE
customer record, and then we relate the payment date, and payment amount to
the customer.

So, the solution would be to build a customer table, and then build a
customer payments table. We could build a form that lets you search and find
a customer, and then use a sub-form to enter the payment date, and payment
amount. This is a far better approach, note that we have:

We get ONE customer record that we just need to search for.
When we finally find the one customer record, then we instantly can see
all the payments made in the sub-form.
If we have to add more payments, then we do NOT have to repeat, or
re-type, or re-copy ANY of the customer information..but simply go right
into the sub-form and enter a payment date, and payment amount (assuming
here that our sub-form shows payment date, and payment amount).


So, you can see now how we have gone from asking how to repeat all kinds of
customer information to a situation where we find ONE customer, and then
enter some information that BELONGS to that customer. Further, if I now have
to change the customer name, then all of the related data now has the new
customer name..since in fact the customer information is not copied over and
over. And, if I add a new field like a phone number

So, don't try and use the concepts of a spreadsheet for a database.

So, that "group" of repeating data that you are trying to auto copy should
in fact likely be put into ONE record, and then just the additional data
that actually changes need only to be entered, and no duplication of data
occurs. You can rapidly see also that no duplication of effort also occurs.
Even when you CAN automate the duplication of data, you don't want to do
this, as it reduces reporting flexibility.

In most cases the "process" or "procedure" for the end user makes more sense
also. You find ONE customer, and the UI lets you click on a button, or do
whatever to add information to that customer.

Of course, the only real draw back of normalizing your data is that it take
more design work up front. And, in fact setting up a system with normalized
data can be harder to implement, but results in much more flexibility.

So, my first suggestion is to look at normalizing and eliminate the actually
"need" to copy the data over and over. However, likely like all of us, we
are busy..and you do things under lack of time like all of us. The #1 rule
is just to get your work done!

Hence, you can write some code to "copy" the data for you, but I don't think
the efforts are worth it, and you might be just best off using Excel until
you can eliminate the need to repeat the data. I hope the above inspires you
a bit about the basic difference between a database and a spreadsheet.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn