2 fields combination to get 1 result from a third field 1

[rogerarce]

Hello friends:

How can I get this to work:

I have 1 table with 3 fieldslantID,SizeID,PriceID

There are different combinations:

Plant:
01
02
03
04

Size:
Small
Medium
Big
Tree

Price:(Depends on the combiantion Plant/Size

01 Small = 0.23
01 Big = 2.00

or

02 Big = 2.20
02 Tree = 45.00

I have no idea how to make this combination works and
get the correct price.

Any idea will be helpful. Thanks in advance!

 

[Golom]

I'm a bit unclear about your objective.

Obviously, with only one table, you would just manually enter the prices for each combination of PlantID and SizeID unless there is some formula for computing the price. Just looking at the prices, I don't see any obvious way to do that.

Can you expand a bit more about what you are attempting to accomplish?

 

[fneily]

It would help if you post all your tables and structures. I'll have to make up a whole bunch of stuff.
I guess you have:
tblPlant
PlantID Primary Key
PlantName
Plant info fields

Then you could have:
tblPrices
PriceSizeID Primary Key
PlantID
Size (obtained from value list - small, medium, big, tree)
Price

You seem to have a one-to-many relationship, one plant can have many sizes. So tblPlant connects to tblPrices through PlantID.
Now just fill in the tables.

You don't need a table for Size since it's small and limited. That would be over-normalizing.

 

[rogerarce]

Ok let's see this example:

I have 1 table named: week
This table has 3 fields: Plant,Labor,Price

There are several combinations in this table like:
plant Labor Price
01 01 0.20
01 02 0.15
01 03 0.10

Now, I have one form with the combo box for plant and labor. What I need to do is fill the third field in the form automatically depending in the choosen combination of plant and labor. Is this possible?

Thank you for your help and patience! and sorry for my late reply.

 

[fneily]

I created two comboboxes. The rowsource for the first was:
SELECT DISTINCT [Plant] FROM customer_table ORDER BY [Plant];

The rowsource for the second was:
SELECT DISTINCT [Labor] FROM [customer_table] ORDER BY [Labor];

Then create a textbox.

On the AfterUpdate (in VBA, not the property sheet)of the second combobox, put:
Me![pricebox].Value = DLookup("[Price]", "[customer_table]", "[Plant] = " & Chr(34) & Me![plant].Value & Chr(34) & " and [Labor] = " & Chr(34) & Me![labor].Value & Chr(34))

If Plant and Labor are numeric, drop the Chr(34)'s in the Dlookup function.

 

[rogerarce]

Thanks!!! fneily I will work on it.

 

[rogerarce]

I doesn't seem to work.

Plant and labor fields are text but price is numeric.

Thanks

 

[rogerarce]

It just worked. Thanks!

 

[rogerarce]

There's something funny happening with the code:

If we take this example:

plant Labor Price
01 01 0.20
01 02 0.15
01 03 0.10
02 01 0.30
02 02 0.45

What happens is that the code will always return the first
price from 01 01 $0.20 even if 02 01 is $0.30 What could be the problem? Thanks

 

[fneily]

Don't know, you didn't post your code.

 

[fneily]

Just thought - if you're selecting Labor first, then it won't pick up the new Plant. So place the code on both comboboxes. Or do things in order.

 

[rogerarce]

Fneily: Is the one you provided:

fneily (Instructor)
18 Jun 08 10:56
I created two comboboxes. The rowsource for the first was:
SELECT DISTINCT [Plant] FROM customer_table ORDER BY [Plant];

The rowsource for the second was:
SELECT DISTINCT [Labor] FROM [customer_table] ORDER BY [Labor];

Then create a textbox.

On the AfterUpdate (in VBA, not the property sheet)of the second combobox, put:
Me![pricebox].Value = DLookup("[Price]", "[customer_table]", "[Plant] = " & Chr(34) & Me![plant].Value & Chr(34) & " and [Labor] = " & Chr(34) & Me![labor].Value & Chr(34))

If Plant and Labor are numeric, drop the Chr(34)'s in the Dlookup function.

 

[fneily]

I know what I wrote. I can't see what you actually have. My table I called Customer_table - did you change this name to your tablename? Is the Dlookup on one line? Did you put the code on the AfterUpdate event of the combobox? What are your combobox's names? Copy and paste your code.

 

[rogerarce]

Here's mu code:

COMBOBOX1:
SELECT DISTINCT [codPlanta] FROM precios ORDER BY [codPlanta];

COMBOBOX2:
SELECT DISTINCT [Labor] FROM precios ORDER BY [Labor];

Filed LABOR:
after update

Me![PrecioUnitario].Value = DLookup("[preciounitario]", "[precios]", "[Planta] = " & Chr(34) & Me![Planta].Value & Chr(34) & " and [Labor] = " & Me![Labor].Value)

What happens is that takes the first value from price without taking into consideration the plant. The tables are in spanish.

The information is in one table named precios, preciounitarios is where the prices are, planta for plant
and labor is labor.

thanks for your time. I really appreciate it.

 

[fneily]

You posted "Plant and labor fields are text but price is numeric." So your code should look like:

Me![PrecioUnitario].Value = DLookup("[preciounitario]", "[precios]", "[Planta] = " & Chr(34) & Me![Planta].Value & Chr(34) & " and [Labor] = " & Chr(34) & Me![labor].Value & Chr(34))

Me![PrecioUnitario] should be an unbound textbox, maybe name it different from the field name.

Again, this is on the Afterupdate event of the Labor. So you select Plant first, then Labor. Or also put the code on Plant or on a command button, etc.

 

[rogerarce]

I had changed the Plant and labor fields to numeric, that why I did not include the & Chr(34. I just changed the [preciounitario] to unbound, but it presents the same problem.

 

[fneily]

If Plant and labor are numeric, then the code is:

Me![PrecioUnitario].Value = DLookup("[preciounitario]", "[precios]", "[Planta] = " & Me![Planta].Value & " and [Labor] = " & Me![labor].Value)

You must take out all the Chr(34)'s.