normalizing my data...help (1 Viewer)

[token_remedie]

I thought I had normalised all my data, and then when I ran the query to put it all together it showed the same record a million times over and crashed the DB.
So if I could get a hand just telling me what I need to do to normalize this properly I would be forever in debt.

The Master Table:
UID PK Auto Num
Asset Number Number
Cocd Number
Class Text
Asset Description text
Serial No# text
Invent No text
CostCentre number
Plnt number
LOCATION text


I inherited this from a bunch of idiot spreadsheets and all i want to do is not duplicate the same data for every record. The only unique things are asset number serial, invent no and UID everything else has already been split into tables e.g asset description table, cocd table, costcentre table etc. Basically all I need to know is how to I have my main table with JUST asset number, serial number, invent number, and UID and then everything else is built off relationships and it's accurate?
I hope that makes sense, I almost died when I realised it was all wrong because I thought I was almost finished

 

[jdraw]

Instead of Main Table, can you tell us what the data represents? What is the application?
We can't really offer focused advice until we understand what you are dealing with.

I can suggest the first few topics at this site as highly recommended and appropriate for you generally.
http://www.rogersaccesslibrary.com/forum/topic238.html

Normalization -- Entity Relationship Diagramming

 

[Alansidman]

Your issue may be in your query. How many tables in your query and how they are joined. If you put all your tables in the query and did not join them correctly or at all, then you may have received a cartesian result.

Post a copy of your SQL statement for your query.

 

[token_remedie]

I've been doing some reading, and where I'm going wrong is, the relative field in every table isn't really viable. Like the only way I can actually link all the tables is to have the foreign key as primary ID autonumber or asset number, if I'm going to put that in every single table I may as well just leave it the way it is because it's still replicating something 50000 times in each table.
I'm basically building a library cataloging system, but it's for assets instead of books. I still need to track where it's been and where it is now same as I might track who has borrowed a book from a library.

edit: I also deleted the query because it was wrong

 

[jdraw]

There is a data model here that may help.
Here is rough Access layout.
http://www.databaseanswers.org/data_models/home_libraries/access_database_for_home_libraries.htm