UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dimension/measurements, Access 2010    
 
   
LilAnnCC1
post Nov 1 2017, 10:14 AM
Post#1



Posts: 719
Joined: 31-May 04
From: Wisconsin, USA


Hello all!

I need to set up a measurement table (Height, Width, Thickness, Length, Weight, etc.) for parts. Some parts will have these standards, but other parts would have Inside/Outside Diameter and such. In the standard measurements, it could be in both feet and inches, for example. Some could also be in metrics.

Any ideas on designing the table/tables?

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
tina t
post Nov 1 2017, 11:32 AM
Post#2



Posts: 5,196
Joined: 11-November 10
From: SoCal, USA


well, that's little info to go on, but in general i'd say the following:

- store each measurement as a number
- don't store data in field names! each "type" of measurement - Height, Width, Thickness, Length, Weight, ID, OD, etc - should be a value in a record, not the name of a field.

so a table something like the following, as

tblMeasurementTypes
typeID (pk, autonumber)
typeName (one record each, for Height, Width, Thickness, Length, Weight, ID, OD, etc)

tblMeasurements
measID (pk, autonumber)
typeIDfk (fk from tblMeasurementTypes)
measValue (the actual measurement, again, as a number value)

i expect you will need a foreign key back to a table that store the records of what you're measuring, but that's a guess, since no further info was posted.

you probably also need a table of...hmm...measures? increments?...not sure what i'd call them...a descriptor of what a measurement value represents, such as feet, inches, millimeters, etc. that table would be similar to tblMeasurementTypes, and its' primary key would also be a foreign key field in tblMeasurements.

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
LilAnnCC1
post Nov 1 2017, 11:59 AM
Post#3



Posts: 719
Joined: 31-May 04
From: Wisconsin, USA


Thanks for getting back to me Tina!

What you've described below is what I actually have (Measurement Types (Height, Length, etc) and Measurements (MeasurementID, MTypeID, UOMID, PartID, Dimension). Dimension is the measurement value, but it is as a text field not a numerical field.

I've been fighting with this for days and was ready to scrap it and do what you stated NOT TO DO!

With a part having a Thickness of .25 INCH and Width of 1-1/4 INCH and a Length of 20 FEET. I need a way to take the 3 records for this part and combine them together so I get a description like:

.25" TH X 1-1/4" W X 20' L

Do you have any idea of how I would go about this?

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
projecttoday
post Nov 1 2017, 12:09 PM
Post#4


UtterAccess VIP
Posts: 8,678
Joined: 10-February 04
From: South Charleston, WV


I think you need measurements as your main table with 2 sub tables: measurement types (length, width, volume, etc.) and measurement units: (inches, centimeters, millimeters, gallons, etc.).

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
LilAnnCC1
post Nov 1 2017, 12:38 PM
Post#5



Posts: 719
Joined: 31-May 04
From: Wisconsin, USA


That's what I have:

tPart (The actual table holding PartID, Number, and Description
tPart_Measurement (MeasureID, MTypeID, UOMID, PartID, Dimention) Measurement, Measurement Type, UOM, Part and Value)
tPart_MeasurementType (MTypeID, Type) Measurement Type (Length, Width, etc.)
tPart_UOM (UOMID, UOM-Unit of Measurement (Inch, Foot, etc.)

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
projecttoday
post Nov 1 2017, 12:40 PM
Post#6


UtterAccess VIP
Posts: 8,678
Joined: 10-February 04
From: South Charleston, WV


Looks good to me. What I meant was Parts is the main table. Measurements is the sub table. Types and units are the sub/sub tables.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
kfield7
post Nov 1 2017, 01:51 PM
Post#7



Posts: 774
Joined: 12-November 03
From: Iowa Lot


re: .25" TH X 1-1/4" W X 20' L

There's a couple ways to do this.
1) using the table structure suggested, a crosstab query can align the different types of measures to allow another query to create a field that can combine them, or
2) modify one of the user defined concatenate() functions found on this site
Go to the top of the page
 
tina t
post Nov 1 2017, 02:29 PM
Post#8



Posts: 5,196
Joined: 11-November 10
From: SoCal, USA


