Can this problem be solved in a query?

[Arnfinn]

Hello !
I have a task I do not understand how to solve.

I have 3 tables containing information. They are named:
TANK with field names TankID, Description, SG, Volume, FillGroup, Group
TANK_FILLING with field names TankID, Time, Filling
TANK_SOUNDING with field names TankID, Sounding, Volume, XCoGlo, YCoGlo, ZCoGlo, Fsm-X, Fsm-Y

I must limit the tables to a certain TANKID (ie. TB-11-0053)
I then have a given volume for my specified TankID from TANK.

In TANK_FILLING i must limitid the records to a specified time (i.e time = 95)
I will then multiply the fillingvalue (ie. 0,5) (in TANK_FILLING) with the volume in TANK and get a filled_volume. Based on this filled_volume (and tankid) I want to search the TANK_SOUNDING table and find the volume which is higher and lower than my filled_volume, retrieve the corresponding XCoGlo for these 2 volumes, calculate a interpolated value for between these to XCOGlo (which correspond/fit my filled_volume in comparison to the lower and higher volume values) and retrive this interpolated XCOGlo as the result of my query. (Similar must be done for YCOGlo and ZCOGlo).

Was this understandable? Can this be done in one simple query?

Thanks for your time and help...

 

[aleb]

Not simple but doable

 

[Arnfinn]

And the answer is....

Thank you for your interesting answer. Since you mean it is doable can you also share the solution with me? Please provide your solution in an replay to this replay.....Thanks...

 

[sportsguy]

Solutions require a bit more detail, such as

why is it necessary to do this in one query?

Also, is the overall concept that you are backing into the expected tank Ullage after transfer?

i have the first step down in the following query and screenshot, now
why do you have to do this in one query, or are you allowing for one answer query made with multiple queries?

this query gets you the start information, I think

SELECT tblTANK.TankID, tblTANK_SOUNDING.Volume, tblTANK_FILLING.Filling, [Filling]*[tblTANK].[Volume] AS Answer1
FROM (tblTANK INNER JOIN tblTANK_FILLING ON tblTANK.TankID = tblTANK_FILLING.TankID) INNER JOIN tblTANK_SOUNDING ON tblTANK.TankID = tblTANK_SOUNDING.TankID
WHERE (((tblTANK.TankID)=[Enter TANKID]) AND ((tblTANK_FILLING.Filling)=[Select Time]));


And if so, then we can continue. . . hopefully

sportsguy

 

[aleb]

I didn't understand the "interpolation" part of your message.
Am I correct in the following?:
Result 1
Volume*Filling = 300
Result 2 from SOUNDING:
Exact value = 300, XCoGlo = 1.3
Lower values = 298,297,296 ;XCoGlo values 1.2 , 1.1 , 1.0
Higher values = 301,302,303 ;XCoGlo values 1.4 , 1.5 , 1.6

Result 3 ( interpolation )
average(1.0,1.1,1.2) = 1.1
average(1.4,1.5,1.6) = 1.5
average(1.5,1.1) = 1.3