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

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

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

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