QUOTE
Dimension is the measurement value, but it is as a text field not a numerical field.

well, as i said before, i'd recommend that you store the dimension as a number. that means standardizing the measurement data a bit, so

.25" TH X 1-1/4" W X 20' L

dimensions would be stored as

.25
1.25
20

however, unless you may need to perform math functions on the measurement data, i suppose that's not strictly necessary.

QUOTE
tPart_UOM (UOMID, UOM-Unit of Measurement (Inch, Foot, etc.)

in either case, suggest you add a field to table tPart_UOM to hold an alternate UOM indicator, where appropriate. so the record for Inch would store the double quote " character in this field, the Foot record would store single quote ' character, etc. this isn't vital, but perhaps easier to retrieve the field value than to have to write alternate indicators in the code, or in expressions, to substitute a double quote for "Foot", etc.

then it's just a matter of concatenating the data. i'd think you could write a query to retrieve the target measurement record(s), with the two supporting tables joined in the query to provide that additional info - thick, wide, long, and indicators and/or alternate indicators. looping through the query records in code would be one way to construct a string to show the measurements as you indicated.

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
LilAnnCC1
post Nov 2 2017, 12:02 PM
Post#9



Posts: 719
Joined: 31-May 04
From: Wisconsin, USA


I appreciate your help!

I'll continue to try and figure this out. Because there are "exceptions" to every rule, I can't just say give me H X W X L, as some parts will actually need TH X W X L X H, and some will need TH X ID X OD. So I need to figure out a way to determine which parts get what types of measurements.

Thanks, All!

Hope your day is happy!

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
kfield7
post Nov 2 2017, 04:32 PM
Post#10



Posts: 774
Joined: 12-November 03
From: Iowa Lot


You could

1) add a field in your parts table, or perhaps in a parts type table, that determines the format of the expression, or

2) if the measurement type is not found (null), have the concatenation skip it.
Go to the top of the page
 
LilAnnCC1
post Nov 14 2017, 04:22 PM
Post#11



Posts: 719
Joined: 31-May 04
From: Wisconsin, USA


Sorry for the re-visit, but still working on this.

Have the tables set up and form built to do the data entry. I have a text box building the full dimension and these are the some of the dimensions I get:

1/4"-20 THR X 2" L
0.5" H X 0.25" OD
5MM DIA X 45MM L
0.25" THK X 1.25" W X 20' L
0.125" THK X 1.25" W X 20' L

There are times when I need these Dimensions and I've been fighting VBA all day to get the proper string that VBA/Access wants. Right now, I'm using the value of a text box on a form called txtDim, but if I try to do anything else with these descriptions I get errors (either parameters or missing ??? failing inside the dimension text box.

How do I write the proper string to handle these dimensions?

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
kfield7
post Nov 15 2017, 08:35 AM
Post#12



Posts: 774
Joined: 12-November 03
From: Iowa Lot


I can't get too specific without seeing your tables, but here's one (of many) concatenate functions referenced on this site:

Concatenate

You should be able to use this function, or tweak if necessary, to get your results.
Go to the top of the page
 
kfield7
post Nov 15 2017, 08:38 AM
Post#13



Posts: 774
Joined: 12-November 03
From: Iowa Lot


Here's another concatenate discussion:

Concatenate
Go to the top of the page
 
LilAnnCC1
post Nov 15 2017, 02:24 PM
Post#14



Posts: 719
Joined: 31-May 04
From: Wisconsin, USA


Thank you, kfield7! I appreciate your staying with me!

The text box is already concatenated.

I'm looking to grab the entire value of the concatenated text box as a string and can't get VBA to play nice because of the (inch, foot and pound) characters that make up the value of the text box. I want to be able to run queries or procedures and may involve this "description" and I've tried every combination of double quotes, combination quotes, etc. and it still gives me issues.

Is there some fool-proof code that will get access to ignore the special characters inside a text field and just work with the entire value of the text field? I know I'm saying this wrong and not using the correct terminology.

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
kfield7
post Nov 15 2017, 04:53 PM
Post#15



Posts: 774
Joined: 12-November 03
From: Iowa Lot


Can you provide an example of what you're trying to do? What code are you using?
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 04:26 PM