Multiple Nested IN SubQueries - Access 2003

[turkishgold]

Hi,
I'm having some difficulty with a multiple nested query in access. Here's what I have so far:

SELECT Sample.site_id, Sample.dt_tm, Sample.collecting_agency, Sample.sample_id
FROM Sample
WHERE ((Sample.site_id) In (SELECT [Sample.site_id] FROM [Sample] INNER JOIN Sites ON Sample.site_id = Sites.site_id WHERE ((Sites.site_comments = "in") 
GROUP BY [site_id],[dt_tm] HAVING Count(*)>1  And [dt_tm] = [Sample].[dt_tm]-))
ORDER BY Sample.site_id, Sample.dt_tm;

No matter what I try I get an error kicked back. I'm using Access 2003.

Any help would be appreciated.

thanks much

Tom

 

[boblarson]

Re: Multiple Nested Queries

It would REALLY help if you included the text of the ERROR message you are getting. Remember there are THOUSANDS of possible error messages in Access and it is much easier if we don't have to try to guess which one you are talking about. In fact, it could very well be a quick fix if we recognize the error message.

Oh and ...

 

[turkishgold]

Wow - that was quick!

Here you go:

<Message> in query expression <expression>. (Error 3075)
The expression you typed is not valid for the reason indicated in the message. Make sure you have typed field names and punctuation correctly, and then try the operation again.

 

[boblarson]

Not 100% sure of it all, but it looks like you are missing something between the red areas:

And [dt_tm] = [Sample].[dt_tm]-))

 

[turkishgold]

Thanks - changed that, but still no joy:

SELECT Sample.site_id, Sample.dt_tm, Sample.collecting_agency, Sample.sample_id
FROM Sample
WHERE (((Sample.site_id) In (SELECT [Sample.site_id] FROM [Sample] INNER JOIN Sites ON Sample.site_id = Sites.site_id WHERE ((Sites.site_comments = "in") GROUP BY [site_id],[dt_tm] HAVING Count(*)>1 And [dt_tm] = [Sample].[dt_tm]))
ORDER BY Sample.site_id, Sample.dt_tm;

 

[boblarson]

Did you copy and paste it in here? Because if you did, you are now missing the IN in red:

SELECT Sample.site_id, Sample.dt_tm, Sample.collecting_agency, Sample.sample_id
FROM Sample
WHERE (((Sample.site_id) [COLOR="Red"]In [/COLOR](SELECT [Sample.site_id] FROM [Sample] INNER JOIN Sites ON Sample.site_id = Sites.site_id WHERE ((Sites.site_comments = "in") GROUP BY [site_id],[dt_tm] HAVING Count(*)>1 And [dt_tm] = [Sample].[dt_tm]))
ORDER BY Sample.site_id, Sample.dt_tm;

 

[turkishgold]

Yeah i corrected it back in the query form and still no luck. Not sure why it got cut out, i'm having some weird goings-on with my copy/pasting

but, the code you see here is what I'm trying now:

SELECT Sample.site_id, Sample.dt_tm, Sample.collecting_agency, Sample.sample_id
FROM Sample
WHERE ((Sample.site_id) In (SELECT [Sample.site_id] FROM [Sample] INNER JOIN Sites ON Sample.site_id = Sites.site_id WHERE ((Sites.site_comments = "in") GROUP BY [site_id],[dt_tm] HAVING Count(*)>1  And [dt_tm] = [Sample].[dt_tm]))
ORDER BY Sample.site_id, Sample.dt_tm;

 

[turkishgold]

just tried this, no luck:

SELECT Sample.site_id, Sample.dt_tm, Sample.collecting_agency, Sample.sample_id
FROM Sample
WHERE ((Sample.site_id) In (SELECT [Sample.site_id] FROM [Sample] INNER JOIN Sites ON Sample.site_id = Sites.site_id WHERE ((Sites.site_comments = "in") GROUP BY [Sample.site_id],[Sample.dt_tm] HAVING Count(*)>1  And [Sample.dt_tm] = [Sample].[dt_tm]))
ORDER BY Sample.site_id, Sample.dt_tm;

 

[boblarson]

Does the sub query work by itself? If not, get it to work first and then add it to the other one. Make sure each part works separately first.

 

[turkishgold]

Yeah, the nested one does not work. It throws an error then highlights the GROUP keyword.

I had this query originally which someone else generated which does work:

SELECT Sample.site_id, Sample.dt_tm, Sample.collecting_agency, Sample.sample_id
FROM Sample
WHERE (((Sample.site_id) In (SELECT [site_id] FROM [Sample] As Tmp GROUP BY [site_id],[dt_tm] HAVING Count(*)>1  And [dt_tm] = [Sample].[dt_tm])))
ORDER BY Sample.site_id, Sample.dt_tm;

but wanted to further refine the results returned using the below query, which also works by itself. In other words, basically wanted to carry out the below query and feed the results into the IN portion of the above query (which seems like it could almost be another sub SELECT...WHERE...IN structure, like a query within a query within a query, but couldn't seem to make that happen myself either).

SELECT [Sample.site_id], Sites.site_comments
FROM Sample INNER JOIN Sites ON Sample.site_id = Sites.site_id
WHERE (((Sites.site_comments)="in"));

any help on combining the two efficiently would be much appreciated

thanks
Tom

 

[boblarson]

Okay, found you were missing two fields from the grouping and I got rid of the darn brackets that Access throws around:

SELECT Sample.site_id, Sample.dt_tm, Sample.collecting_agency, Sample.sample_id
FROM Sample
WHERE Sample.site_id 
In (SELECT [Sample.site_id] FROM [Sample] INNER JOIN Sites ON Sample.site_id = Sites.site_id 
WHERE Sites.site_comments = "in") 
GROUP BY [site_id],[dt_tm], [collecting_agency], [sample_id] 
HAVING Count(*)>1 And [dt_tm] = [Sample].[dt_tm]
ORDER BY Sample.site_id, Sample.dt_tm;

 

[turkishgold]

thanks, that one worked. so was just missing the [collecting_agency] and [sample_id] field form the GROUP BY part?

 

[boblarson]

yep - if they are being selected, they have to be part of the Group By. Go into the QBE grid and add 4 fields and then hit the group by button

and then see how it says Group By in all of the fields?