Importing data from Excel then adding additional data via a form

[nairb]

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

 

[nairb]

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

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).

 

[nairb]

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,
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

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).