Re: Importing Excel spreadsheets

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: John Nurick (j.mapSoN.nurick_at_dial.pipex.com)
Date: 01/10/05 

Date: Mon, 10 Jan 2005 21:06:55 +0000

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 work*** 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!

On Mon, 10 Jan 2005 14:31:26 +0000 (UTC), "Tony Williams"
<tw@tcpinvalid.com> wrote:

>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 *** 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" <j.mapSoN.nurick@dial.pipex.com> wrote in message
>news:tq93u09k54f51502jus8ifnkaftf904jit@4ax.com...
>> Hi Tony,
>>
>> If I understand the situation right,
>>
>> (a) The first work*** 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 work*** 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 work*** to the main
>> table and updates the fields in the table that "belong" to the first
>> work***, and a second that does the same for the second work*** 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.
>>
>>
>> On Sun, 9 Jan 2005 19:48:33 +0000 (UTC), "Tony Williams"
>> <tw@tcpinvalid.com> wrote:
>>
>> >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 [Microsoft Access MVP]
>>
>> Please respond in the newgroup and not by email.
> 

--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
Quantcast