Help Query Conversion from Excel

[lukeh81]

Hi,



I am trying to create a query where:

SWL_mBGL (= water level below ground level) is calculated using tbl_Water_Levels.SWL_mBTOC (water level below top of casing, which is entered manually) and tbl_TOC_Offset.TOC_mAGL (a table which shows the height above ground level of the top of casing). It must take into account the fact that the TOC_mAGL can change over time, eg. if it gets cut down from 1m to 0.5 m above ground level due to being hit by a car. So as far as a water level measurement is concerned, if a measurement is taken on a date between the Start_Date and End_Date of that TOC_mAGL row, will that value be used. Where there is no End_Date, it would be good to assume it is currently active.

So, in the case of HoleA on 1/01/2001, the SWL_mBGL will be 9, but then on the 1/01/2010 it will be 8.


The other issue I'm trying to solve is for the field 'Previous_SWL_mBTOC'. I simply want to be able to show the last SWL_mBTOC measurement for that particular Hole_ID according to date.

So, for HoleB on 1/01/2020, this field will have 101 in it (not 100, 20 or 10), regardless of how the table is sorted and filters are activatedt (eg. whether Measured_Date is sorted from highest to lowest or lowest to highest, makes no difference)



This table should be a query, the actual table won't have the last 3 fields.



I have attached a zip containing the Access File.
tbl_Collars holds the Primary Key for Hole_ID.
Calculating mRL is simply mRL: [tbl_Collar.Elevation-SWL_mBGL]

Any help would be greatly appreciated

Cheers,
Luke

[Welshgasman]

Really helps if you post details of the issue?

We are good, but not that good.

FWIW I managed to find this much of the O/P's post

Today, 05:23 AM
lukeh81 started a thread Help Query Conversion from Excel in Queries
Hi, I am trying to create a query where: SWL_mBGL (= water level below ground level) is calculated using tbl_Water_Levels.SWL_mBTOC (water...

Today, 05:23 AM
lukeh81 started a thread Help Query Conversion from Excel in Queries
Hi, I am trying to create a query where: SWL_mBGL (= water level below ground level) is calculated using tbl_Water_Levels.SWL_mBTOC (water...

[pbaldy]

Post 1 was re-moderated, I'm posting to trigger email notifications.

[mike60smart]

Hi Luke

I believe your table structures are wrong.

I think you need a table listing details of specific Holes.

Then you need a related table to enter details of the Water Levels for Each specific Hole.

[xps35]

Something like this?

SELECT tbl_Water_Levels.ID, 
       tbl_Water_Levels.Hole_D, 
       tbl_Water_Levels.Measured_Date, 
       tbl_Water_Levels.SWL_mBTOC, 
       SWL_mBTOC-TOC_mAGL AS SWL_mBGL, 
       DMax("SWL_mBTOC","tbl_Water_Levels","Hole_D = '" & [tbl_Water_Levels].[Hole_D] & "' AND Measured_Date<#" & [Measured_Date] & "#") AS Previous_SWL_mBTOC
FROM tbl_Water_Levels INNER JOIN tbl_TOC_Offset ON tbl_Water_Levels.Hole_D = tbl_TOC_Offset.Hole_ID
WHERE Measured_Date>=Start_Date And Measured_Date<=Nz(End_Date,Date());

[lukeh81]

Thanks for the responses and sorry for the late reply to them. I thought my post got deleted after I tried to edit it!

Thanks Peter, that code you made did the trick. Much appreciated.