 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / January 2005how will a 50x10,000 table work in Access
order form has 50 fields, the one table receives 10,000 saves (records). i cannot break apart the data in smaller packets, its relevant to 50 different part numbers and their ordered quantities respectively. can one table run the data efficiently or is there a better way? all field data is very small. thanks, rob I would suggest you re-structure the Table. The Table Structure is incorrect since you have 50 repeating groups, each group comprising of 1 Field. This violates the First Normal Form of the Relational Database Design Theory. In general, your Database / Table Structure should at least satisfy the 3rd Normal Form. See your Access / Database book(s) about Normal Forms.  SignatureHTH Van T. Dinh MVP (Access) > order form has 50 fields, the one table receives 10,000 > saves (records). i cannot break apart the data in smaller [quoted text clipped - 3 lines] > data is very small. > thanks, rob Van, thank you very much. Could you explain the 50 repeating groups again? the group of fields (the same 50 different PNs)repeat but with different values with every order number. the quantity for each part number changes per order. what is the correct way to relate the 50 different values per orderId? i will accumulate ~10000 different order IDs per year. ~rob >-----Original Message----- >I would suggest you re-structure the Table. The Table Structure is [quoted text clipped - 14 lines] > >. Like John wrote, you have One-to-Many relationship between Orders and PartNumbers and the quantity of each part per order should be treated as such. There are a number of advantages to set the One-to-Many relationship correctly but it is rather long for me to go through. For example, if you want to find out how many different Parts ordered (each different Part counted as 1 regardless of quatity) or the total number of items ordered (quantities taken into account), it is much easier if the Parts and quantities are separated into another Table from the Order table. You should look into the sample database NorthWind that comes with your Access software as it has exactly the set up you want. This is also covered in every Database Theory book. I am sure you can find one that can explain much better than I can in these newsgroups. SignatureHTH Van T. Dinh MVP (Access) > Van, thank you very much. Could you explain the 50 > repeating groups again? the group of fields (the same 50 [quoted text clipped - 3 lines] > different values per orderId? i will accumulate ~10000 > different order IDs per year. ~rob >order form has 50 fields, the one table receives 10,000 >saves (records). i cannot break apart the data in smaller >packets, its relevant to 50 different part numbers and >their ordered quantities respectively. can one table run >the data efficiently or is there a better way? all field >data is very small. Yes. There is a better way. If you have fifty fields for fifty part numbers, YOU HAVE A ONE TO MANY RELATIONSHIP. One order - fifty part numbers. You *can* break the data into smaller packets: Orders OrderID OrderDate <other info about the order> OrderDetails OrderID <link to Orders> LineNo <two-field joint primary key> PartNo Quantity Four fields, fifty rows. You're using a relational database, not a spreadsheet! Use it relationally, and you'll find that it's very capable. But... to answer your question... yes, Access can handle this; 10,000 records is pretty trivial. 10,000,000 records is getting pretty big but still possible. John W. Vinson[MVP] John, thank you very much for your advice. ~rob >-----Original Message----- > [quoted text clipped - 34 lines] > John W. Vinson[MVP] >. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.