Definition Tables using a Query?

[AccessMaccess]

Hey All,

Complete newb here but really giving it a go. I have about 40 hours of Access training now but I'm running into some complications with a program I'm trying to build.

I have to calculate some estimations on a series of different data categories.(fields) I've been able to use a button that fires off a series of queries searching for strings and then updating the field with values. My question is, instead of building 15 queries to search for *string*, then updating table.field * value, can I just build a definition table that houses the string name in one field and daily value associated to it in another field, then run a query to search for that value and update based on the value and its daily rate?

This is probably too vague but I'll try to explain further. Lets say you need to calculate a class cost based on a location. In the class cost field, you want it to reflect $500 for a five day class because the facility that the class is hosted at is $100 a day. Only, you have hundreds of facilities with different daily rates. Is there a way to fire off a query to search for a class location description(string), then take the value of that class locations daily rate and muiltiply it by the length of the class and populate the estimated value in a different field called location cost? Can this be done with a query or is it best handled using vba?

Right now I have several queries that have a list of *string* values it searches for. Once it finds the string, I have it taking a hard coded value and multiplying it by tableclass.length and placing the total value in the location cost field. Rather than having 15 queries to accomodate to 150 different location strings and values, I was thinking it would be easier to build a table of all the class locations, their associated daily rates, then firing off a SQL statement / query to do the work. That way I can later build a form in case those daily values change so that they can be easily updated vs. hardcoding 15 queries again.

Any help would be appreciated.

[orange]

Care to show us a model of your data base/relationships?

[AccessMaccess]

Care to show us a model of your data base/relationships?

So to keep it simple I built a dummy table example. I need to search for a variety of different facility names and take the daily rate x length and update the "FacilityCost" field. Same goes for other cost types like "InstructorCost" in the tbl_Class.

The concept is to link to a database using read only linked tables. From there, I would copy the linked read only data into a local table where I can then use the data and estimate costs. In the case below, I only have two costs related to the class event, an instructor cost and a facility cost. A cost is derived from a daily rate x length. Right now I fire off about 15 queries that search for a string using something like; *John Smith* to search for John Smith, then it updates the "InstructorCost" field with update criteria like tbl_class.length x 650. Rather than firing off 15 queries like this to cover all the instructors and their individual daily rates, it would seem as though I could build a table called "InstructorCost" and house a daily rate next to each instructor name. The query would run a compare string from tbl_class against tbl_InstructorCost, then once found, take the "DailyRate" field and multiply it by "Length" and populate a total cost in the tbl_Class.InstructorCost field?

Is this a good approach? Can it be done using a query or do I need to start learning the vba aspects of Access?

There are roughly 5 cost "types" that I need to estimate for, and within each cost type there are as many as lets say 10 different strings that have different daily rates associated with them. Using InstructorCost as an example; John Smith, Joe Treddler and Tim Stone = three different string searches with three different daily rates.

[orange]

Your tables are not linked.
Normally you would not store a calculated field.... because you can always run a query to do the calculation using the basic facts involved.

My suggestions at this point.

Take a look at this link for ER Model and normal forms. Work thru examples to understand what a relationship is and the purpose of ForeignKey and PrimaryKey.

http://www.rogersaccesslibrary.com/forum/topic238.html

Create a data model of your situation. Here's an existing model that may help understand how the pieces fit together (not exactly your case, but conceptually this is where you're heading).
http://www.databaseanswers.org/data_...ding/index.htm

For general Access reference - lots of tips and videos
http://www.accessmvp.com/strive4peace/

[AccessMaccess]

Your tables are not linked.
Normally you would not store a calculated field.... because you can always run a query to do the calculation using the basic facts involved.

Hi Orange,

As mentioned above, the example I provided is a quick dummy set. In my project I'm working on to manage my work, I have tables linked and somewhat normalized. The reason I'm calculating is because I need to store an estimated about and then the actual amount permanently to provide statistics and to show on a form. Therefore I need to be able to store estimations.

If you can help me understand how to estimate using a reference/definition table with the example or by modifying the example provided, I'd appreciate it. If there's a better way, I'm all ears.

Thanks for your help in advance.

[orange]

You can do what you want with a query or queries.
But you have to show or provide an example with some
representative data. The query structure will depend on your relationships.

There is no need to send off search strings

*John Smith* to search for John Smith,

John Smith will be uniquely identified as will his daily rate as will each Class.

Show us a picture of your real data base with the relationships and we can define or suggest a query.

You may want to download this for an example.
http://www.ieor.berkeley.edu/~ieor21...ueries-SQL.doc