Table look up Conversion 2

[Leventcos21]

Hi,
I hope someone can lead me in the right direction. Currently, I have a lookup table in Excel that converts various units of measure.

For example, I will enter in a number for Lot Size: 5 and then select the unites of measure: mg (dropdown)

Then I select Packagesize: 10 and then select the unitsofmeasure: g (dropdown)

Then I get my conversion. .0005 is what I get in excel. Would I need multiple tables to to put the from and to conversion lookup?

thanks

 

[SkipVought]

Hi,

"Would I need multiple tables..."

Multiples of WHAT?

Similar data should almost NEVER be in segmented into separate tables.

What are you trying to do?

Skip,

_[red][/red]

 

[Leventcos21]

In Excel the conversion is done by a vloookup and Match. I want to incorporate something similiar in Access.
If the first combobox is mg and the second is g and I type 5mg. My conversion should be .005g

	mg	g
mg	1	0.001
g	1000	1
kg	1000000	1000
lbs	454545.4545	454.5454545
tons	909090909.1	909090.9091
ml	-	-
L	-	-
KL	-	-
Gallons	-	-
55 G Drum	-	-

 

[dhookom]

Create a table like:
[tt][green]
FromUOM ToUOM Multiplier
mg g 0.001
g mg 1000
....
[/green][/tt]
If you don't want to enter duplicates like above you could just enter one and use a union query to calculate the other:

SELECT FromUOM, ToUOM, Multiplier
FROM tblUOMConversion
UNION ALL
SELECT ToUOM, TromUOM, 1/Multiplier
FROM tblUOMConversion;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Leventcos21]

Dhookom,

Maybe I am not able to visualize the table structure of the array. If I have 2 comboboxes that allows me to select the FromUOM and ToUOM, how can pull the appropriate Multiplier to display in the label box? I created the first suggestion but had a long list for the combobox. The union query prompted for a parameter. Am I missing something in the structure?

		Conversion Lookup		
		To		
		mg	g	                  kg
From	mg	1	0.001	                  0.000001
	g	1000	1	                  0.001
	kg	1000000	1000	                   1
	lbs	454545.4545	454.5454545	0.454545455
	tons	909090909.1	909090.9091	909.0909091
	ml	-	-	-
	L	-	-	-
	KL	-	-	-
	Gallons	-	-	-
	55 G Drum	-	-	-

 

[PHV]

The union query prompted for a parameter
Replace this:
TromUOM
with this:
FromUOM

 

[SkipVought]

Your SECOND combobox list should be dependent on the selection in the FIRST combobox. ie. not every ToUM is valid for any given FromUM. ToCombo list is...

"select ToUM " 
"From Conversion "
"Where FromUM='" & FromCombo.Value & "'"

Skip,

_[red][/red]

 

[Leventcos21]

Thanks for everyones help.
SkipVought, when you mentioned the second combobox being dependent, that rung a bell. I don't know if this is structurally correct but choose to do a cascading combobox. I selected my From and TO and I get my calcuation.