Table Size

[dkubiak]

Is there any advantage to breaking up data into a group of smaller tables if it can also be combined into one very large table?

 

[Andrew Fergus]

It depends - how much data / how many records are you talking about?
Andrew

 

[dkubiak]

100 fields, lots and lots of data.

 

[Andrew Fergus]

How many records and what is the context? Why are there 100 fields?

 

[dkubiak]

Tracking spoilage in a manufacturing environment. 50 different defects will be tracked, then an accompanying field must identify the method of measurement (some departments record weight and some record inches).

There will be approximately 36 entries made each day with each entry using approximately 30% of the fields.

 

[Andrew Fergus]

36 readings x 50 defects x 30% usage = 540 records per day = approx 200,000 per year if you set this up right. That volume is no problemo provided it is set up correctly. To set this up properly requires a many-to-many set-up between the defects and readings - one reading can have many defects and one defect can be recorded on many readings (there have been a few posts on this lately - have a look at some of the recent posts from Norie and myself in this board). The readings may or may not be linked to either a machine or a reader - that's up to you.

Set up a defects master table with a defect id, description, unit of measurement etc - all of the static data (this table replaces the 50 x 2 fields you wanted to set up in the main table). Then set up a readings table with reading id, reader, date/ time etc - this is the same as your original table without the defects and the defect details. Then set up a readings/defects table with a reading id, defect id and a value field. This third table records the values of the defects and allows the many to many relationship - this is the table that will accumulate 200k records per annum. Set up a one to many link from defects to readings/defects and also a one to many link from readings to readings/defects. This should give you a structure that will work without overloading Access.

HTH, Andrew.

 

[dkubiak]

I think I understand what you are suggesting: one table that merely lists the 50 defects, and one table that records the quantity of defects. Is this right?

I also need to link the defects to order numbers and be able to provide calculations with a query. As our products move through the plant they are measured differently. For example, the first process measures in inches, the second in weight, and later on in weight again (but this is after being cut to a small size and therefore needing a different calculation). So, defect #1, for example, may be measured in inches, and weighed at two different stages. I planned to translate all of these measurements into a standard measurement of sheets (we deal with sheets of paper). So, if the paper was 10pt board, I would translate 5 inches to 500 sheets. If each sheet weighed 1 pound, I would translate 10 pounds to 10 sheets. If each carton (cut from a sheet) weighted .25 pounds and there were three cartons per sheet, I would translate 1.5 pounds to 2 sheets.

I don't care so much who recorded the information or where it was recorded. Is this what your "reader" table was for?

 

[Andrew Fergus]

In response to this :

one table that merely lists the 50 defects, and one table that records the quantity of defects. Is this right?

Yes.

I also need to link the defects to order numbers and be able to provide calculations with a query.

This is done through the third table - but where I used used the term readers or readings, you will use orders.

Is this what your "reader" table was for?

Yes.
Any questions, just ask.
Andrew

 

[dkubiak]

I still am not sure how I would provide calculations. I edited my previous reply to discuss the following:

Defect #1, for example, may be measured in several different ways. In one department it may be measured in inches. In the next it may be measured in full sheet weight. After passing through the die cutting process, it may be measured in weight of a single carton. I planned to set up calculations to translate all of this to a sheet count.

How can I deal with mutiple types of calculations on the same defect?

 

[dkubiak]

Could I simply add a field to the readings/defects table that includes the characteristic of the value? Reading Id, Defect Id, Value, Type of Value