Is DLookUP my best bet here??

[blue weasle]

Thanks in advance for any help!

I have a Timber inventory database I'm working on and need to pull a specific volume from a table based on log diameter and log height. I am using the following expression in a subform to get that value:

[volumecalc]=DLookUp("volume","VolumeTable_Doyle78","logs=forms![StandForm]![InventorySub].Form!logs And dbh=Forms![standform]![InventorySub].Form!dbh")

Then use this expression to get my volume per acres based on that individual log:

[volperacre]=([volumecalc])*(((DLookUp("BAF","StandInfo","StandID=forms![StandForm]![InventorySub].Form!StandID")/(0.005454*([dbh]^2))/DLookUp("Points","StandInfo","StandID=forms![StandForm]![InventorySub].Form!StandID")))/1000)

This is working great, but is this the most efficiant way to get this? Also, I want to beable to sum the [volperacre] total on the subform and multiply that by the ACRES field on the Parent Form but get an error for the Total Volume on the Parent form.

What can I do to show the user the current total volume on the parent form?

anyone??

 

[Pat Hartman]

Rather than using DLookup() which is inefficient at best, you should be using a query that joins to the lookup tables as the RecordSource for your form.

The problem with the sum is that you can't sum a control. You need to repeat the calculation.

=Sum(a + b * c / dLookup(...))

 

[blue weasle]

I've posted the tables and forms from my db to help deter confusion. Since I am using the DLookUp function to pull one value based on two variables, I run into problems trying to design a query based on my calculation. I have tried to join the tables but I can't get the results I need.

Again, any help would be greatly appreciated.

blue

 

[Pat Hartman]

For starters VolumeTable_Doyle78 needs a primary key. You also need to change the data types in the inventory table so that the foreign keys are defined with the same data type as the primary keys to which they relate. To define a 2-field pk, click on the first field and cntl-click on the second. When both are selected, press the key icon on the toolbar.

Once the pk is defined, you can create relationships and enforce referential integrity between the tables.

The final step will be to create the appropriate query and do the calculations within the query.

 

[blue weasle]

Thanks Pat,

It's amazing that for as long as I've been using access there's still more to learn every day. I'll look into your suggestions and see what I get. This will probably be my most elaborate DB to date and will require a lot work. I hope to bounce some more questions off the message board and maybe be able to anwer a few others questions.

Blue

 

[blue weasle]

Well Pat,

I did as you suggested and currently I'm sitting with my mouth agape, banging my head off the desk. How could I have missed this little feature all these years!!!! It works great!

Thanks a lot,

Blue