normalising my data

[token_remedie]

hi all,
so I've learn't a lot about databases this week thank you to you wonderful people, and time and time again I'm hearing NORMALIZE YOUR DATA!!! and I have always thought huh? so I read this:
http://office.microsoft.com/en-us/access-help/database-design-basics-HA001224247.aspx
now my question is:
I've managed to import ALL of the data I need into one stupidly big 300 MB table, really the only truly unique identifiers in this table are asset number and serial number. and invent no. I then have colums like, location code, location name, cost, plnt, manufacturer, which can all be referenced in seperate tables (since we only have three manufactuers that table would be three entries long, yet in the main table it's repeated god knows how many times.

so how do I now, become part of the 'in' crowd that can also say NORMALIZE YOUR DATA!!! by normalising my own? how do I put that information into separate tables yet access still knows for example which manufacturer an asset number is supposed to have?

I hope that made sense.

 

[Alansidman]

Create queries that contain the fields you want in each table. Once the query is run and you are satisfied that the data is correct, change the query to a make table query. Go into the new table and set a primary key.

Do this until you have all your data in the tables as you need them for normalization.

Caution: Before you start, backup your maintable/database in case you make a mistake and need to restore.

Good luck
Alan

 

[token_remedie]

awesome, so I've got all the tables created with unique data in them, which ended up being 15 extra tables, I've only got in each table the information I require and a UID which is the primary key. So asset description just has UID and asset description for example.

Now what? I tried creating a query linking the main table fields with the 15 other tables but that showed nothing, so I guess somehow I need to associate all these tables together and then from the main table delete the redundant information I dont need due to the other 15 tables..... but now do I do that?

 

[Alansidman]

Did you eliminate the original flat file that was the source of the other tables you created? You should not need it in your queries if you have all the data in the other tables. Join the other tables, PK to FK as necessary and build your queries. Perhaps it is time for you to tell us (or show us via an upload of your db) what you are attempting to do and what is not happening.

Alan

 

[token_remedie]

I deleted everything because I didn't think it was right, but this is my imported table. The F fields can be ignored they've just come across from the excel import. Anyway the only fields that are different every time are asset number, serial number and invent number. Everything else can go into a separate table. I've been reading about foreign keys, so I think I need to put the ID - primary key from this table into each one of the separate tables but then it doesn't create one instance of that information. For example if i put the primary key from that main table into asset description (which there should be 4 entries for) it ends up creating 50000 because the asset number is unique each time, does that make sense? if you need me to re-create the separate tables and put the relationships back I can do that so you can see what I'm trying to do to normalise.

 

[Alansidman]

I cannot read your mind as to how you wish to set up your db because I don't know what the fields represent or what you want to do with them. I suggest you go back and put the 15 tables in with record IDs and FK's and let us look at what you are attempting. It also helps if there is some data in the fields. Not a lot -- just a few records.

Alan

 

[token_remedie]

thank you so much for your help, I worked it out. I set up the relationships and then just delete them from the query and that works perfectly

 

[Alansidman]

Excellent. Glad to hear it.

 

[token_remedie]

the database is now 25 MB, down from 300 so that's AWESOME!!! except now when I run the query to display all the fields together, it shows fine until i try to go to the last record then it says nooo way no how this thing is bigger than 2gb. If I just base my form on that query will it run ok? There's no real need to open up every record at once anyway I was just seeing if it worked, or should it not even crash like that?

 

[Alansidman]

Run a compact and repair. When you delete things in Access it does not necessarily give back the space until you run a compact and repair. See if that helps.

 

[token_remedie]

yeah I did that, the db is down to 9.2mb it just doesn't like that query. if i open the form attached to that query and jump to the last record it does the same thing :S

 

[Alansidman]

I would try one more thing. If that doesn't solve it, then post a new thread with the new issue so that you can max eyes on it.

Open a new database (blank). Now import all objects and data into the new database from the old one. The old one (current) may have some corruption in it. Sometimes this works and other times it doesn't. Its worth a try before you attempt something more complex.

Good luck.