Calculation Query - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Calculation Query

Gina Powell

4 years ago

I need to create a calculation (possibly three) in my database - not sure if it would go in a query or something else.

Gallon Equivalents (GE) equation:

item size code x 1 = GE (ex. item code=LAG000CAR003 - would need to use part of that code for the equation...003)

GE x total units sold = total gallon equivalents

Then divide selling price by the GE to compare different crops.

How would I go about doing this? Thank you for any assistance you can give!

Adam Schwanz @Reply

4 years ago

You could use a query, or if it's something you're going to do reports on with a lot of records, it may be better for performance to make a field for them in the table and set the values with VBA instead.

For getting the part of the equation you could get it with the RIGHT function, or LEFT+Replace if you like to do things the hard way like me LOL. Then it's just making simple math problems in the query or VBA to set your values.

	Adam Schwanz @Reply 4 years ago
String Functions is a video on the getting that part of the code for the equation.

	Gina Powell OP @Reply 4 years ago
Okay. I will take a look at the video for reference. My boss has now simplified the equation for me (from what he previously requested). I will be creating a new column in a query called GEC, where I will extract the number portion from a separate column (would that be a string function?). Then I will use that extracted number in a calculated field in the query. Does this sound correct?

	Richard Rost @Reply 4 years ago
Yep

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View