Migrating data froexcel to access

Hi,
I am working on a project which is to migrate an existing excel sheet
to access I have done 90% of it .

But there are some gery areas which I don't understande them.

Is there any one that has had a such nightmare?

Many thanks

re: Migrating data froexcel to access

Hi5 wrote:[color=blue]
> Hi,
> I am working on a project which is to migrate an existing excel sheet
> to access I have done 90% of it .
>
> But there are some gery areas which I don't understande them.
>
> Is there any one that has had a such nightmare?[/color]

Only last night!

Although that involved a walk in a changing city, too.

Seriously: yes, migrating from a free-form medium like Excel hides 90%
of the work in the last 10% of the sheet. Pareto made a nice analysis of
this.

Questions?

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

re: Migrating data froexcel to access

Hi,
Yes the question is as follows :
I have actually finished the database designfor this client except one
part which is : they will have a table of clients in which each client
may have 1 or more categories recorded with each record in the client
table .
Is there any other way to have all this data without bmaking a middle
table as abig as all categories?
Many Thanks
F.K

*** Sent via Developersdex http://www.developersdex.com ***

re: Migrating data froexcel to access

Hi,
Yes the question is as follows :
I have actually finished the database designfor this client except one
part which is : they will have a table of clients in which each client
may have 1 or more categories recorded with each record in the client
table .
Is there any other way to have all this data without bmaking a middle
table as abig as all categories?
Many Thanks
F.K

*** Sent via Developersdex http://www.developersdex.com ***

re: Migrating data froexcel to access

Hi 5 wrote:[color=blue]
>
> Hi,
> Yes the question is as follows :
> I have actually finished the database designfor this client except one
> part which is : they will have a table of clients in which each client
> may have 1 or more categories recorded with each record in the client
> table .
> Is there any other way to have all this data without bmaking a middle
> table as abig as all categories?[/color]

Oh, there may be other ways. But as you describe is the normalized way
to do it. You would end up having some object that size anyway.

A category can be linked to many clients, and a client can have many
categories: that calls for a junction table. Just do it. Any objection?
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

re: Migrating data froexcel to access

Hi ,
I ahave tried the junctiontion table it dosen' t seem to have the
capablity to hold more thean one category per client.thereofre I have
tried making it with as many as possible culomns to overcome the
limitation of the three column table,but looks allright but seems that
access has problems with it?
it just loses the relationships and also it when I try to get a simple
query from the system which must looklike the original spreadsheet it
seems that access multiplies the number of rows bythe number of culomns
in the client category.
Is there any solution?
Many thanks

re: Migrating data froexcel to access

You should have

a client table with a primary key (possibly clientID)
a category table with a primary key (possibly categoryID)
a junction table with all fields from the above primary keys as primary
key (so clientID, categoryID)

This allows you to have as many records per client as you have
categories. Of course you won't have all categories in one record.

If you want to recreate the original spreadsheet, use the Crosstab Query
Wizard. When you create a new query you have several options, among
which this one.


--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html