A query to perform a different calculation based on a "category"

[Guest]

I want a query with fields that will either perform a caluclation dependant
upon the value of a "code" in an underlying table, or return zero if the
record does not contain the specified "code"

 

[Guest]

Make the column in your query like this

NameofColumn: IIf([YourCodeField] = "SpecificCode",YourCalculation,0)

 

[Guest]

Hi Dennis,

Thanks for responding. It's not quite that simple, "my code field" is in a
table of prices and contains a list of codes which relate to various products.

However, the product quantity is calculated in different ways depending upon
the type of product. Quantity is size dependent also. The calculations are
sometimes individual items that go to make up the product, square metres, or
just metres. There are several codes that relate to each type of measure
(quantity).

The price calculations are not necessarily straightforward either, although
I've got the method for those sussed, but this means that I need as simple a
way as possible to calculate the quantities. I'm no mathematician and
following too many brackets makes my brain hurt - and can be complicated to
come back to at a later date to see what you've done.

Would it be easier to have a "categories" table and/or run several queries?
But would that involve a macro or VB and how?

Crazy Lady

Make the column in your query like this

NameofColumn: IIf([YourCodeField] = "SpecificCode",YourCalculation,0)

I want a query with fields that will either perform a caluclation dependant
upon the value of a "code" in an underlying table, or return zero if the
record does not contain the specified "code"

 

[Guest]

It would probably be in code on the click event of a calculation button.
Something like find something in a table and then check on it in case Select
statements and then run the relevant query.
Check the help on DLookUp & Case ... Select

Hi Dennis,

Thanks for responding. It's not quite that simple, "my code field" is in a
table of prices and contains a list of codes which relate to various products.

However, the product quantity is calculated in different ways depending upon
the type of product. Quantity is size dependent also. The calculations are
sometimes individual items that go to make up the product, square metres, or
just metres. There are several codes that relate to each type of measure
(quantity).

The price calculations are not necessarily straightforward either, although
I've got the method for those sussed, but this means that I need as simple a
way as possible to calculate the quantities. I'm no mathematician and
following too many brackets makes my brain hurt - and can be complicated to
come back to at a later date to see what you've done.

Would it be easier to have a "categories" table and/or run several queries?
But would that involve a macro or VB and how?

Crazy Lady

Make the column in your query like this

NameofColumn: IIf([YourCodeField] = "SpecificCode",YourCalculation,0)

I want a query with fields that will either perform a caluclation dependant
upon the value of a "code" in an underlying table, or return zero if the
record does not contain the specified "code"

 

[Guest]

Thanks Dennis I'll have a look at that.

This is where I come unstuck, though, because nowhere in help or even in a
Visual Basic Book does it give any idea of how to write code or list the
commands and what they mean/do.

Regards,

Crazy Lady

It would probably be in code on the click event of a calculation button.
Something like find something in a table and then check on it in case Select
statements and then run the relevant query.
Check the help on DLookUp & Case ... Select

Hi Dennis,

Thanks for responding. It's not quite that simple, "my code field" is in a
table of prices and contains a list of codes which relate to various products.

However, the product quantity is calculated in different ways depending upon
the type of product. Quantity is size dependent also. The calculations are
sometimes individual items that go to make up the product, square metres, or
just metres. There are several codes that relate to each type of measure
(quantity).

The price calculations are not necessarily straightforward either, although
I've got the method for those sussed, but this means that I need as simple a
way as possible to calculate the quantities. I'm no mathematician and
following too many brackets makes my brain hurt - and can be complicated to
come back to at a later date to see what you've done.

Would it be easier to have a "categories" table and/or run several queries?
But would that involve a macro or VB and how?

Crazy Lady

Make the column in your query like this

NameofColumn: IIf([YourCodeField] = "SpecificCode",YourCalculation,0)

:

I want a query with fields that will either perform a caluclation dependant
upon the value of a "code" in an underlying table, or return zero if the
record does not contain the specified "code"