Help with normalizing a database

[Darrell]

I have spreadsheet with four columns. Division, Unit, Office, Position

Each Division, has multiple units. Each Unit has multiple Offices, and each
Office has multiple Positions. The spreadsheet has data in all four columns
for each record. I want to import the sheet into Access tried spit it into
four tables representing the four columns, but keep the relationship to the
data from each column. I tried the import wizard, but it does not show the
subdatasheets as I expected. Can I manipulate the wizard in a way to
accomplish what I want. I am trying to eliminate the duplicated is each
column. All columns have duplicate data accept for the position column. I
have as this point inported y sheet into access, and added a primary key
field.

Darrell.

 

[Tim Ferguson]

Can I manipulate the wizard in a way to
accomplish what I want.

Unlikely. Just dump the wizards and design the stuff the way you want.
It's far quicker; you'll get a better database; you'll learn along the
way; and as long as you document everything as you go, the person who
comes after you will think you're a saint.

I am trying to eliminate the duplicated is
each column. All columns have duplicate data except for the position
column. I have as this point inported y sheet into access, and added
a primary key field.

This is a simple hierarchy. Unknowingly, however, you may be on the edge
of a religious war here... there are two basic design philosophies. One
uses all artificial keys:-

(*=Primary key, +=foreign key)

Divisions(*DivisnID, Name, HeadHoncho, etc)

Units(*UnitID, BelongsToDivision+, HQAddress, NumberOfSecretaries)

Offices(*OfficeID, BelongsToUnit+, PhoneNumber, FloorNumber)

Positions(*PositnID, BelongsToOffice+, DateHired, DateToBeFired)


The other uses natural keys:

Divisions(*InternalDivCode, Name, HeadHoncho, etc)

Units(*Divcode+, *UnitNumber, HQAddress, SecsCount, etc)

Offices(*DivCode, *UnitNumber, DoorNumber, Phone, Floor)
FK (DivCode, UnitNumber)

Positions(*Divcode, *UnitNumber, *DoorNumber, *JobCode,
Hired, ToBeFired)
FK (DivCode, UnitNumber, DoorNumber)


There are advantages and disadvantages to either method; personally I am
happy to argue either side against anyone who puts "always" or "never" in
their advice.

Hope it helps


Tim F

 

[Guest]

Attention MVP

I am sorry for posting the following in a reply box, but I cannot open a new
post pane, no matter what I try...............Can you transfer this post to
the correct category and give me some asistance???.....Again my apolagies
JN

Here's the query>>>>

Problem:

I have split an mdb database and placed the backend, the linked frontend and
three linked copies of the frontend in a folder on a server. The folder is
accessible to users on four linked machines. The format is 2000 and the
installed version of Access on the machines is 2003. Shortcuts from each of
the frontends have been placed on the desktops of the user machines. From
these the users can open their frontends and use them.

There is one problem. All frontends and the backend are set to compact and
repair automatically on close. This happens as required on one of the
machines, but on the other three the compact/repair fails, because the
database is behaving as write-protected on each of these machines. Thus the
Backup temporary DB1 cannot be deleted, nor can due process be completed with
the actual frontend. As a result, with each failure to compact, a residual
Db1,Db2, Db3 etc is being left in the server folder, while the actual DB is
not being updated with a c/r.

The problem can be overcome by going to the server folder on the machine
which is behaving properly, deleting the temp Db1, Db2, Db3 etc and
performing a Compact/Repair on each frontend, which works fine on this one
machine, for all frontends.

I cannot see any write-protect/read only element in the DB properties when I
examine each frontend, either from it’s own operating machine, or from the
one machine which is OK.

If I go to the server folder through any of the problematic machines, I
cannot delete the temp Db1,Db2, Db3 etc…….because the machine thinks they are
write-protected also!!

I have a number of databases operating on different Client systems, each
following the same distribution idea…………..Folder with frontends and backend
on server……..short-cutted to user machines. I have not experienced this
problem before. I assume it’s something to do with how the machines are set
up on the network, which is not my strong area

So I think the question is how to persuade each of the “Problematicâ€
machines to see the databases in the server folder as NOT being
write-protected. Any advice???

You can e-mail a reply to me at (e-mail address removed), as I am having problems
sending this as a new post

Any Help appreciated

Jim Normile
,

 

[Douglas J. Steele]

You need to ensure that all users have Change permission (that's Read,
Write, eXecute and Delete) on the folder where the MDB file exists. This is
done through the operating system, not through Access.

You need to ensure that the permissions for the Share and for the folder
itself aren't contradicting one another.

For what it's worth, setting the backend to compact on close does nothing.
You're not actually opening the backend (even though you'll see a locking
file created when there's actually a connection to the database). As well,
you're generating far more network traffic having the frontends on the
server, rather than on each user's hard drive.

 

[Guest]

Thank you Douglas

Your advice helpful and effective!!

Jim Normile