how will a 50x10,000 table work in Access

  • Thread starter Thread starter rob
  • Start date Start date
R

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.
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.
 
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.
Click to expand...

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-----


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]
.
Click to expand...
 
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
 
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.
 
Back
Top