Hi Mark
It depends on how you want to use the data. You might consider adding
"reference tables" for data such as SoilType and ZoningDesignation. This
would make it easy for you to use combo boxes to aid and limit data entry.
CityTown/Province/PostalCode could conceivably be in a separate table (with
PostalCode as a unique or even primary key?) but I would not bother
normalising other repeating fields such as StreetType.
In general, when importing data, I prefer to create a table linked to the
data source rather than importing to a new table. Then I run append and
update queries to add and update data in the "real" tables. Add new records
to the reference tables first, using a query like this:
INSERT INTO tblZoningDesignations(zdName)
SELECT DISTINCT ZoningDesignation FROM LinkedImportTable
WHERE ZoningDesignation not in (select zdName from tblZoningDesignations);
(assuming tblZoningDesignations has fields zdID (autonumber), zdName (unique
text), zdDescription, etc)
Then, when you import into your "main" table, use a query joining
LinkedImportTable to tblZoningDesignations on ZoningDesignation=zdName to
get the zdID for the related record.
Best wishes,
Graham
> From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Mark
> Sent: Wednesday, 16 May 2012 09:32
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Importing text file into Access 2007 problems
>
>
> Hi thanks for your help as it seemed to do the trick. Now I do have a
follow up question...how can I "normalize it" the data into 3 tables. As
when I use the analyze table option it seems to make several tables...the
primary key should be rollnumber.
>
> Mark
>
> --- In MS_Access_Professionals@yahoogroups.com, "Graham Mandeno"
<graham@...> wrote:
> >
> > Hi Mark
> >
> > First, I apologise for all the BBCode tags that were sprinkled through
my
> > first answer. I had not yet had my second coffee for the morning and I
> > thought I was posting to a different forum :-/
> >
> > More importantly, it appears that your file is NOT fixed width, but is
> > actually variable width with "%" as a delimiter.
> >
> > In the import wizard, select "Delimited" and click Next. Then select
> > "Other" as the delimiter and type % in the adjacent box. Also, select
the
> > "First row contains field names" option, then click Next.
> >
> > You will then need to change the data types of some of the fields.
> > RollNumber should be changed to Text (because the 19-digit numbers are
too
> > long for a numeric data type). You may also want to change StreetNumber
> > (Integer or Text?), and AssessedValue (Currency?).
> >
> > Then all the records should import without errors.
> >
> > Best wishes,
> > Graham
Tidak ada komentar:
Posting Komentar