SUM referenced columns

BCNRookie

Hello everyone,
this is going to be my first post, so I hope you can help me out with this dilema.
I have a lot of different tables, some of them containing items to populate different dropdowns, and others, like the one in the picture, which I use to determine from a large dataset (according to different options in the main form) the exact row I need. The column I'm getting is "control_precio" that is reference to table "Precio", therefore I'm getting an ID from that table. The formula I use to determine which row I want:

ANY(SELECT(C_control[control_precio];AND([control_cpu]=[_THISROW].[ctrl_cpu];[control_tipo]=[_THISROW].[ctrl_tipo];[control_subtipo]=[_THISROW].[ctrl_subtipo];[control_tension]=[_THISROW].[ctrl_tension];[control_encoder]=[_THISROW].[ctrl_encoder];[control_potenciadesde]<[_THISROW].[ctrl_potencia];[control_potenciahasta]>=[_THISROW].[ctrl_potencia]))).

Well here's the thing. I do the same thing with a lot of columns trying to save all the calculated prices (picture below) to retrieve them later, and after that I want to sum all those calculated prices in a different column, but they are all ID's from table PRICE, and of course, summing ID's is not what I want to do. I want the final price as the sum of all the calculated one's

Rafael_ANEIC-PY

Hello there,

I understand you're working on an inventory system with a large variety of products and attributes, but since I don't know your use case I can't suggest anything for reducing the amount of tables you're working with, or the complexity of your expressions

If you're grabbing all those ID's from the "Price" table, you just need to dereference the price column with an expression such as [columnNameWithRefToPriceTable].[pricingColumn], you can do these for all of your REF's that link to the Price table like this:

[columnNameWithRefToPriceTable1].[pricingColumn]+[columnNameWithRefToPriceTable2].[pricingColumn]+.....

You can also wrap any of those expressions in another SELECT() that uses the result of the first SELECT() as a criteria for the second one, in order to directly take the pricing column in that table.

Of course, having large SELECT() expressions let alone nested SELECT() is a big risk for your app in terms of sync speed and stability.

BCNRookie

First of all, thanks a lot for your valuable time rafael_ANEIC-PY.
Since you mentioned the application I can comment its a tool for making quotations for elevator electronics, and maybe with more information you can give me some advice about how to manage the quotation and the prices.

I structured this way:

A: You have the "B_...." tables, which populate different dropdowns. Just in case I have to add new options I have them separated

Example of "B_...." tables

I also have the "C_...." type tables where I combined many diferent "B_tables" to be able to select the proper control according to the selections in the dropdowns of the final form. This returns usually, the "control_precio" column, which is an ID of the PRICE table.

Example of "C_...." type table

The "M_...." type table is the one that use the user to make the quotation selecting many variables. From here, using many diferent SELECTs to calculate and save each price for a later SUM.
Would you recomend to this in a separated table or in the same table?
My main concern is that I have a looooot of columns and I believe, in terms of performance, this is not a good practice. I that case, how would you do it?

I still don't know how to solve the main question I did, which was about the ID I select from the C_ type tables, that I can't get the price of the "Precios" table but only the ID, and I can't sum IDs later in a column that combine many calculated prices.
I was using LOOKUPs combined with the SELECT to go to the Price table, but it doesn't feel also a good practice. How can I do this?

Price table:

Example of the price table:

Thanks a lot!