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
|
|||
|
|||
Importing data from Excel then adding additional data via a form
Hello,
I have a question regarding the modification of data after importing it from a spreadsheet but before adding it to a table. I have created a db that is basically a parts library. In this I bring in bills of materials (BOM), which I add to a table called raw_masterlist. The purpose of the table is to enable me to do a where used search. This might not be the best way to do it but that is what I did. The problem is that I receive the BOM in excel format and then have to add specific data to it which defines where used. (Where used, date, rev, board name, unit name) My question is: Is there a simple way to import the table into an add record form then add the where used data that i need and do an autofill or something before I actually add the records to the table? I don't know if this makes sense to you or not but I don't know exactly how to pose this question. Any help you can give me would be greatly appreciated. nairb |
#2
|
|||
|
|||
nairb wrote:
Hello, I have a question regarding the modification of data after importing it from a spreadsheet but before adding it to a table. I have created a db that is basically a parts library. In this I bring in bills of materials (BOM), which I add to a table called raw_masterlist. The purpose of the table is to enable me to do a where used search. This might not be the best way to do it but that is what I did. The problem is that I receive the BOM in excel format and then have to add specific data to it which defines where used. (Where used, date, rev, board name, unit name) My question is: Is there a simple way to import the table into an add record form then add the where used data that i need and do an autofill or something before I actually add the records to the table? I don't know if this makes sense to you or not but I don't know exactly how to pose this question. Any help you can give me would be greatly appreciated. nairb Well the data is not really in Access until it is in a table. However I do have an idea that may help. Import the data into the table. Then open the form with a where clause that will exclude all the records that already have the data you add, like where [unit name] is null (or blank). -- Joseph Meehan Dia duit |
#3
|
|||
|
|||
Joseph,
Thanks for the reply but I don't understand what you mean. I currently have a form that I created where "data entry" property is set to yes and the default view to datasheet. This allows me to copy and paste data into the form without actually entering it into the main table. At this point, or before the data is pasted in, I would like to be able to enter the where used data fields and have them fill in for each record. What I imagine is a form where you enter in the specific where used data then say here are the parts that are used herein and dump the spreadsheet in. Any thoughts? nairb "Joseph Meehan" wrote: nairb wrote: Hello, I have a question regarding the modification of data after importing it from a spreadsheet but before adding it to a table. I have created a db that is basically a parts library. In this I bring in bills of materials (BOM), which I add to a table called raw_masterlist. The purpose of the table is to enable me to do a where used search. This might not be the best way to do it but that is what I did. The problem is that I receive the BOM in excel format and then have to add specific data to it which defines where used. (Where used, date, rev, board name, unit name) My question is: Is there a simple way to import the table into an add record form then add the where used data that i need and do an autofill or something before I actually add the records to the table? I don't know if this makes sense to you or not but I don't know exactly how to pose this question. Any help you can give me would be greatly appreciated. nairb Well the data is not really in Access until it is in a table. However I do have an idea that may help. Import the data into the table. Then open the form with a where clause that will exclude all the records that already have the data you add, like where [unit name] is null (or blank). -- Joseph Meehan Dia duit |
#4
|
|||
|
|||
nairb wrote:
Joseph, Thanks for the reply but I don't understand what you mean. I currently have a form that I created where "data entry" property is set to yes and the default view to datasheet. This allows me to copy and paste data into the form without actually entering it into the main table. At this point, or before the data is pasted in, I would like to be able to enter the where used data fields and have them fill in for each record. What I imagine is a form where you enter in the specific where used data then say here are the parts that are used herein and dump the spreadsheet in. Any thoughts? OK lets back up a couple of steps. If I understand correctly, you want some sort of list of the parts needed for a each job and the Excel worksheet is providing a list based on the "location." Then you want to dump that list into the primary table. That sounds like a bad table design. Can you not just import all that Excel stuff into your database and have a list of parts based on location table. Then all you need do is link to that table rather than keep going back to the Excel list. As the Excel list changes you can have a import set up so you can update that table when you have new data to add from Excel. nairb "Joseph Meehan" wrote: nairb wrote: Hello, I have a question regarding the modification of data after importing it from a spreadsheet but before adding it to a table. I have created a db that is basically a parts library. In this I bring in bills of materials (BOM), which I add to a table called raw_masterlist. The purpose of the table is to enable me to do a where used search. This might not be the best way to do it but that is what I did. The problem is that I receive the BOM in excel format and then have to add specific data to it which defines where used. (Where used, date, rev, board name, unit name) My question is: Is there a simple way to import the table into an add record form then add the where used data that i need and do an autofill or something before I actually add the records to the table? I don't know if this makes sense to you or not but I don't know exactly how to pose this question. Any help you can give me would be greatly appreciated. nairb Well the data is not really in Access until it is in a table. However I do have an idea that may help. Import the data into the table. Then open the form with a where clause that will exclude all the records that already have the data you add, like where [unit name] is null (or blank). -- Joseph Meehan Dia duit -- Joseph Meehan Dia duit |
#5
|
|||
|
|||
Joseph,
Thanks once again for the reply... I agree that the table design is not ideal. With that said, could you scketch out how the table should be divided so that it is more ideal? I start with an Excel spreadsheet that has a parts list on it and need to enter the data into a table which I compare to a master parts library table. The problem is I have to manually, in Excel, add the where used data. This is the task I am trying to automate. Any recommendation would be appreciated. nairb "Joseph Meehan" wrote: nairb wrote: Joseph, Thanks for the reply but I don't understand what you mean. I currently have a form that I created where "data entry" property is set to yes and the default view to datasheet. This allows me to copy and paste data into the form without actually entering it into the main table. At this point, or before the data is pasted in, I would like to be able to enter the where used data fields and have them fill in for each record. What I imagine is a form where you enter in the specific where used data then say here are the parts that are used herein and dump the spreadsheet in. Any thoughts? OK lets back up a couple of steps. If I understand correctly, you want some sort of list of the parts needed for a each job and the Excel worksheet is providing a list based on the "location." Then you want to dump that list into the primary table. That sounds like a bad table design. Can you not just import all that Excel stuff into your database and have a list of parts based on location table. Then all you need do is link to that table rather than keep going back to the Excel list. As the Excel list changes you can have a import set up so you can update that table when you have new data to add from Excel. nairb "Joseph Meehan" wrote: nairb wrote: Hello, I have a question regarding the modification of data after importing it from a spreadsheet but before adding it to a table. I have created a db that is basically a parts library. In this I bring in bills of materials (BOM), which I add to a table called raw_masterlist. The purpose of the table is to enable me to do a where used search. This might not be the best way to do it but that is what I did. The problem is that I receive the BOM in excel format and then have to add specific data to it which defines where used. (Where used, date, rev, board name, unit name) My question is: Is there a simple way to import the table into an add record form then add the where used data that i need and do an autofill or something before I actually add the records to the table? I don't know if this makes sense to you or not but I don't know exactly how to pose this question. Any help you can give me would be greatly appreciated. nairb Well the data is not really in Access until it is in a table. However I do have an idea that may help. Import the data into the table. Then open the form with a where clause that will exclude all the records that already have the data you add, like where [unit name] is null (or blank). -- Joseph Meehan Dia duit -- Joseph Meehan Dia duit |
#6
|
|||
|
|||
nairb wrote:
Joseph, Thanks once again for the reply... I agree that the table design is not ideal. With that said, could you scketch out how the table should be divided so that it is more ideal? I start with an Excel spreadsheet that has a parts list on it and need to enter the data into a table which I compare to a master parts library table. The problem is I have to manually, in Excel, add the where used data. This is the task I am trying to automate. Any recommendation would be appreciated. nairb That is really tough since I don't really understand what you are doing and the data you have. Let me try giving you some things to think about. Maybe they will lead you in the right direction or at least give you some guidance on the type of information someone will need to know in order to understand what you have well enough to make some specific suggestions. When you look at a finished table, you should not see a lot of repeated data, like you would in a spreadsheet. In a spread sheet you might have StudentName StudentHomePhone StudentCellPhone Instructor InstructorPhoneNumber Joe 1234567 4567892 Ann 6549872 Mary 9517485 7554215 Ann 6549872 Jim 9787562 5467545 Frank 8529632 Sue 7539875 Frnak 8529632 That's fine in a spreadsheet, but in a relational database it should be Table1 StudentName StudentHomePhone StudentCellPhone InstructorID Joe 1234567 4567892 01 Mary 9517485 7554215 01 Jim 9787562 5467545 02 Sue 7539875 02 Table2 ID Instructor InstructorPhoneNumber 01 Ann 6549872 02 Frank 8529632 Table design usually have little to do with the outcome desired, only the data and how it works. Almost always a well designed table, that is a well normalized table, will make getting the desired results easier. If you have any references you may want to look un "normalize" It should also be in the Access help file. Does that help? "Joseph Meehan" wrote: nairb wrote: Joseph, Thanks for the reply but I don't understand what you mean. I currently have a form that I created where "data entry" property is set to yes and the default view to datasheet. This allows me to copy and paste data into the form without actually entering it into the main table. At this point, or before the data is pasted in, I would like to be able to enter the where used data fields and have them fill in for each record. What I imagine is a form where you enter in the specific where used data then say here are the parts that are used herein and dump the spreadsheet in. Any thoughts? OK lets back up a couple of steps. If I understand correctly, you want some sort of list of the parts needed for a each job and the Excel worksheet is providing a list based on the "location." Then you want to dump that list into the primary table. That sounds like a bad table design. Can you not just import all that Excel stuff into your database and have a list of parts based on location table. Then all you need do is link to that table rather than keep going back to the Excel list. As the Excel list changes you can have a import set up so you can update that table when you have new data to add from Excel. nairb "Joseph Meehan" wrote: nairb wrote: Hello, I have a question regarding the modification of data after importing it from a spreadsheet but before adding it to a table. I have created a db that is basically a parts library. In this I bring in bills of materials (BOM), which I add to a table called raw_masterlist. The purpose of the table is to enable me to do a where used search. This might not be the best way to do it but that is what I did. The problem is that I receive the BOM in excel format and then have to add specific data to it which defines where used. (Where used, date, rev, board name, unit name) My question is: Is there a simple way to import the table into an add record form then add the where used data that i need and do an autofill or something before I actually add the records to the table? I don't know if this makes sense to you or not but I don't know exactly how to pose this question. Any help you can give me would be greatly appreciated. nairb Well the data is not really in Access until it is in a table. However I do have an idea that may help. Import the data into the table. Then open the form with a where clause that will exclude all the records that already have the data you add, like where [unit name] is null (or blank). -- Joseph Meehan Dia duit -- Joseph Meehan Dia duit -- Joseph Meehan Dia duit |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
strategy for data entry in multiple tables | LAF | Using Forms | 18 | April 25th, 2005 04:04 AM |
transpose | john | Using Forms | 1 | November 24th, 2004 06:16 PM |
How to create graphs in a monthly report where the base data can change | John Clarke | Charts and Charting | 3 | June 25th, 2004 02:22 AM |
importing access data into excel | lmdolph | Worksheet Functions | 1 | January 31st, 2004 03:53 PM |