Export Data from Excel into Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am having trouble to find a way to update my database (keep old records,
update records, and add new records) when importing data from Excel into
Access.

Here is an example:

this is what i already have in my database

Issue Title Issue Start Date Issue Finish Date Status
Importing 04/06/07 04/09/07 Active

this is what i am importing from Excel to update/add new records

Issue Title Issue Start Date Issue Finish Date Status
Importing 04/06/07 04/15/07 Resolved
Exporting .....
......
.......

What is the best/easiest way to update my database on a weekly basis given
the source would be an Excel Spreadsheet.

Thanks a lot for the help!

Jean.
 
Jean

It sounds like you are saying that you want to update your Access table data
from some other (e.g., Excel) source.

First, backup your database -- you'll be doing updates.

Next, you'd need to have a way to identify which record(s) in Access will be
updated with data from (which records in) Excel. Do you have a common ID
field? Your example seemed to imply that [Issue Title] is a common field
(not exactly an ID, but ...).

Then you'd create a query, adding the table to be updated and the fields to
be updated. Then add the source of the update information (a table that is
a link-to-Excel would work). Join the two tables on their shared ID/common
field(s). When this query is working (as a select query), change it to an
update query, and update the fields in your Access table with the
appropriate fields from your source table.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,

Actually I have the same fields in my Excel and Access database. I just
learned the best way is not to import excel file into access, but to link my
excel file to access. There is however some disadvantages like you cannot
delete records.... i m gonna try this way...any advice please let me know.

Thanks a lot for the help.

Jeff Boyce said:
Jean

It sounds like you are saying that you want to update your Access table data
from some other (e.g., Excel) source.

First, backup your database -- you'll be doing updates.

Next, you'd need to have a way to identify which record(s) in Access will be
updated with data from (which records in) Excel. Do you have a common ID
field? Your example seemed to imply that [Issue Title] is a common field
(not exactly an ID, but ...).

Then you'd create a query, adding the table to be updated and the fields to
be updated. Then add the source of the update information (a table that is
a link-to-Excel would work). Join the two tables on their shared ID/common
field(s). When this query is working (as a select query), change it to an
update query, and update the fields in your Access table with the
appropriate fields from your source table.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jean said:
Hi,

I am having trouble to find a way to update my database (keep old records,
update records, and add new records) when importing data from Excel into
Access.

Here is an example:

this is what i already have in my database

Issue Title Issue Start Date Issue Finish Date Status
Importing 04/06/07 04/09/07 Active

this is what i am importing from Excel to update/add new records

Issue Title Issue Start Date Issue Finish Date Status
Importing 04/06/07 04/15/07 Resolved
Exporting .....
.....
......

What is the best/easiest way to update my database on a weekly basis given
the source would be an Excel Spreadsheet.

Thanks a lot for the help!

Jean.
Click to expand...
Click to expand...
 
Jean

I probably hid my suggestion to well - I was proposing that you link to the
Excel data, then use a query between your Access table and your (linked)
Excel "table" to do the update.

By the way, if your Excel data structure and Access data structure are
identical, there's a chance you aren't getting the best out of Access'
features and functions. Since Access is a relational database, if the data
structure you feed it is what you have to use for a spreadsheet, odds are
that the data are not well normalized.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jean said:
Jeff,

Actually I have the same fields in my Excel and Access database. I just
learned the best way is not to import excel file into access, but to link
my
excel file to access. There is however some disadvantages like you cannot
delete records.... i m gonna try this way...any advice please let me know.

Thanks a lot for the help.

Jeff Boyce said:
Jean

It sounds like you are saying that you want to update your Access table
data
from some other (e.g., Excel) source.

First, backup your database -- you'll be doing updates.

Next, you'd need to have a way to identify which record(s) in Access will
be
updated with data from (which records in) Excel. Do you have a common ID
field? Your example seemed to imply that [Issue Title] is a common field
(not exactly an ID, but ...).

Then you'd create a query, adding the table to be updated and the fields
to
be updated. Then add the source of the update information (a table that
is
a link-to-Excel would work). Join the two tables on their shared
ID/common
field(s). When this query is working (as a select query), change it to
an
update query, and update the fields in your Access table with the
appropriate fields from your source table.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jean said:
Hi,

I am having trouble to find a way to update my database (keep old
records,
update records, and add new records) when importing data from Excel
into
Access.

Here is an example:

this is what i already have in my database

Issue Title Issue Start Date Issue Finish Date Status
Importing 04/06/07 04/09/07 Active

this is what i am importing from Excel to update/add new records

Issue Title Issue Start Date Issue Finish Date Status
Importing 04/06/07 04/15/07 Resolved
Exporting .....
.....
......

What is the best/easiest way to update my database on a weekly basis
given
the source would be an Excel Spreadsheet.

Thanks a lot for the help!

Jean.
Click to expand...
Click to expand...
Click to expand...
 
Hi Jeff,

thanks for your input. Here is the exact scenario:

I have an issue list to be updated each week. I created an excel spreadshit
that has the following fields:

"Issue Title" (drop down list) "Opened By" "Assigned To" "Opened Date" "Due
Date"
"Priority" "Comments", "Category"( drop down list).

I created an access database that has the same fields + a primary key that
is an autonumber "ID". The purpose of creating the database was to build
professionel reports (Open Issues, Closed Issues, Issues By Category...) and
also keep track of what issues were closed......

So now what i am trying to do is to find a way (by linking my access issues
table or create a query to my excel spreadsheet) to update my access issues
list when a new issue is added, closed, updated..... For instance, how an ID
(primary key) is generated automatically along with the other issues fields
when a new issue in my excel spreadshit is added....

