Storing Decimals/ Using Forms Efficiently

[dkubiak]

I am building a database to store information on defects in a manufacturing environment. One table needs to include standard measurements, such as the weight of a single part. The weight has to be exact. However, I cannot enter data in my table that has a decimal, such as .25 It always turns back into a zero.

Also, once I begin storing defects I would like to be able to select the order number in a drop down list box then enter the various defects, again from a drop down list box, with the quantity of the defect found. I want this information to be entered in my "values" table. Is is possible to build a form where I can select the order number once and then enter multiple defects and have the defects stored as seperat entries but all include the order number?

 

[Norie]

Why can't you enter decimal values in your table?

 

[dkubiak]

That is what I am trying to figure out.

 

[Norie]

Well could you outline the structure of your table?

 

[dkubiak]

Okay, I got the decimal thing down now. For reasons I do not understand I had to set it for:

Field Size: Single
Format: Fixed

What about my second question. Any thoughts there? :wink:

 

[Norie]

I think what you have here is a many to may relationshio between orders and defects.

If this is right you need to have 3 tables - Orders, Defects and HasDefect.

The Orders table will hold the details of each order and will have a primary key field, say OrderID, which would be Autonumber and will identify each order uniquely.

By the way this is independent of the actual order number.

The Defects table will hold details of each type of defect and have a primark key field, say DefectID, again an Autonumber that will identify each type of defect uniquely.

The HasDefects table will have 2 foreign key fields - OrderID and DefectID.

It will also have a field for quantity of the defect found, say DefectQuantity.

Finally it would have a primary key HasDefectID, again an Autonumber which uniquely identifies each record in the table.

The relationships between the tables would be as follows.

From Orders to HasDefect, many to 1 based on OrderID.

From Defects to HasDefect, many to 1 based on DefectID.

With this set up you could create a form based on the HasDefect table that does as you want.

 

[Andrew Fergus]

Hi Dan

In response to your original question :

Also, once I begin storing defects I would like to be able to select the order number in a drop down list box then enter the various defects, again from a drop down list box, with the quantity of the defect found. I want this information to be entered in my "values" table. Is is possible to build a form where I can select the order number once and then enter multiple defects and have the defects stored as seperat entries but all include the order number?

Yes you can do this. Following on from Norie's suggestion, you would base the form on the defect values table but you would use a form with a subform. The main part of the form would have a drop down box that would look up the order number and the subform part of the form would store the type of defect (again, based on a drop down list via a combo box), the quantity and the unit of measure.

Experiment with forms and subforms as well as combo boxes and you should be able to build what you want.

HTH, Andrew.

 

[SydneyGeek]

Dan -- a quick comment on data types. Avoid Single if you need precision -- it can't approximate decimals very accurately. Double, though it takes a bit more memory, is a better choice.

Denis