Complicated query to traspose data and search records that approach a value

[planera63]

Hello


I created a db Access importing data from EXCEL. The attached zip file includes the file DataMSFD_Test.accdb that is only a part of the complete data-base.
Now I try to explain the data structure.

In practice, for each Region ([Region]) are identified some stations of sampling ([NationalStationID]); this ID is unique for the territory
national. For each station, sampling is carried out in several days of the year. The attached db contains only the data related to the Veneto region.


List of some field of the table D5_2015_2017 (environmental data)

• [Subregion] = subregion of sampling (e.g. Mar-Adr = Adriatic Sea)
• [Subdivision] = subdivision of sampling (e.g. NAS = North Adriatic Sea)
• [Region] = region of sampling (e.g. Veneto)
• [Determinand_Nutrient] = List of monitored parameters (e.g. Salinity, Temeprature, Cholorophyll a, Nitrate,..)
• [Concentration] = value of the parameter
• [SampleDepth] = sampling depth (in meter)
• [Year] = year of sampling
• [Month] = month of sampling
• [Day] = day of sampling

This is the query in the my database that I'm asking you for help with: Sal_T_Chl_MinDepth



SELECT Subregion, Subdivision, NationalStationId, Region, Year, Month,
Day, SampleDepth,
sum(iif(Determinand_Nutrients='Temperature',Concen tration,Null)) AS
Temperatures, sum(iif(Determinand_Nutrients='Salinity',Concentra tion,null))
AS Salinity, sum(iif(Determinand_Nutrients='Chlorophyll
(a',Concentration,null)) AS Chl_a
FROM D5_2015_2017 AS a
WHERE SampleDepth=(select min(SampleDepth)
FROM D5_2015_2017 b
WHERE a.NationalStationId=b.NationalStationId
and a.Year=b.Year
and a.Month=b.Month
and a.Day=b.Day
and b.Determinand_Nutrients in ('Temperature','Salinity','Chlorophyll a'))
AND Determinand_Nutrients in ('Temperature','Salinity','Chlorophyll a') AND a.NationalStationId='50530'
GROUP BY Sub-region, Subdivision, NationalStationId, Region, Year,
Month, Day, SampleDepth;

The query at moment select the data of Salinity, Depth and Chlorophyll, by traslating them to different columns, detected at the lower depth of the
(minimum value of [SampleDepth]) of the vertical profile carried out on each day only fot the station '50530'.


Help request.

1. Now I have got a new need or request for selecting records. I don't want to select the records that match with the lower depth (e.g. SampleDepth=0.42m, for NationalStationId=50530, Year=2016, Month=1, Day=19) of the sampling profile of the day, but the record with SampleDepth closest to the 0.5m depth of the profile carried out on each day(e.g. SampleDepth=0.44m, for NationalStationId=50530, Year=2016, Month=1, Day=19). I believe that it can implement by selecting the min value of [ABS(0.5-SampleDepth)] ) but I don't know how to write in a SQL statement or if it's necessary to write a VBA routine.
2. Moreover the query is too slow. It is possible to speed it up maybe even changing the structure of how the data is stored or by changing the query structure?

I hope I’ve made myself clear !!!

That's all

[June7]

Try:
WHERE Abs(0.5-SampleDepth)=(SELECT Min(Abs(0.5-SampleDepth))

Or:
SELECT * FROM D5_2015_2017
WHERE ID IN(SELECT TOP 1 ID FROM D5_2015_2017 AS Dupe WHERE Dupe.NationalStationID=D5_2015_2017.NationalStationID AND Dupe.Year=D5_2015_2017.Year AND Dupe.Month=D5_2015_2017.Month AND Dupe.Day=D5_2015_2017.Day AND Dupe.SampleDepth<0.5 ORDER BY Dupe.NationalStationID, Dupe.Year, Dupe.Month, Dupe.Day, Dupe.SampleDepth DESC);

Nested queries that involve Cartesian association (no join clause) of records are notoriously slow with large dataset.

[planera63]

Thanks, thanks !!!

I've replaced your statement "WHERE Abs(0.5-SampleDepth)=(SELECT Min(Abs(0.5-SampleDepth))" in place of my "SampleDepth=(select min(SampleDepth)" and the result is OK !!!!

Now is:

SELECT Sottoregione, Sottodivisione, NationalStationID, Region, Year, Month, Day, SampleDepth, sum(iif(Determinand_Nutrients='Temperature',Concen tration,Null)) AS Temperature, sum(iif(Determinand_Nutrients='Salinity',Concentra tion,null)) AS Salinity, sum(iif(Determinand_Nutrients='Chlorophyll a',Concentration,null)) AS Chl_a
FROM D5_2015_2017 AS a
WHERE Abs(0.5-SampleDepth)=(SELECT Min(Abs(0.5-SampleDepth))
FROM D5_2015_2017 b
WHERE a.NationalStationId=b.NationalStationId
and a.Year=b.Year
and a.Month=b.Month
and a.Day=b.Day
and b.Determinand_Nutrients in ('Temperature','Salinity','Chlorophyll a'))
AND a.Determinand_Nutrients in ('Temperature','Salinity','Chlorophyll a') AND a.NationalStationId='50530'
GROUP BY a.Sottoregione, a.Sottodivisione, a.NationalStationID, a.Region, a.Year, a.Month, a.Day, a.SampleDepth;

How could it run more fast ?
Maybe I could divide the query in 2-3 query or I could use an INNER JOIN ?
What do you think ?

[June7]

Sorry, don't know any way to make it faster.

[CJ_London]

SELECT Sottoregione, Sottodivisione, NationalStationID, Region, Year, Month, Day, SampleDepth, sum(iif(Determinand_Nutrients='Temperature',Concen tration,Null)) AS Temperature, sum(iif(Determinand_Nutrients='Salinity',Concentra tion,null)) AS Salinity, sum(iif(Determinand_Nutrients='Chlorophyll a',Concentration,null)) AS Chl_a
FROM D5_2015_2017 AS a
WHERE Abs(0.5-SampleDepth)=

(
SELECT Min(Abs(0.5-SampleDepth)) 
FROM D5_2015_2017 b
WHERE a.NationalStationId=b.NationalStationId
and a.Year=b.Year
and a.Month=b.Month
and a.Day=b.Day
and b.Determinand_Nutrients in ('Temperature','Salinity','Chlorophyll a')
)


AND a.Determinand_Nutrients in ('Temperature','Salinity','Chlorophyll a') AND a.NationalStationId='50530'

GROUP BY a.Sottoregione, a.Sottodivisione, a.NationalStationID, a.Region, a.Year, a.Month, a.Day, a.SampleDepth;

I think there may be a couple of things you can do on the basis the code highlighted in red is your subquery

1. Change it to a aggregate query and join on the fields

(
SELECT Min(Abs(0.5-SampleDepth)) As minDepth
FROM D5_2015_2017
WHERE Determinand_Nutrients in ('Temperature','Salinity','Chlorophyll a')
GROUP BY NationalStationId, Year, Month, Day

)

Your query then becomes

SELECT Sottoregione, Sottodivisione, NationalStationID, Region, Year, Month, Day, SampleDepth, sum(iif(Determinand_Nutrients='Temperature',Concen tration,Null)) AS Temperature, sum(iif(Determinand_Nutrients='Salinity',Concentra tion,null)) AS Salinity, sum(iif(Determinand_Nutrients='Chlorophyll a',Concentration,null)) AS Chl_a
FROM D5_2015_2017 AS a INNER JOIN

(
SELECT NationalStationId, Year, Month, Day, Min(Abs(0.5-SampleDepth)) As minDepth
FROM D5_2015_2017
WHERE Determinand_Nutrients in ('Temperature','Salinity','Chlorophyll a')
GROUP BY NationalStationId, Year, Month, Day

) b
ON a.NationalStationId=b.NationalStationId
and a.Year=b.Year
and a.Month=b.Month
and a.Day=b.Day
and Abs(0.5-SampleDepth)=b.minDepth
WHERE a.Determinand_Nutrients in ('Temperature','Salinity','Chlorophyll a') AND a.NationalStationId='50530'
GROUP BY a.Sottoregione, a.Sottodivisione, a.NationalStationID, a.Region, a.Year, a.Month, a.Day, a.SampleDepth;

2. The other thing to do is turn this query into a crosstab with 'Temperature','Salinity','Chlorophyll a' as your column headings

A couple of things on performance, ensure all fields that are grouped and/or joined on are indexed. You might also want to consider combining Year, Month, Day into a single date field

Finally Year, Month, Day are all reserved words, use them as field names at your peril.

EDIT: Sorry should have said - you will not be able to see this query in the query window because of the non standard join 'and Abs(0.5-SampleDepth)=b.minDepth'. However you could move this one line out of the join and put into the WHERE clause, probabaly won't have much effect on performance

[planera63]

Sorry, don't know any way to make it faster.

No problem.

I am not an expert in SQL so I want ask you an information.

Why your statement
WHERE Abs(0.5-SampleDepth)=(SELECT Min(Abs(0.5-SampleDepth))
run and
the other
WHERE SampleDepth=(SELECT Min(Abs(0.5-SampleDepth))
don't run ?

[June7]

Because SampleDepth values in field are not matching the calculated value. Why would you want to try matching 42 with (0.5-42)? Even if there were matches, would be wrong records.

[planera63]

Thanks.
I write your sql code in a query.

SELECT Sottoregione, Sottodivisione, NationalStationID, Region, Year, Month, Day, SampleDepth, 
sum(iif(Determinand_Nutrients='Temperature',Concentration,Null)) AS Temperature, 
sum(iif(Determinand_Nutrients='Salinity',Concentration,null)) AS Salinity, 
sum(iif(Determinand_Nutrients='Chlorophyll a',Concentration,null)) AS Chl_a
FROM D5_2015_2017 AS a INNER JOIN
(
SELECT NationalStationId, Year, Month, Day, Min(Abs(0.5-SampleDepth) As minDepth
FROM D5_2015_2017
WHERE Determinand_Nutrients in ('Temperature','Salinity','Chlorophyll a')
GROUP BY NationalStationId, Year, Month, Day
) AS b
ON a.NationalStationId=b.NationalStationId
and a.Year=b.Year
and a.Month=b.Month
and a.Day=b.Day
and Abs(0.5-SampleDepth)=b.minDepth
WHERE a.Determinand_Nutrients in ('Temperature','Salinity','Chlorophyll a') AND a.NationalStationId='50530'
GROUP BY a.Sottoregione, a.Sottodivisione, a.NationalStationID, a.Region, a.Year, a.Month, a.Day, a.SampleDepth;

but the query don't run. The Microsoft message is: "Syntax error in the clause FROM". Do you know what I have to fix in your code ?

[planera63]

Ok. Now I understand

[June7]

Well, Ajax suggested that query version, not me. Have to let him clarify.

[CJ_London]

Do you know what I have to fix in your code ?

Difficult without the tables to test the query ans there are two From's. I tested this on a dummy table and it ran OK. So may have lost something in translation to using your table
Suggest do the following actions.

I presume NationalStationId is text and not numeric since your original query ran with it as text

1. The subquery in brackets should run on it's own - so try that
2. take out the 'and Abs(0.5-SampleDepth)=b.minDepth' line and see if it runs
3. change the names of the year, months and day fields - as mentioned before they are reserved words and using them can create misleading errors

[planera63]

Dear Ajax,

I'm sorry but your code doesn't run.
If you want, you can check your code within my database that is attached in my first post and after you can send me the database with your code. If it is not possible downloading the file, let me know and I will attache again. If you send me your email I will send you.

[CJ_London]

this code works - it was missing a bracket (indicated in red) which you would have found if you had run the subquery as I suggested. And for some reason the non standard join doesn't work (probably because of the aggregating), so I just moved it to the outer criteria as I also suggested

SELECT a.Sottoregione, a.Sottodivisione, a.NationalStationID, a.Region, a.Year, a.Month, a.Day, a.SampleDepth, Sum(IIf([Determinand_Nutrients]='Temperature',[Concentration],Null)) AS Temperature, Sum(IIf(Determinand_Nutrients='Salinity',Concentration,Null)) AS Salinity, Sum(IIf(Determinand_Nutrients='Chlorophyll a',Concentration,Null)) AS Chl_a
FROM D5_2015_2017 AS a INNER JOIN
 (
SELECT NationalStationId, Year, Month, Day, Min(Abs(0.5-SampleDepth)) As minDepth
FROM D5_2015_2017
WHERE Determinand_Nutrients in ('Temperature','Salinity','Chlorophyll a')
GROUP BY NationalStationId, Year, Month, Day
)  AS b

 ON (a.Day = b.Day) AND (a.Month = b.Month) AND (a.Year = b.Year) AND (a.NationalStationId = b.NationalStationId)
WHERE (((Abs(0.5-[SampleDepth]))=[b].[minDepth]) AND ((a.Determinand_Nutrients) In ('Temperature','Salinity','Chlorophyll a')) AND ((a.NationalStationId)='50530'))
GROUP BY a.Sottoregione, a.Sottodivisione, a.NationalStationID, a.Region, a.Year, a.Month, a.Day, a.SampleDepth;

Produces this result

[CJ_London]

this is the same result using a crosstab query

TRANSFORM Sum(a.[Concentration]) AS SumOfConcentration
SELECT a.Sottoregione, a.Sottodivisione, a.NationalStationID, a.Region, a.Year, a.Month, a.Day, a.SampleDepth
FROM D5_2015_2017 AS a INNER JOIN 
(
SELECT NationalStationId, Year, Month, Day, Min(Abs(0.5-SampleDepth)) As minDepth
FROM D5_2015_2017
WHERE Determinand_Nutrients in ('Temperature','Salinity','Chlorophyll a')
GROUP BY NationalStationId, Year, Month, Day
)  AS b 
ON (a.Day = b.Day) AND (a.Month = b.Month) AND (a.Year = b.Year) AND (a.NationalStationId = b.NationalStationId)
WHERE (((Abs(0.5-[SampleDepth]))=[b].[minDepth])  AND ((a.NationalStationId)='50530'))
GROUP BY a.Sottoregione, a.Sottodivisione, a.NationalStationID, a.Region, a.Year, a.Month, a.Day, a.SampleDepth
PIVOT a.Determinand_Nutrients  In ('Temperature','Salinity','Chlorophyll a')

[planera63]

Ok. Now your code run !!!
Thanks very much.
I am a marine biologist involved in marine environmental data analysis, GIS and Remote Sensing; but I'd like to manage data and database and I know something about the SQL language.
What is you job ? Are you a software programmer ?

Could you send me your email to ask you for other advice if it should happen ?