Thanks, please let me know if you have a solution.

Thanks a lot,

Jean.


Jeff Boyce said:
Jean

I probably hid my suggestion to well - I was proposing that you link to the
Excel data, then use a query between your Access table and your (linked)
Excel "table" to do the update.

By the way, if your Excel data structure and Access data structure are
identical, there's a chance you aren't getting the best out of Access'
features and functions. Since Access is a relational database, if the data
structure you feed it is what you have to use for a spreadsheet, odds are
that the data are not well normalized.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jean said:
Jeff,

Actually I have the same fields in my Excel and Access database. I just
learned the best way is not to import excel file into access, but to link
my
excel file to access. There is however some disadvantages like you cannot
delete records.... i m gonna try this way...any advice please let me know.

Thanks a lot for the help.

Jeff Boyce said:
Jean

It sounds like you are saying that you want to update your Access table
data
from some other (e.g., Excel) source.

First, backup your database -- you'll be doing updates.

Next, you'd need to have a way to identify which record(s) in Access will
be
updated with data from (which records in) Excel. Do you have a common ID
field? Your example seemed to imply that [Issue Title] is a common field
(not exactly an ID, but ...).

Then you'd create a query, adding the table to be updated and the fields
to
be updated. Then add the source of the update information (a table that
is
a link-to-Excel would work). Join the two tables on their shared
ID/common
field(s). When this query is working (as a select query), change it to
an
update query, and update the fields in your Access table with the
appropriate fields from your source table.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi,

I am having trouble to find a way to update my database (keep old
records,
update records, and add new records) when importing data from Excel
into
Access.

Here is an example:

this is what i already have in my database

Issue Title Issue Start Date Issue Finish Date Status
Importing 04/06/07 04/09/07 Active

this is what i am importing from Excel to update/add new records

Issue Title Issue Start Date Issue Finish Date Status
Importing 04/06/07 04/15/07 Resolved
Exporting .....
.....
......

What is the best/easiest way to update my database on a weekly basis
given
the source would be an Excel Spreadsheet.

Thanks a lot for the help!

Jean.
Click to expand...
Click to expand...
Click to expand...
 
Jean

I'm not clear whether you are creating a totally new Excel spreadsheet each
time period, with only "new" issues, or if you are continuously adding new
issues to an existing Excel spreadsheet.

If you want to ensure that you cannot append an issue to an Access table if
you already have that issue in the table, you could add an index to the
table that included all fields that could uniquely identify the issue. That
way, even if you TRIED to append a (pre-existing) issue, Access would
refuse, due to a violation of the index.

Have you looked into "normalization"? The structure you have in Excel may
not be a good pattern for the structure you have in Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jean said:
Hi Jeff,

thanks for your input. Here is the exact scenario:

I have an issue list to be updated each week. I created an excel
spreadshit
that has the following fields:

"Issue Title" (drop down list) "Opened By" "Assigned To" "Opened Date"
"Due
Date"
"Priority" "Comments", "Category"( drop down list).

I created an access database that has the same fields + a primary key that
is an autonumber "ID". The purpose of creating the database was to build
professionel reports (Open Issues, Closed Issues, Issues By Category...)
and
also keep track of what issues were closed......

So now what i am trying to do is to find a way (by linking my access
issues
table or create a query to my excel spreadsheet) to update my access
issues
list when a new issue is added, closed, updated..... For instance, how an
ID
(primary key) is generated automatically along with the other issues
fields
when a new issue in my excel spreadshit is added....

Thanks, please let me know if you have a solution.

Thanks a lot,

Jean.


Jeff Boyce said:
Jean

I probably hid my suggestion to well - I was proposing that you link to
the
Excel data, then use a query between your Access table and your (linked)
Excel "table" to do the update.

By the way, if your Excel data structure and Access data structure are
identical, there's a chance you aren't getting the best out of Access'
features and functions. Since Access is a relational database, if the
data
structure you feed it is what you have to use for a spreadsheet, odds are
that the data are not well normalized.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jean said:
Jeff,

Actually I have the same fields in my Excel and Access database. I just
learned the best way is not to import excel file into access, but to
link
my
excel file to access. There is however some disadvantages like you
cannot
delete records.... i m gonna try this way...any advice please let me
know.

Thanks a lot for the help.

:

Jean

It sounds like you are saying that you want to update your Access
table
data
from some other (e.g., Excel) source.

First, backup your database -- you'll be doing updates.

Next, you'd need to have a way to identify which record(s) in Access
will
be
updated with data from (which records in) Excel. Do you have a common
ID
field? Your example seemed to imply that [Issue Title] is a common
field
(not exactly an ID, but ...).

Then you'd create a query, adding the table to be updated and the
fields
to
be updated. Then add the source of the update information (a table
that
is
a link-to-Excel would work). Join the two tables on their shared
ID/common
field(s). When this query is working (as a select query), change it
to
an
update query, and update the fields in your Access table with the
appropriate fields from your source table.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi,

I am having trouble to find a way to update my database (keep old
records,
update records, and add new records) when importing data from Excel
into
Access.

Here is an example:

this is what i already have in my database

Issue Title Issue Start Date Issue Finish Date Status
Importing 04/06/07 04/09/07 Active

this is what i am importing from Excel to update/add new records

Issue Title Issue Start Date Issue Finish Date Status
Importing 04/06/07 04/15/07
Resolved
Exporting .....
.....
......

What is the best/easiest way to update my database on a weekly basis
given
the source would be an Excel Spreadsheet.

Thanks a lot for the help!

Jean.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top