A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Rapid input Via datasheet



 
 
Thread Tools Display Modes
  #1  
Old November 18th, 2004, 10:01 PM
RudyR_Seattle
external usenet poster
 
Posts: n/a
Default Rapid input Via datasheet

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.
Ads
  #2  
Old November 19th, 2004, 02:22 AM
Albert D. Kallal
external usenet poster
 
Posts: n/a
Default

"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


  #3  
Old November 19th, 2004, 03:19 AM
RudyR_Seattle
external usenet poster
 
Posts: n/a
Default



"Albert D. Kallal" wrote:

"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


Let me modify my question because the issue again is speed of data entry. This is necessary because this is evidence that I was catalogueing. Is there any way to do a fill down procedure using the datasheet in Access? Believe me, this is the fastest way to enter this type of evidence. Yes I know about normalization and blah blah blah. But thats not what I need. It's hard to explain through this forum what the procedure for this data was but so far a fill down really .... really ... would help. And I couldn't eliminate the need to repeat the data. Especially with temps working on literally hundreds, if not thousands of electronic media to catalogue.

  #4  
Old November 19th, 2004, 07:06 AM
Albert D. Kallal
external usenet poster
 
Posts: n/a
Default

"RudyR_Seattle" wrote in message
news:4EA3C3D8-F5BF-4D1F-B2AF-


Let me modify my question because the issue again is speed of data entry.
This is necessary because this is evidence
that I was catalogueing.


Ok...fair enough. I was just trying to give you some ideas here. Often, if
you can build some type of natural grouping to the data entry process, then
in fact the rate of data entry *INCREASES* with much less typing. Further,
the data is thus more consistent and more able to be reported on, and
cataloged on. And, that normalized data makes it MUCH harder for data entry
people to make mistakes during entry. And, the it becomes much easer to fix
data entry mistakes also *after* the data have been entered (this assumes
this is a important issue for you to check the data). So, I just saying you
do owe yourself a look at the whole process..and just the actual speed of
putting data in via copy is usually not the ideal solution here.
side.

Is there any way to do a fill down procedure using the datasheet in Access?


It is not built in to ms-access. So, you are going to have to code this. It
is not clear if you are editing the tables directly, or you have in fact
built a form, and are using that form in datasheet mode ? I certainly hope
you are using a form, as you then can at least have some type of data
verification going on during data entry..right? (again, this depends on if
you value the data).

You can use cut and paste, ..but that is likely more dangerous then what
Excel
and format paint/copy does. You can also use the ctrl-' (that is hold
down the control key, and hit the ' key). So, the ctrl-' key will copy the
previous field value from the previous record (that you entered). However,
this does not copy the whole record, and again is likely not much use. (the
ctrl-' key also works in excel!!).

Believe me, this is the fastest way to enter this type of evidence. Yes I
know about normalization and blah blah blah.


Well, the fact the sql-server, Oracle, ms-access and all commercial database
systems don't have this ability has to tell you something about the whole IT
industry. What is means that for data entry, the data can't be verified, or
normalized during data entry (this actually slows down data entry, and also
opens up your data to more entry mistakes). However, lets just assume for
the time being that the data you are entering is not very important, and all
you care about is copying and repeating data quickly during the entry
process.

About the only solution I can think here is to write some code. I would use
a form in continues mode, and not datasheet mode. You then can place a
button to "copy" the current detail line you are editing, and you can then
prompt the user for how many copies of the current data line. You could also
hot key it, so, when the user hits ctrl-d, a box pops up asking how many to
copy, you type 8, and then Enter...and the current row would the thus
coped 8 times.. This would also eliminate the need to use the mouse during
data entry anyway. Any good developer will design their forms to allow the
data entry process to be done via keyboard anyway, and forcing users to use
the mouse is a poor design idea for data anyway. So, if we use a hot key,
then you can simply navigate to any line...hit ctl-d...enter the number to
duplicate..and hit enter. With a excel "format/copy" idea you can only grab
the last line.

Of course, a good designed form, and normalized data, then a LOT of popup
etc will occur during data entry to aid the user during typing..and this
actually means less typing, and prevents mistakes as many of the category
fields etc are already entered and pop up during data entry (but, again, I
am starting to diverge for you question ).

So, a continuous form and adding a button (or better yet...a hot key) that
pops up and asks how many lines to copy for the current line seems to be
about the best idea.

The code to do this is quite easy:

dim intCopies as integer
dim strSql as string
intCopies = Val(InputBox("how many records to copy"))
If intCopies 0 Then
Me.Refresh ' force current record to disk before copy
strSql = "insert into yourTable " & _
"(Description,Notes, Field1, Field2, etc, etc) " & _
" select Description, Notes, Field1, Field2, etc, etc" & _
" from yourTable where id = " & Me.ID

For i = 1 To intCopies
CurrentDb.Execute strSql
Next i
Me.Requery
End If

And, perhaps, you might just keep using Excel for data entry. Your data
seems to fit the spreadsheet design here anyway. I would not bow to any
popular pressure that says you can't use Excel for entering this data (Excel
don't do a good job of verify input, but that not your concern in your case,
you just want this stuff put in ASAP). So, every good rule and idea has
exceptions here, and excel might just better for your data entry (you just
import into ms-access when you are done).

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

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





  #5  
Old January 31st, 2005, 01:33 AM
Rick
external usenet poster
 
Posts: n/a
Default

If you could provide the detailed steps to do what you were trying to explan
to Albert ---greatly appreciated.

I have one table that has my contact information in and I wish to build a
new table to house additional remedy tickets and "pull" the related contact
information into my new record of the new table.

ie..my first contact table has address, phone numbers, contact info fields,
etc and my new table will be an entry point for remedy tickets created by
those contacts who have access to the database. I wish to have the new table
enter all the contact information that I predefine on my new table when the
contact enters their name in the first field of the record ---ie, all
predefined contact info will be auto populated on the new record from the
entry of the contact name.

I may not be explaining clearly but my objective is to ease customer input
of data I already have stored in a contact table.

Thank you,
Rick

"Albert D. Kallal" wrote:

"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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
"Input" data sheet Kev General Discussion 0 November 3rd, 2004 12:15 PM
Input Masks mk2 Database Design 1 October 31st, 2004 08:52 PM
Input masks not being displayed jfischer_5809 General Discussion 1 September 8th, 2004 05:41 PM
Validating time-of-day input Phil C General Discussion 1 August 23rd, 2004 11:10 PM
XL2003 Validation Input Msg Problem Phil General Discussion 3 July 28th, 2004 02:43 AM


All times are GMT +1. The time now is 12:20 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.