Normalisation Question perhaps?

Hi all,
I have been asked to look at an existing Access 2002 DB tasked with
adding some more features and improving the speed at which it runs. It is a
split fe/be application with around 30,000 records. The main form has quite
a lot of fields and I am trying to reduce them. Every morning, the users run
a SQL from the main works management system, save the text file and import
the data. One of the fields from the SQL they run (ipartid which is 20
characters) is split (from the original SQL) and imported into 5 separate
fields. My question is, would it be more efficient to import it as one field
and then split it down in the form i.e.
mid([import].[ipartid],1,5) or would it be quicker to leave it as 5 separate
fields?

I hope I have explained this OK

Thanks in advance,

Mark

re: Normalisation Question perhaps?

I'm not sure of the reason that the id is 5 part, but I suspect they
indeed refer to 5 separate attributes of the id. In which case they may
need to be split in all databases.

e.g.if the partid represents company, bin, category etc. then they will
probably be needed separtely in other or future queries.

Mark Reed <mark.reed75@ntlworld.com> posted in
news:4FAGc.1020$qD.923@newsfe3-gui.ntli.net
[color=blue]
> Hi all,
> I have been asked to look at an existing Access 2002 DB tasked
> with adding some more features and improving the speed at which it
> runs. It is a split fe/be application with around 30,000 records. The
> main form has quite a lot of fields and I am trying to reduce them.
> Every morning, the users run a SQL from the main works management
> system, save the text file and import the data. One of the fields
> from the SQL they run (ipartid which is 20 characters) is split (from
> the original SQL) and imported into 5 separate fields. My question
> is, would it be more efficient to import it as one field and then
> split it down in the form i.e. mid([import].[ipartid],1,5) or would
> it be quicker to leave it as 5 separate fields?
>
> I hope I have explained this OK
>
> Thanks in advance,
>
> Mark[/color]

--
Phil

re: Normalisation Question perhaps?

"Mark Reed" <mark.reed75@ntlworld.com> wrote in message
news:4FAGc.1020$qD.923@newsfe3-gui.ntli.net...[color=blue]
> Hi all,
> I have been asked to look at an existing Access 2002 DB tasked with
> adding some more features and improving the speed at which it runs. It is[/color]
a[color=blue]
> split fe/be application with around 30,000 records. The main form has[/color]
quite[color=blue]
> a lot of fields and I am trying to reduce them. Every morning, the users[/color]
run[color=blue]
> a SQL from the main works management system, save the text file and import
> the data. One of the fields from the SQL they run (ipartid which is 20
> characters) is split (from the original SQL) and imported into 5 separate
> fields. My question is, would it be more efficient to import it as one[/color]
field[color=blue]
> and then split it down in the form i.e.
> mid([import].[ipartid],1,5) or would it be quicker to leave it as 5[/color]
separate[color=blue]
> fields?[/color]

It would be silly do undo something that was done correctly. A multi-valued
field means your resulting table isn't even in first normal form. It means
you
have to do calculations every time you want the multiple pieces of
information
as seperate pieces of information.

re: Normalisation Question perhaps?

Mark,
As a rule, it's generally easier to work in code with values split into
columns. If you think about the parts of a persons name: Prefix, First
Name, Middle Name, & Suffix, then consider the task of pulling out the
middle name using string manipulation, you don't have to work with it for
long before it becomes clear that it's a lot easier if the parts of the name
are in seperate columns. I'd split it on when I imported it.

"Mark Reed" <mark.reed75@ntlworld.com> wrote in message
news:4FAGc.1020$qD.923@newsfe3-gui.ntli.net...[color=blue]
> Hi all,
> I have been asked to look at an existing Access 2002 DB tasked with
> adding some more features and improving the speed at which it runs. It is[/color]
a[color=blue]
> split fe/be application with around 30,000 records. The main form has[/color]
quite[color=blue]
> a lot of fields and I am trying to reduce them. Every morning, the users[/color]
run[color=blue]
> a SQL from the main works management system, save the text file and import
> the data. One of the fields from the SQL they run (ipartid which is 20
> characters) is split (from the original SQL) and imported into 5 separate
> fields. My question is, would it be more efficient to import it as one[/color]
field[color=blue]
> and then split it down in the form i.e.
> mid([import].[ipartid],1,5) or would it be quicker to leave it as 5[/color]
separate[color=blue]
> fields?
>
> I hope I have explained this OK
>
> Thanks in advance,
>
> Mark
>
>[/color]