Help with my DLOOKUP functions.

[SpaceDyeVest28]

Oh my god it's like magic.



Okay, so when I want to display more of the OptDesc names, how do I do that? Right now it's only displaying the first one.

[Minty]

If you can explain the process ( and a spreadsheet is fine for sample data and the example sums - e.g. what you are expecting out of the system based on what you are putting in.) I'm sure we can work it out -
I'm pretty sure it won't be super simple easy, so this probably isn't going to be a five minute process.

[SpaceDyeVest28]

So, on the table I want to show the name, GPS CONTROLLER for example, then based off the NEW PRICING listed in tblOptionsPricing I want to take that pricing times an amount in the DEPRECIATION table to get a depreciated amount.

So...

Option -> New Price -> Depreciation Rate -> the figure I need.

I need the option name and depreciated price displayed for all options on a form.

[Minty]

Like I said you'll have to probably take a step back, and really think about the following ;
1. What you want the system to do in basic English simple terms. (Outputs / Reports / Calculations)
2. What you need to record in order to get those outputs (Inputs / The data you have / what you'll need to record) - This is your tables and relationships

Forms are actually a later step. If you design the forms before knowing what you need - you'll end up with a cludge or worse still do a load of work then have to start over again anyway.
As someone much more cleverer than I once said - If you can't write it down on paper, then you can't do it in Access.
And if you don't put the information In you can't get it out

[Minty]

Do you need to record the price of things over time - does your new price change and you need to know what it was a year ago for instance.
This is why I said - take a step back. Think about the whole process.
Write down the major points - then divide them into more granular data points.
The better it's thought out the easier it will be to spot the holes and catch the missing bits.

[SpaceDyeVest28]

I got ya.

I'll try and type it out to help me figure out my life.

So, I have new pricing for all of these options and depreciation rates for all these years, to figure out the market value of combines, tractors, etc... I need to be able to select a year, a year in which references a depreciation rate. Then I need that depreciation rate to be applied to all of the options, based off the year selected by the person using the database. This would be a read only database in which we only spit back numbers at people.

[SpaceDyeVest28]

So, if I'm starting basic, it should look kind of like this?

Sorry I'm such a noob but my job doesn't feel like getting me Access classes for some reason.

[June7]

Seems you could really benefit from some structured learning to gain basic knowledge in relational database principles, Access functionality, programming concepts, VBA and/or macro coding. I expect a solid week with an introductory tutorial book would be productive. A forum is not really appropriate medium to learn basics.

The second link between tblOptions and tblOptionPricing makes no sense to me. The OptPrice value is already available with one link between tables. And the table link you show is not correct. tblOptions ID is saved into OptionID field of tblOptionPricing. The linking should be on ID and OptionID fields.

Minty's db does not have PricingLookup field

[SpaceDyeVest28]

Seems you could really benefit from some structured learning to gain basic knowledge in relational database principles, Access functionality, programming concepts, VBA and/or macro coding. I expect a solid week with an introductory tutorial book would be productive. A forum is not really appropriate medium to learn basics.

The second link between tblOptions and tblOptionPricing makes no sense to me. The OptPrice value is already available with one link between tables. And the table link you show is not correct. tblOptions ID is saved into OptionID field of tblOptionPricing. The linking should be on ID and OptionID fields.

Minty's db does not have PricingLookup field

Is there anything offered online? I could maybe convince my boss.

[June7]

There are tons of instructional sites online - some free ones are actually fairly decent.

I've just never used them for Access or VBA. A book might be just as good or better.

Check out http://www.functionx.com/access/index.htm, I have seen typos that might cause some confusion but otherwise seems quite thorough, organized, and up to date.

[isladogs]

Try Steve Bishop's Access videos on You Tube.
Over 100 videos from Beginners to Intermediate then Advanced.
Clear and well structured -something there for everyone