SubQuery to narrow down data for duplicate value quality control

[turkishgold]

Hi,
I'm trying to devise a query which will result in showing records based on result values that equal another distributing sources result values. So, I want to select the result values from the table from a particular "agency", and then based on that subquery, use that to narrow down the results from a similar query. I'm trying to resolve duplicate "result" values between these two "distributing agencies" for the purpose of doing Quality Control on a water quality database compilation. I'd only like to see records from one distributing agency where the result value equals a result value associated with a record from the other distributing agency (trying to resolve duplicates between two agencies at a time). Hopefully this makes sense...

This is what I have:

SELECT Sample.site_id, Sample.dt_tm, Salinity.sample_id, Salinity.result_value, Salinity.distributing_agency, Salinity.param_code, Sample.collecting_agency
FROM Salinity INNER JOIN Sample on Salinity.sample_id = sample.sample_id
WHERE Sample.site_id
In (SELECT Sample.site_id FROM Sample WHERE Salinity.distributing_agency = 8 AND (Salinity.result_value In (Select Salinity.result_value From Salinity WHERE Salinity.distributing_agency = 6))
GROUP BY [Sample.site_id],[Sample.dt_tm] HAVING Count(*)>1  And Sample.dt_tm = Sample.dt_tm;)
ORDER BY Salinity.result_value, Salinity.distributing_agency, Sample.dt_tm, Sample.site_id;

any help would be greatly appreciated!

I tried to run the above query, and Access just seems to be hung now.

I had the below query which works, but I tried to take it and further refine the records returned based on what I describe as my goal above:

SELECT Sample.site_id, Sample.dt_tm, Salinity.sample_id, Salinity.result_value, Salinity.distributing_agency, Salinity.param_code, Sample.collecting_agency
FROM Salinity INNER JOIN Sample on Salinity.sample_id = sample.sample_id
WHERE Sample.[site_id]
In (SELECT [Sample.site_id] FROM [Sample] INNER JOIN Salinity On Sample.sample_id = Salinity.sample_id WHERE Salinity.distributing_agency = 8 OR Salinity.distributing_agency = 6 
GROUP BY [Sample.site_id],[Sample.dt_tm] HAVING Count(*)>1  And Sample.dt_tm = Sample.dt_tm;)
ORDER BY Salinity.result_value, Sample.dt_tm, Salinity.distributing_agency, Sample.site_id;

thanks for any help/insight

Tom

 

[WayneRyan]

Tom,

Instead of using a Nested Select statement, break this into two
separate queries.

The first is your Nested "Group By" query.

Then just do an Inner Join to that query and you should be
pretty happy.

hth,
Wayne

 

[turkishgold]

Hi,
thanks much for the response. SO, are you saying that I should pull this 2nd subquery out:

(Salinity.result_value In (Select Salinity.result_value From Salinity WHERE Salinity.distributing_agency = 6)

so that I have the query I mention below that works, and then the above query?

I guess it wouldn't matter if I ran the other query that works first (what I assume you're referring to as the "nested GROUP BY query"), then joined it to the above (thinking in terms of order of operations)? Initially I was thinking the above would need to run first to get that return set.

I'm not real clear on how to do an inner join to another query as you describe. I've used SQL for lighter applications before, but this is my first run doing some really more involved queries, so learning a bit as I go along.

thanks much
Tom

 

[WayneRyan]

Tom,

The Jet engine really doesn't handle nested Selects very well.
They can seriously degrade your performance.

Just make a Query with your "Group By" part first.

Then make the second query and just bring in the First Query
as though it were a table. The standard Inner Join should
be what you want.

Wayne

 

[turkishgold]

Hi,
OK - I tried breaking out the 2nd nested query by instead running it as initial query to get those results, and then putting those in a table. Conceptually, that's what I wanted to do in my initial trial - get this smaller set, then run the rest of the original nested query which does work against those results.

My new 1st query is here:

SELECT Sample.site_id, Salinity.distributing_agency, Sample.dt_tm, Salinity.result_value, Salinity.sample_id, Salinity.param_code, Sample.collecting_agency INTO DBSA_SAHRA_Staging
FROM Salinity INNER JOIN Sample ON Salinity.sample_id = Sample.sample_id
WHERE Salinity.distributing_agency = 6;

I then took the larger query and tried to run it referencing the table created by the above, but I'm getting this weird Access error "Syntax Error in FROM clause", and it highlights the 2nd INNER keyword in the below SQL:

SELECT b.site_id, b.dt_tm, a.sample_id, a.result_value, a.distributing_agency, a.param_code, b.collecting_agency
FROM Salinity as a 
INNER JOIN Sample as b
INNER JOIN DBSA_SAHRA_Staging as c
on a.sample_id = b.sample_id 
on c.sample_id = b.sample_id
WHERE b.site_id
In (SELECT b.site_id FROM b WHERE a.distributing_agency = 8 AND a.result_value = c.result_value)
GROUP BY [b.site_id],[b.dt_tm] HAVING Count(*)>1  And b.dt_tm = b.dt_tm
ORDER BY a.result_value, a.distributing_agency, b.dt_tm, b.site_id;

So, not sure what the problem is with the FROM statement....

any help would be greatly appreciated!

thanks
Tom