Query calculation in Base

[bluegator4]

Hello,
I am using Libre Office 4.2.6.3 on Windows 7 64 bit.

I have a table of grocery products (“Products”), a price history table (“Prices”) and a table (“Stores”) for the locations where the items are purchased. See my query design, below, for the relationships.

For each price of a product entered in “Prices”, I want the query to calculate the product price per quantity (ounces, pounds, etc), by dividing the price by the quantity. The result is to be stored in an “alias” called “PriceUOM.”

The query, as designed, produces the error message below. I have noted that this method of designating fields in a calculation, with double quotes and a period (example: “Prices”.“Price”) works if the fields in the calculation are in the same table, but apparently fields from different tables don’t work – or else I’m missing something.

I’m in the process of moving from Access to Base, and queries like this work just fine in Access, so I’m confused here.

Trust me, I HAVE googled this problem extensively and so far haven’t found anyone else struggling with this.

Any enlightenment would be appreciated. Thank you!

Query design:

Query result:

[doug]

anything useful under the ‘more’ button? are all the text fields VARCHAR? No CLOB or CHAR?

[Wildcard]

JohnSUN, you may as well convert your comment to an answer. I think you nailed it in one. And if not, you can always edit your answer. (I was going to upvote it because you said exactly what I was thinking.)

[JohnSUN]

I suspect that the field type of Price or of Quo (or both) is not specified as numeric, but as text. In this case, can work as a data transformation

CAST( REPLACE( "Prices"."Price", ',', '.' ) AS DECIMAL ) / "Products"."Qty" "PriceUOM"

or

"Prices"."Price" / CAST( REPLACE( "Products"."Qty", ',', '.' ) AS DECIMAL ) "PriceUOM"

or

CAST( REPLACE( "Prices"."Price", ',', '.' ) AS DECIMAL ) / CAST( REPLACE( "Products"."Qty", ',', '.' ) AS DECIMAL ) "PriceUOM"

[bluegator4]

Yes, in fact, “Qty” was ‘Text[VARCHAR]’ so that was the problem! I changed it to ‘DECIMAL’ to match the type of “Price”, and that fixed it. Thank you for your help, JohnSUN!