Guidance needed - Retrieving a number based upon two inputs

[Apt]

Hi,

I am looking for an elegant way to retrieve a number from a table and display it in a form, where the number is determined by two values that will be input by the user to the form.

In particular, I want to be able to enter into a form:
- a date; and
- a class of asset
and have the relevant rate of depreciation for that asset class and that year appear in the form.

Sounds trivial and I guess it can be done in a number of ways, but I am hoping someone can point me in the best direction.

I have tried DLookup. It looks like it should work, but I have not been able to get the syntax such that it will accept a control value as a field name in the first argument of DLookup. (This would involve a table much like a spread sheet with a column for each year. I guess such tables are frowned upon and I have also heard negative comments about Lookups.)

I have experimented with concatenating the two fields into one such there is only one field to search upon. Seems to work. The table would only be updated annually and would only be a few thousand records. But, it seems a pretty tacky solution. There must be a better way.

I have experimented with having two joins between tables, but that did not seem ideal.

Perhaps a query of a query?

Perhaps a macro?

(Assuming I can get the form to work I would also like to create a report that lists assets and their depreciation.)

I’d be grateful for any suggestions.

 

[Uncle Gizmo]

Have a read here:- http://www.access-programmers.co.uk/forums/showthread.php?p=671226

 

[Apt]

Hi Uncle Gizmo, thanks for your suggestion.

I have read the information you recommended and I understand that a relational database is not like a spreadsheet. I only thought of using a table with a column for each year after coming across Dlookup.

I originally started with what I think is a conventional relational database table which had the following four fields:
ID
Asset_type
Year
Depreciation_rate

My problem is that I cannot see an elegant way to retrieve the Depreciation_rate from the table and display it in a form, where the Depreciation_rate is determined by the combination of Asset_type and Year.)

That is, I want to be able to enter the Asset_type and Year into a form and have the corresponding depreciation rate appear on the form without any other interaction by the user.

 

[Minty]

Assuming Year is a number not a date, ( and I would call it something else, Year is a reserved word in access, something like DepYears) Then Dlookup should work.

However if the depreciation is a fixed calculation based on the asset type, then you don't need to store the year, just the depreciation factor per asset type. You can then simply have a value list for the number of years do the calculation based on that and the rate for the asset type.

The depreciation text box control source would then be something line =[cmbYears] * [cmbAssetDeprecationRate]

 

[Uncle Gizmo]

Depreciation_rate is determined by the combination of Asset_type and Year.)

If you had asset type as a field and year as a field then you could run a query with parameters for Asset_type and Year.

Explain why that wont work, or even better try it in a sample dB, and use that to explain the problem.

Note:- there are some instances where you have to construct your dB like a spreadsheet. However, spreadsheet construction it is usually made by beginners who can't get there head around the new way to do it. How do I know? Well I was once one of those who couldn't understand the new way of thinking.

I don't know your dB or your problem, but I assume there is a dB way of doing it, your job is to convince me that a spreadsheet way is the only way.

 

[Apt]

Thanks Minty,

Yes, I understand about Year. I actually called it Year_bought. Potentially there could be a different rate of depreciation for every combination of Asset_Type and Year_bought.

Thus, the user needs to specify both the Year_bought and the Asset_Type to get the right depreciation rate.

DLookup ("FieldName" , "TableName" , "Criteria") worked to the extent that if I specified the FieldName then it would fetch the depreciation rate according to the criteria which was to match the Asset-type that I had input to the form.

However, I was unable to get it to take the FieldName from the name that I had input to the form. I tried just about every arrangement of quotes and brackets I could imagine. Got any suggestions for the syntax for the first argument if one is getting it from the form? Year_bought has been stored as text because it was originally in the format 2013/14 (financial year).

 

[Apt]

Hi Uncle Gizmo,

I don't really want a spreadsheet solution.

My prototype actually has 11 tables.

I have no problem joining most tables by the ID in one table and a corresponding field in another.

I can create a query and specify criteria and it will give me the record with the depreciation rate I want so long as I set the criteria in the query.

However, I don't know how to make it such that the user can specify the Year_bought and Asset_type via a form such that it comes back with the corresponding Depreciation_rate.

I am guessing there is something I am overlooking.

 

[Uncle Gizmo]

My prototype actually has 11 tables.

Do these tables have the same structure?

 

[Apt]

The tables are quite different.

For instance,
- there is a "Client" table which has detail about clients
- there is a "Property" table that has details about properties owned by the clients
- there is an "Asset" table that has details about the assets in a property.

Such tables have IDs and are joined by child records of one table having a field that matches the ID of the parent record in another table.

My problem is when I try to match records on two fields simultaneously. Easy to do in a query when you can actually specify the criteria, but I don't know how to input those two criteria from the form such that it comes back with the corresponding data.

 

[Uncle Gizmo]

So would something like this do?

Where "Team Name" would be:- "Property"

and "People" would be;- "Asset"

 

[Minty]

DLookup ("FieldName" , "TableName" , "Criteria") worked to the extent that if I specified the FieldName then it would fetch the depreciation rate according to the criteria which was to match the Asset-type that I had input to the form.

However, I was unable to get it to take the FieldName from the name that I had input to the form. I tried just about every arrangement of quotes and brackets I could imagine. Got any suggestions for the syntax for the first argument if one is getting it from the form? Year_bought has been stored as text because it was originally in the format 2013/14 (financial year).

From this I think you need to use
DLookup("DepreciationRateField","TableDepRate","[Asset_Type] = " & AssetTypeIDFromCmbBox & "AND [Year_bought] = '" yearboughtcmbBox & "'")
This assumes Asset_type is a number and Year_Bought is text as you specified

 

[Apt]

Hi Uncle Gizmo,

I think your Team1 example is pretty much the same as my situation.

Using that as an example, what I want is enter into a form a LastName and a Firstname - which in my case would be a unique combination - and have the system return the matching score.

Sounds too easy. There must be an elegant solution, but I can only see clunky solutions.

 

[Apt]

Hi Minty,

Yes, I think your solution would work.

When I had been experimenting with DLookup I was fixed on the idea of needing a table that was much like a spreadsheets. I was trying to use DLookUp to select the "row" and "column".

However, in your suggestion the table only has one field of depreciation data so that field is fixed and the selection process all happens in the criteria argument.

Sounds neat. All too obvious when you see it, but not so obvious when you don't. I'll try it and let you know how it goes.

 

[Apt]

Hi Minty,

Thanks for your advice. I have tried it and it works well.

I found that DLookup does not update automatically, but automatic update can be achieved by placing the code in the "after update" properties of the controls.

A very elegant solution.

Thanks a lot.

Much appreciated.