specific criteria using forms for a query

[timmah]

Hi guys,



I am creating a rating model where the user inputs data using a form with combo boxes, and displays the result on another form.

The rates are per kilogram based on weight breaks of +45kg, +100kg, +500kg and +1000kg, and also take into account a destination and a commodity.

I have a table set up for each destination, for example:
"Los Angeles table"

....................+45kg .....+100kg .....+500kg
Chilled Meat..... $2.00......$2.50....... $3.00
Flowers ..........$4.00 ......$4.78......... $6.00
Sugar .............$0.78........ $1.10 ........$2.44


What I want to have happen is that a user inputs data using a combo box, a query selects the target rate and a result is displayed, example:

Combo Box .............Selection Value
Destination .............Los Angeles
Commodity ..............Flowers
Weight ....................+100kg


Desired result returned by query = $4.78

Is this possible, and if so how do I do it?!

Thanks in advance!
Tim

[TheShabz]

your sql string would be:

strSQL = "SELECT " & Me.objWeight & " FROM " & Me.objDestination & " WHERE tblName.commodity = " & Me.objCommodity & ";"

you may or may not need the & ";" at the end there. I forget if VBA wants the ; or not.

EDIT: Though I will say I'm not a fan of your table design.

[timmah]

Thanks for the reply.

I'm not the most confident with using VBA - do I just need this one-liner as code for the string to function?

I'm also not a huge fan of the table design myself - have you got a better suggestion? I'm mindful that there are ~100 commodities to ~30 destinations and each have seperate rates...

Thanks!
Tim

[TheShabz]

bah, ignore the vba. I'm not having a good day. I dont think you can even run a select query from vba. open up a query in sql view and throw this in there.

SELECT Forms!myForm.objWeight FROM Forms!myForm.objDestination WHERE tblName.commodity = Forms!myForm.objCommodity ;

see if that gives you what you want.


EDIT:
for the table design... I would have 3 tables
1. tblCommodity (CommID (PK), CommDesc)
2. tblDestination (DestID (PK), DestDesc)
3. tblPricing (PriceID (autonumber, PK), CommID (FK), DestID (FK), 45kg, 100kg, 500kg)

This way all you need to do is query just this one table and put in commodity, destination, and the weight as your criteria

If you need an explanation, let me know.

[timmah]

Ahh yes ok that makes more sense - but what does PK and FK stand for?

I'm not sure what you are suggesting with the rating table - I enter all rates for all commodities and all destinations here?

Thanks again for your help!
Tim

[TheShabz]

Primary Key
Foreign Key

in the relationship window, you will join the primary keys and foreign keys together. (also enforce referential integrity)

[timmah]

awesome stuff - thanks for your assistance!

[TheShabz]

Did the query work?