Importing Excel spreadsheets

[Tony Williams]

I have a database that holds monthly stats. We are supplied with
spreadsheets with 2 pages of new month's figures. I'm trying to import the
new month's figures but the import doesn't sort the new data correctly.
There are two critical fields, the month and the company name, there is then
a whole load of data for each company on two pages of the spread sheets. All
the fields on the two pages are contained in the one table in my database.
The companies may not always be the same on both pages, some may be on one
but not on the other. The Month is always the same on both pages.There is
always a record for the company in the existing database, but the month is
new.
I've tried importing both pages separately into separate tables and then run
append queries against them to update my table, but if the same company
appears in both pages it adds two records instead of one. I've tried making
a new table from the two imported spreadsheets and then appending that table
to my existing one but that creates an incredible number of duplicated
records. Can anyone help with this as we will need to do this monthly?
TIA
Tony

 

[John Nurick]

Hi Tony,

If I understand the situation right,

(a) The first worksheet contains something like this
Month, CompanyName, a set of other fields
and the second one contains something like this
Month, CompanyName, a different set of fields
while your Access table contains
Month, Companyname, both sets of fields

(b) For each company in your database, there may be zero or one record
in each worksheet each month.

Is that right?

What I can't work out is the structure of your main table. Is it meant
to have one record for each company that needs to be updated each month,
or is it meant to have one record for each company for each month?

If it's one record per company updated each month, you need to use two
Update queries: one that joins the first Excel worksheet to the main
table and updates the fields in the table that "belong" to the first
worksheet, and a second that does the same for the second worksheet and
the remaining fields.

If it's a matter of adding one record per company per month, this can be
done in one go with a suitable query. But please tell us more about the
situation first.

 

[Tony Williams]

Thanks John. Your assumptions under a) and b) are spot on. The main table
has a record for each company for each month. So for say March 2004 I have
49 records, one for each company that supplies data on page 1 and I could
have 47 records on the page 2 with data and although most of the companies
may be the same not all the companies on page 1 provide data on page 2 and
vice versa. For June 2004 I may have 47 records with data for each company
BUT there may be a new company or two providing data and one or two
companies from March may not provide data in June and so on. So my two pages
in the spread sheet don't necessarily have the same companies every month
but have probably a 90% match. If it was the same list of companies every
month providing the same data I could have just cut and pasted the two pages
together but as they're not I've got to find a way of adding the records to
my database and combining the data from pages 1 and 2 where the company name
is the same. Hope that explains my problem.
Thanks for you input, as a novice I greatly appreciate the help from the
experts on such Groups as this.
Tony

 

[John Nurick]

Right. It sounds like the task is to append one record to the main table
for each company that appears in either (or both) worksheets.

We'll do this by creating a group of queries which work together. One
essential: if a company appears on both worksheets its name must be
*exactly* the same in each.

You'll need to do a fair amount of experimentation to get it working, so
be sure to work on a copy of your database. Always make sure you have
backups!

First, set up each worksheet as a linked table (File|Get External Data
Link). Let's call them tblPage1 and tblPage2.

Then create a Select query, selecting both tables tblPage1 and tblPage2.
In upper part of the query design window, drag both the CompanyName and
the Month field from tblPage1 to tblPage2. Right click on one of the
resulting join lines, select Join Properties, and in the resulting
dialog select the second option, which should read "Include ALL records
from tblPage1 and only those from tblPage2..." Click OK. The join lines
should have arrows pointing from tblPage1 to tblPage2.

Now pull the CompanyName and Month columns from tblPage1 into the query
design grid, followed by all the other fields from either table that
need to go into your main table. Don't pull CompanyName and Month from
tblPage2. Save and test this query; let's call it qryAllPage1.

Next create another Select query, exactly the same except that tblPage1
and tblPage2 are reversed (i.e. "ALL the records from tblPage2..." and
pull the CompanyName and Month fields from tblPage2, not tblPage1. The
fields should have the same names and be in the same order in both
queries. Call this qryAllPage2.

So you've now got one query that returns one record for each company in
the first worksheet, the record including fields from both worksheets;
and a second query that does the same for each company in the second
worksheet. (And of course if a company exists in both worksheets then
there are duplicate records for it, one in each query.)

The next stage is to create a third query that concatenates the first
two and ignores the duplicates. This is a Union query. Create a new
query but don't select any tables for it. Switch into SQL View and type
the query:

SELECT * FROM qryAllPage1
UNION
SELECT * FROM qryAllPage2
ORDER BY CompanyName, Month
;

Save and test this query. Let's call it qryBothPages. You should get a
single record for each company for each month.

Once this is working properly, the final stage is to create an Append
query that draws its data from qryBothPages and appends to your main
table. (You may find that some fields won't append properly; this is
probably because of a conflict between the field type(s) in your table
and the types that were assigned to the fields in the linked tables. If
so, use expressions in the Update To row in the update query to convert
the data types as required.)

Have fun!

Thanks John. Your assumptions under a) and b) are spot on. The main table
has a record for each company for each month. So for say March 2004 I have
49 records, one for each company that supplies data on page 1 and I could
have 47 records on the page 2 with data and although most of the companies
may be the same not all the companies on page 1 provide data on page 2 and
vice versa. For June 2004 I may have 47 records with data for each company
BUT there may be a new company or two providing data and one or two
companies from March may not provide data in June and so on. So my two pages
in the spread sheet don't necessarily have the same companies every month
but have probably a 90% match. If it was the same list of companies every
month providing the same data I could have just cut and pasted the two pages
together but as they're not I've got to find a way of adding the records to
my database and combining the data from pages 1 and 2 where the company name
is the same. Hope that explains my problem.
Thanks for you input, as a novice I greatly appreciate the help from the
experts on such Groups as this.
Tony