Refreshing Data in a Table

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

Guest

I am a rather new user to Access, so pls excuse me if this is an obvious
question.
I have a table in Access that was imported from excel. I am going to be
receiving this xls report on a monthly basis. The excel reports has a BD tab
and a Won tab (each own Access tables). This data will change on a monthly
basis b/c rows on BD will move to Won if Status = Won.
I need to determine how I will handle these updates to my preexisting
tables. Do I need to reimport each time? How do I ensure that when
importing/refreshing that duplicatants are overwritten?
Thanks in advance for any help on this :-)
 
Hi Amanda,

Is there a reference number or code of some kind that is the same for a
"job" whether it's in BD or in Won?

If so, the "database" way of doing things would probably to have a
single table in Access which contains both "jobs" at the BD stage and
jobs that have been won, with a field (e.g. DateWon) that distinguishes
between them.

In that case the general idea would be to link the two Excel tables, BD
and WOn, and use a series of queries to update and move the data into
your "combined" table which I'll call tblMain:

1)Append to tblMain any records from Won that don't already have
counterparts in Won. This handles any jobs that appear in Won without
first having been in BD (maybe this is only a remote possibility).

2) Append to tblMain any records from BD that don't already have
counterparts in Won.

3) An update query joining BD and tblMain, so that records that already
exist in tblMain but have been modified in the BD worksheet are broght
up to date

4) An update query joining Won and tblMain, so that records already in
tblMain (including "BD" records) that are now in Won are brought up to
date.

If on the other hand there isn't simple transformation of the same "job"
from "BD" to "Won" - e.g. if a given BD may result in zero, one or more
than one jobs won, you'll need two tables - tblBD and tblWon - in the
main database. But the principal of using pairs of queries, an update
query to update existing data and an append query to add new data,
remains the same. You will probably also want to use a further update
query or two to mark records in tblBD that have been "converted" into
won jobs or have ended without conversion.
 
Wow - thanks for all of the extremely helpful info. I will work to implement
your recommendations, but before I do this I have a more simple question. I
will be getting these excel reports once a month, and so I need to figure out
how to import the new reports I receive each month into my exisiting table.

I thought that maybe I would simply need to import the new data (assuming
matching records would not be duplicated b/c I had index of no duplicants).
This however did not work and I got an error. My only work around on this
was to import the new xls report into a new table, but I am not sure how to
link these tables so that only distinct entries are returned on my queries
for "BD" for example (since I will continue to have a BD and Won table). I am
sure there is a better way to import new data into an existing table, so I
would really appreciate it if you could share more of your wisdom.

Thanks so much!
 
Amanda,

