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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
"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
|
|||
|
|||
"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
|
|||
|
|||
"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
|
|||
|
|||
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 | |
|
|
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 |