Design or Query Problem?

[ShredDude]

Newbie here...struggling with a simple task.

I need to retrieve a piece of data based on two criteria. Not sure my data model is in the best shape for this.

In simple terms: tblThings has two fields, Type and Value. tlbType is related to tblThings,; there are several Types. A third table tblTier, contains Tiers and their attributes, let's say there are three tiers, Large, Medium, and Small.

A Thing's Tier is a function of it's Value. Value can change, so I didn't create Tier as field in the Thing table.

Each Type can be associated with all three Tiers. Each Type/Tier combo has a "Code" . These relationships are stored in a Type/Tier Table.


I need to find the appropriate "Code" for a Thing. for example if Thing's Type = A and Value = 100 I'd need to lookup the "Code" for the Type/Tier combo Where I've defined the Tier as function of the Value of 100. eg IF >=100, tier = Large.

Dlookup only allows me to use one critieria. SQL query works, but I'm unclear as to how to return the value to a variable in my VBA code. I need the "code" to use in subsequent calculations.

I know this is easy, but it's late and i'm not seeing it. Thanks in advance for some fresh eyes on it.

My Excel background wants me to store the calculated value. eg. I'd have the "Code" in a cell with a formula that recalcs it based on the other variables changing. But that's not the way in the DB world, is it? Still trying to learn.

Thanks,

Shred

 

[boblarson]

Dlookup only allows me to use one critieria.

No, you can use many criteria in a DLookup. For example:

=DLookup("YourFieldYouWantValueReturnedFromHere", "YourTableNameHere", "[Type]=" & [Forms]![YourFormNameHere]![YourControlNameHere] & " And [Value]=" & [Forms]![YourFormNameHere]![YourControlNameHere])

That is, if Type is numeric. If it is text then you would need quotes

=DLookup("YourFieldYouWantValueReturnedFromHere", "YourTableNameHere", "[Type]='" & [Forms]![YourFormNameHere]![YourControlNameHere] & "' And [Value]=" & [Forms]![YourFormNameHere]![YourControlNameHere])

 

[ShredDude]

It was late when I started this thread. After some sleep I built a sample DB to model my problem. good learning experience for me. I got it to work, but I don't think it's as efficient as it could be. I've attached DB for reference.

I couldn't make it work until I added a field to my Type/Tier table that contained the Tier Ceiling. when I just had the Tier Floor, I got stumped on how to find the appropriate code for a Value that was between tier Floors.

It seems wasteful to me to have both the Floor and Ceiling stored when the Ceiling is simply $.01 less than then next Floor.

I'm sure this is a common problem. Any pointers on how to handle this scenario/?

Thanks,

Shred