You'll normally get an error about key violations if records cannot be
imported because they are duplicates. The simple approach is just to
ignore the error (I think the way to do this is to bracket the line of
code with
DoCmd.SetWarnings 0
..
DoCmd.SetWarnings -1

Alternatively, one can use a join in the append query so that it only
attempts to append the records that don't already exist. Post back if
you need help with this, giving more informatoin about your data (field
names, which field(s) is the primary key, etc.).
 
John,

The fields that I have are:
ID
Create Date
Region
Country
Industry
Client Name
Client Partner
Opp Name
Type of Work (HVC, SI, O)
Status
Service Line
Domain
Campaign/ Big Initiative
Opportunity Type (CCT or CCO)
OppID
Comments
End Date
Closing Year
C+O Rev$
Con %
OS %

ID is the default primary key assigned by access. I had intended to assign
OppID as primary, but I got an error that it could not except b/c some
records were null (thinking i need to redo import and select a range of cells
in excel b/c importing the whole sheet w/ blank rows). I will fix this.

With that said, assuming I use OppID as my primary I am not sure where to go
from there, other than to import my new months report as a new table. I will
play around and familize myself w/ appending tables, but any general tips you
could provide would be extremely helpful.

Thanks again!

Amanda
 
Hi Amanda,

It's essential that there is some field or combination of fields that
appears in both the Excel sheet and the Access table, and that
unambiguously identifies each record.

That sounds like OppID (ID is ruled out because it only exists in the
Access table).

It's not necessary to set this to be the primary key of the Access
table, but if you don't you must create an index on the field that
doesn't allow duplicates and doesn't ignore null values.

Assuming that OppID is a unique value and that you've indexed it in the
Access table, a query like this will append the new records to the
table. You'll need to substitute your actual table, workbook, worksheet,
etc. Paste this into the query designer in SQL view; after that it
should work normally in Design view:

INSERT INTO MyTable
SELECT New.*
FROM [Excel 8.0;HDR=Yes;Database=D:\Folder\Workbook.XLS;].[Sheet1$] AS
New
LEFT JOIN MyTable AS Existing
ON New.OppID = Existing.OppID
WHERE Existing.OppID Is Null
;

Then the query to update the existing records will be like this:

UPDATE MyTable AS Existing
INNER JOIN
[Excel 8.0;HDR=Yes;Database=D:\Folder\Workbook.XLS;].[Sheet1$] AS New
ON Existing.OppID = New.OppID
SET Existing.[Create Date]=New.[Create Date],
Existing.Region = New.Region,
...add the other fields...
;

If you want to import from a specific range of cells, just add them to
the sheet name, e.g. [Sheet1$B5:K99]. For a named range, as far as I can
remember you substitute the range name for the sheet name and omit the
$.
 
Hi John,

Thanks for responding. Actually OppID is a field in both Excel and Access
(I meant that ID was the field assigned by Access as the primary).
I indexed OppID, and then used the first query you recommended. This worked
great. I am now trying to determine what the second SQL statement will do.
I guess I was thinking that the first would take care of all my needs (i.e.
imported all opportunities from xls that were not already on my pre-existing
table).

My questions for you now are:
1. Is the second SQL Statement there for the purpose of updating all
(assuming that fields on an entry can change per each excel report)?
2. How should I handle subsequent updates. Will I need to add new queries
each time that I get a new excel report or do I add on to the existing
queries?

Once again thank you for all of the help and patience!

Amanda

John Nurick said:
Hi Amanda,

It's essential that there is some field or combination of fields that
appears in both the Excel sheet and the Access table, and that
unambiguously identifies each record.

That sounds like OppID (ID is ruled out because it only exists in the
Access table).

It's not necessary to set this to be the primary key of the Access
table, but if you don't you must create an index on the field that
doesn't allow duplicates and doesn't ignore null values.

Assuming that OppID is a unique value and that you've indexed it in the
Access table, a query like this will append the new records to the
table. You'll need to substitute your actual table, workbook, worksheet,
etc. Paste this into the query designer in SQL view; after that it
should work normally in Design view:

INSERT INTO MyTable
SELECT New.*
FROM [Excel 8.0;HDR=Yes;Database=D:\Folder\Workbook.XLS;].[Sheet1$] AS
New
LEFT JOIN MyTable AS Existing
ON New.OppID = Existing.OppID
WHERE Existing.OppID Is Null
;

Then the query to update the existing records will be like this:

UPDATE MyTable AS Existing
INNER JOIN
[Excel 8.0;HDR=Yes;Database=D:\Folder\Workbook.XLS;].[Sheet1$] AS New
ON Existing.OppID = New.OppID
SET Existing.[Create Date]=New.[Create Date],
Existing.Region = New.Region,
...add the other fields...
;

If you want to import from a specific range of cells, just add them to
the sheet name, e.g. [Sheet1$B5:K99]. For a named range, as far as I can
remember you substitute the range name for the sheet name and omit the
$.


John,

The fields that I have are:
ID
Create Date
Region
Country
Industry
Client Name
Client Partner
Opp Name
Type of Work (HVC, SI, O)
Status
Service Line
Domain
Campaign/ Big Initiative
Opportunity Type (CCT or CCO)
OppID
Comments
End Date
Closing Year
C+O Rev$
Con %
OS %

ID is the default primary key assigned by access. I had intended to assign
OppID as primary, but I got an error that it could not except b/c some
records were null (thinking i need to redo import and select a range of cells
in excel b/c importing the whole sheet w/ blank rows). I will fix this.

With that said, assuming I use OppID as my primary I am not sure where to go
from there, other than to import my new months report as a new table. I will
play around and familize myself w/ appending tables, but any general tips you
could provide would be extremely helpful.

Thanks again!

Amanda
Click to expand...
Click to expand...
 
Amanda,

The first query
INSERT INTO MyTable ...
will add a record to MyTable for every record in the Excel worksheet
whose OppID does not already appear in MyTable. So it handles any new
records.

The second,
UPDATE MyTable ...
goes through the existing records - the ones whose OppID appears in both
MyTable and in the Excel worksheet, so that any edits that have been
made in Excel are taken over into the Access table. If all you want to
do is to append the new records from Excel, don't use this, just use the
first query.

Subsequent updates from Excel shouldn't need new queries. Instead, you
can just decide on a standard name and location for the Excel file and
use this in the query/ies, e.g. "C:\Temp\Import.xls". Then each time a
new Excel file comes in, copy it to C:\Temp\, rename it to Import.xls,
and run the query/ies.






Hi John,

Thanks for responding. Actually OppID is a field in both Excel and Access
(I meant that ID was the field assigned by Access as the primary).
I indexed OppID, and then used the first query you recommended. This worked
great. I am now trying to determine what the second SQL statement will do.
I guess I was thinking that the first would take care of all my needs (i.e.
imported all opportunities from xls that were not already on my pre-existing
table).

My questions for you now are:
1. Is the second SQL Statement there for the purpose of updating all
(assuming that fields on an entry can change per each excel report)?
2. How should I handle subsequent updates. Will I need to add new queries
each time that I get a new excel report or do I add on to the existing
queries?

Once again thank you for all of the help and patience!

Amanda

John Nurick said:
Hi Amanda,

It's essential that there is some field or combination of fields that
appears in both the Excel sheet and the Access table, and that
unambiguously identifies each record.

That sounds like OppID (ID is ruled out because it only exists in the
Access table).

It's not necessary to set this to be the primary key of the Access
table, but if you don't you must create an index on the field that
doesn't allow duplicates and doesn't ignore null values.

Assuming that OppID is a unique value and that you've indexed it in the
Access table, a query like this will append the new records to the
table. You'll need to substitute your actual table, workbook, worksheet,
etc. Paste this into the query designer in SQL view; after that it
should work normally in Design view:

INSERT INTO MyTable
SELECT New.*
FROM [Excel 8.0;HDR=Yes;Database=D:\Folder\Workbook.XLS;].[Sheet1$] AS
New
LEFT JOIN MyTable AS Existing
ON New.OppID = Existing.OppID
WHERE Existing.OppID Is Null
;

Then the query to update the existing records will be like this:

UPDATE MyTable AS Existing
INNER JOIN
[Excel 8.0;HDR=Yes;Database=D:\Folder\Workbook.XLS;].[Sheet1$] AS New
ON Existing.OppID = New.OppID
SET Existing.[Create Date]=New.[Create Date],
Existing.Region = New.Region,
...add the other fields...
;

If you want to import from a specific range of cells, just add them to
the sheet name, e.g. [Sheet1$B5:K99]. For a named range, as far as I can
remember you substitute the range name for the sheet name and omit the
$.


John,

The fields that I have are:
ID
Create Date
Region
Country
Industry
Client Name
Client Partner
Opp Name
Type of Work (HVC, SI, O)
Status
Service Line
Domain
Campaign/ Big Initiative
Opportunity Type (CCT or CCO)
OppID
Comments
End Date
Closing Year
C+O Rev$
Con %
OS %

ID is the default primary key assigned by access. I had intended to assign
OppID as primary, but I got an error that it could not except b/c some
records were null (thinking i need to redo import and select a range of cells
in excel b/c importing the whole sheet w/ blank rows). I will fix this.

With that said, assuming I use OppID as my primary I am not sure where to go
from there, other than to import my new months report as a new table. I will
play around and familize myself w/ appending tables, but any general tips you
could provide would be extremely helpful.

Thanks again!

Amanda

:

Amanda,

You'll normally get an error about key violations if records cannot be
imported because they are duplicates. The simple approach is just to
ignore the error (I think the way to do this is to bracket the line of
code with
DoCmd.SetWarnings 0
..
DoCmd.SetWarnings -1

Alternatively, one can use a join in the append query so that it only
attempts to append the records that don't already exist. Post back if
you need help with this, giving more informatoin about your data (field
names, which field(s) is the primary key, etc.).

On Tue, 21 Sep 2004 21:23:03 -0700, "Amanda Guenthner"

Wow - thanks for all of the extremely helpful info. I will work to implement
your recommendations, but before I do this I have a more simple question. I
will be getting these excel reports once a month, and so I need to figure out
how to import the new reports I receive each month into my exisiting table.

I thought that maybe I would simply need to import the new data (assuming
matching records would not be duplicated b/c I had index of no duplicants).
This however did not work and I got an error. My only work around on this
was to import the new xls report into a new table, but I am not sure how to
link these tables so that only distinct entries are returned on my queries
for "BD" for example (since I will continue to have a BD and Won table). I am
sure there is a better way to import new data into an existing table, so I
would really appreciate it if you could share more of your wisdom.

Thanks so much!


:

Hi Amanda,

Is there a reference number or code of some kind that is the same for a
"job" whether it's in BD or in Won?

If so, the "database" way of doing things would probably to have a
single table in Access which contains both "jobs" at the BD stage and
jobs that have been won, with a field (e.g. DateWon) that distinguishes
between them.

In that case the general idea would be to link the two Excel tables, BD
and WOn, and use a series of queries to update and move the data into
your "combined" table which I'll call tblMain:

1)Append to tblMain any records from Won that don't already have
counterparts in Won. This handles any jobs that appear in Won without
first having been in BD (maybe this is only a remote possibility).

2) Append to tblMain any records from BD that don't already have
counterparts in Won.

3) An update query joining BD and tblMain, so that records that already
exist in tblMain but have been modified in the BD worksheet are broght
up to date

4) An update query joining Won and tblMain, so that records already in
tblMain (including "BD" records) that are now in Won are brought up to
date.

If on the other hand there isn't simple transformation of the same "job"
from "BD" to "Won" - e.g. if a given BD may result in zero, one or more
than one jobs won, you'll need two tables - tblBD and tblWon - in the
main database. But the principal of using pairs of queries, an update
query to update existing data and an append query to add new data,
remains the same. You will probably also want to use a further update
query or two to mark records in tblBD that have been "converted" into
won jobs or have ended without conversion.





On Mon, 20 Sep 2004 16:41:01 -0700, "Amanda Guenthner"

I am a rather new user to Access, so pls excuse me if this is an obvious
question.
I have a table in Access that was imported from excel. I am going to be
receiving this xls report on a monthly basis. The excel reports has a BD tab
and a Won tab (each own Access tables). This data will change on a monthly
basis b/c rows on BD will move to Won if Status = Won.
I need to determine how I will handle these updates to my preexisting
tables. Do I need to reimport each time? How do I ensure that when
importing/refreshing that duplicatants are overwritten?
Thanks in advance for any help on this :-)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top