Please help me normalize this table into 3NF, I just need it normalized in Excel so I can export it into access. Link: http://www.megaupload.com/?d=20EOSRRP
I should be able to take a look at this later this evening for you. I assume you just want the field names in the first tab of your attachment normalized into tables and columns? What's your deadline?
yes, just normalized
that's part one of the assignment, after normalization I need it imported into access, the instructions are on the second tab of the excel file but that part is optional. However, if you're able to help me with that part too then I will add another bonus of $30 upon completion. Deadline is tomorrow at 11:59pm, but like I mentioned I need to import the normalized tables into access so would need it before then.
Understood. I will have some time later on this evening to work on it. Which version of Access? I should be able to get the fields normalized tonight and then I will see how much time I have after that. At least I can answer your question so you can start on the next part.
any version of access
if you can help me out that would be great, I had another expert help me out for awhile but he suddenly opted out due to power failure
I may just go ahead and do it in Access but I only have 2007 and you would need that version or higher. If you're not sure I'll just do it in Excel.
2007 is fine
that's the only version I have also
I'm not turning in the excel file, everything will be in access
Great. I'll send you an update later this evening. I think I have everything I need to get started!
thank you so much, if you feel you have to opt out please let me know before the day is over. thanks again.
Yes, if I can't have it complete in the next 4 hours I will opt out.
Hello, just wanted to check up on how it's going.
Pretty well. I'm more than half way through normalizing.
I want to make sure I normalize as much as needed without getting too extreme.
thank you so much
At what level of database expert is this targeted? Beginner/MidLevel/Advanced?
Ok, I won't take it too far then...stand by and I'll send you what I have....
are you able to do the queries also? if not normalizing alone is okay
I'll do the queries if I have time. Probably can't get to that tonight. I assume you are going to actually enter the data?
Here is the link for what I have so far. I also made the assumption that you are going to define the relationships in Access?
if you're also able to do the queries before 11:59pm tomorrow that would help also, but in the mean time I will do it on my own and compare with yours later if you're able to do it
Sounds great. Give me about 15-20 minutes and I should have something for you. Were you able to download the file?
Ok. I believe I have it in 3NF at a beginner level. If this was a real situation then there are some additional things I would proabably break out like Sales Position into a separate table but since there are no additional attributes that define that positions commission or other details I really don't think it will be necessary.
I'm going to enter one record to make sure the referential integrity is good and then I'll send you the answer and wait a few minutes for you to look over it.
I have completed the normalization and entered in the first record in the associated tables to make sure it works correctly and that it enforces the referential integrity. Everything checks out. I'll send the link momentarily.
I have completed all the normalization as well as Query 1 and Query 2. I'm just out of time tonight or I would help you with the rest for the additional bonus. I'm going to switch the question to Q&A mode but don't accept any answer until you are 100% satisfied. We won't have the chat window but we can go back and forth as necessary. You can download the completed normalization in Access at the following link: http://wikisend.com/download/437190/3NF.accdb Again, if you view the file and are happy please accept the answer. If you need additional help tomorrow please let me know and I will try to assist you with the last three queries. You can give any bonus at that time. I appreciate being able to help you. If you do want some additional help tomorrow I'll do my best to help but please make sure all the data is entered into the tables so that when we design the queries we can see the results! Also, you can upload the database with all the records in place to wikisend and send me the link to save time.
Thank you Alex, I will look at this tomorrow and work on the last 3 queries and accept the answer then. I appreciate your time and effort, and hopefully I'll be able to do the queries on my own. But I will accept the answer before the due date tomorrow, thank you again.
I'm going to send you an additional link that has the file zipped in case it doesn't download correctly on the initial link. I tried it and it didn't seem to work correctly. If the first link doesn't work just try this one, I have tried it and it works successfully.
Last post tonight. I went ahead and wrote Query 3 to help you get a head start since you have a deadline tomorrow night!
Zipped and available here:
Thank you, I'm entering the data in right now. I will finish up the last 2 queries tomorrow and get back to you with further questions. Thanks again Alex.
Sounds good. Hope you are able to finish it and are happy with the results of the normalization.
Hi Alex, I'm having a bit of difficulty adding data to the "SalesOrderTable" it won't allow me to add another record. Will you please take a look for me?
excel data: http://wikisend.com/download/960476/Copy of RelativeResourceManager.xls
I sorted the information in excel by sales order number, deleted all the repeating entries. When I tried to add sales order 113 I encountered that problem.
Can you zip the file and repost on wikisend? Wikisend doesn't seem to let you post the raw accdb file. If you don't have zip just right click and select Send To -> Compressed Zip Folder. Did you add the supporting customer number etc into the customers table first? I'll look at it as soon it's posted zipped.
http://wikisend.com/download/908632/Copy of RelativeResourceManager.zip
I added the customers data first then added the sales data, and that's where I encountered the problem. I'm also not sure why query 2 and 3 are not showing up.
Also, is Transaction Type and Transaction Sequence required?
Looks like you entered the CustomerPONumber as 506 instead of 806 in the Sales Order Table. Until that says 806 you won't be able to add any records in the Sales Order Detail table. Queries 2 and 3 won't show a result until you have most of the data entered. Transaction sequence is required but it's an autonumber field that just makes that particular line unique, you have to have something like that and there is no other key field that would make it unique. Edited by Alex on 10/28/2010 at 3:25 PM EST
Were you able to enter it now?
I was able to enter in all the data but I'm not able to get query 2 and 3 to show up even with all the data.
Looks like you only have 12 transactions entered in the sales order detail table. You will need to enter in all 43 transactions. While the table is called sales order detail it does require all transactions to be in place. Once those are all in place it should work. If you switch Query 2 to Design view by right clicking and then say run you should see a couple of people for which you have entered transactions. You literally have to go line by line through the main page of your excel spreadsheet and enter the data into the associated tables. Part of the normalization process is that it won't allow you to enter invalid data that isn't in a referenced table.
let me try that
Are the transaction type/sequence columns required?
Yes, the transaction type is just part of the normalization. You could leave it out but it wouldn't be true 3NF and would require changing the referential integrity. The sequence is a column that has to be there to make the sales order detail table records unique. The primary key consists of several fields but the sequence number is XXXXX makes it unique. Doesn't matter what the value is so I set it as autonumber, just let Access enter that number and don't worry about it. Without that sequence you don't have a normalized table.
So I went back and deleted all the entries in sales order detail to add back all 43 transaction. But this time it's not letting me. I'm not sure what I'm doing wrong.
For the sales order table shouldn't there be an employee #, item # XXXXX also?
No, that all gets derived from the details of the transaction. It's how normalization works. There's no duplicated data which is what we want. I've input all 4 types of transactions which is the complete sales order #110 and will post it to the wiki momentarily. If they are looking for 3NF then the employee #, item #, etc can't be deleted. Just have to trust me on this one.
Easiest way is to just go through each sales order number and enter all transactions. As soon as the transaction is in sales order table you can enter in all the detail lines as you already have all the supporting tables filled with data. If you encounter an error it's because you don't have any associated information in the referenced tables, in this case probably sales order. Hopefully this will get you where you need to go.
It looks like a lot of people are normalizing this into 9 tables: Sales, Customer, Item, Employee, Invoice, Bank, Shipping, Cash Receipt, Check. Does this look possible to you?
Sure, it's possible but what would the Bank table include? There are no repeating details such as the Bank Address, Zip, City, etc. I would consider that to exceed standard 3NF as you have a table with a single field which is the primary key which doesn't provide anything. Really that is just creating a domain table which is part of 3NF when required but I wouldn't. I've done this stuff for years and this is exactly how I would implement this for any corporate client. Also realize that 3NF is a science and not necessarily a "one right answer" type of thing. Everyone's will probably be slightly different and that's acceptable. The additional tables you mentioned are in my opinion not at all necessary. If you would please go ahead and accept my initial answer I would appreciate it. I certainly don't expect any bonus unless the three queries I wrote work as they should. As soon as you can accept I'll be happy to help further as I have time. I'll be home in about 15 minutes and will have a little more time to devote to it if you need further assistance but hopefully at this point you feel I have exceeded the expectations of the initial answer request which was to just normalize it in Excel. Thanks! I'll check back in momentarily.
10+ Years of development experience in c#, vb, java, SQL Server, Oracle.
Yes, thank you for your answer. I will go with your method as I feel I understand it more than the 9 table route. Let me finish entering in all the data and if I have any questions I will report back.
Thanks again, I appreciate your help.
Great. I'll check back in about 15 minutes. If I can help further be sure and post the updated zip of the DB. If you don't get all the queries and I run out of time I believe the other expert from yesterday can help. He did enter a peer review and agreed with my approach.
Hi, I've inputted all the data but query 3 is not showing up. Can you tell me what I did wrong?
Just check the query. The transaction type you are filtering on is "Shipment" and not "Ship". Not sure which one of us put in the wrong value but the query does work now. I just changed the one field criteria in design view to "Shipment" instead of "Ship".
I'll be offline for a few hours.
Thank you, XXXXX XXXXX figured that out. I should be fine from here on out.
Thanks so much for your time!
Final question, for query 5, how do I input the gross profit margin and gross profit percentage? I believe I did query 4 correctly, if you can quickly take a look at that for me that would be great.
Looks good on #4. Here is #5 complete just check the output column names and formatting required. The design and funtionality is there just check the column names/query names, currency format, etc. You're all set!
Great thanks so much for your help, finally done!
Thanks! Great working with you!