SQL SubQuery and EXISTS

[GK in the UK]

SQL novice here. I've created a SQL lookup on two tables with a subquery on a single field.

But, I want to fetch more than one field from the sub query, which is an IN clause. Not to compare and select, I just want the fields.

When I try, it Access pops up error 3306, 'You have written a subquery that can return more than one field without using the EXISTS clause ... Revise the SELECT statement of the subquery to request only one field'

Working code (with only 1 field selected from tblAllocations):

''    ' WORKING CODE
''    ' SQL to query tblAllocations
''    ' Will contain all records that were in the original batch eg MBAT0000015
''    ' Form field where we want allocDocReference is called thDocReference
''    strSQL1 = "SELECT allocDocReference " & _
''                "AS thDocReference FROM tblAllocations " & _
''                "WHERE allocBatchReference ='" & varBatchID & "'"
''
''    ' SQL to query tblTransHeaders
''    ' Will contain all header records that were allocated in eg MBAT0000015
''    ' Field names match the fields on the form, don't need AS here
''    strSQL2 = "SELECT TransHeaderID, thDocReference, thDate, thDueDate, thYourOrderRef, " & _
''    "thNetValue_summed, thVatValue_summed, thPPD_summed " & _
''    "FROM tblTransHeaders WHERE thDocReference IN (" & strSQL1 & ")"

I'm re-writing the SQL but don't seem to have got it right. I've re-written it with an EXISTS (and with only the same fields extracted for the moment) but it hangs for a few seconds and Access quits. What's wrong ?

strSQL2 = "SELECT allocDocReference AS thDocReference FROM tblAllocations WHERE EXISTS " & _
"(SELECT TransHeaderID, thDocReference, thDate, thDueDate, thYourOrderRef, thNetValue_summed, thVatValue_summed, thPPD_summed " & _
"FROM tblTransHeaders " & _
"WHERE thDocReference IN (SELECT allocDocReference FROM tblAllocations WHERE allocBatchReference ='" & varBatchID & "'))"

 

[isladogs]

Its hard to follow that SQL statement and I may be barking up the wrong tree but …
Why can't you just create a single query joining the two tables using a suitable field. Possibly with an outer join?

 

[GK in the UK]

Thank you for having a look. You mean there's a SQL statement that's easy to follow ?

You know I'm not actually sure what an outer join is.

The statement is actually a single query to set a RecordSource from two tables. So it might have an outer join for all I know. Sorry I should have also shown:

ctlAllocationsDsheet.Form.RecordSource = strSQL2

The working code example:

strSQL1 is the query which returns a list of transactions from tblAllocations that were matched with payments (or each other) and the value allocated to each. One record per invoice or payment. So a batch must contain 2 records at a minimum but more likely several invoices allocated (in full or part) from one payment.

strSQL2 returns from tblTransHeaders, the invoices and payments that were IN the batch of allocations, and the value that was allocated to each at the time. As in: IN strSQL1

In the working example, I can return only 1 field from tblAllocations, using the IN clause. I need more (the value allocated and other stuff)

I want to return several fields from tblAllocations, but only want to perform the IN on one field, comparing allocDocReference with thDocReference in tblTransHeaders.

I think the answer is in getting the SQL right with an EXISTS, just not sure how to do it.

 

[isladogs]

Have a look at this article Query Join Types

If that doesn't help, suggest you post a stripped down copy of your database for someone to look at

 

[GK in the UK]

Thank you Colin, it was an Inner Join I was looking for.

Actually I really should have looked at the Query Builder before getting bogged down. Access did most of the work leaving me to tweak the query for aliases. EXISTS doesn't appear.

 

[jdraw]

GK,
Also other sources for reference.
There are several youtube videos on SQL.
Also w3schools has several SQL articles.

 

[isladogs]

In case it helps I have an example of using EXISTS in the second part of this article Synchronise Data

 

[Uncle Gizmo]

I ran into a similar problem recently. I was trying to create an unmatched query as an SQL Statement. I wanted to return several Fields, but I could only return one.

The solution it appears was to create queries and then use those in an SQL Statement.

I avoid using queries (as in the navigation pane) wherever I can, and I would like to avoid using those queries in this situation. I'm not happy with my code at the moment, and I'm looking for a better solution.

I know SQL is a very powerful language and I am reasonably sure that there must be a way of doing this, but I haven't found it yet.

I know there are some SQL experts on here, so I'm hoping that someone will know of a solution!

Sent from Newbury UK

 

[isladogs]

Tony
I'm not sure I understand
You can have as many fields as you want in an unmatched query.
You can also use more than one field as a reference for checking the matches.
May also be worth you having a quick read of the synchronise data article from my last reply as that includes several examples

 

[Pat Hartman]

I avoid using queries (as in the navigation pane) wherever I can,

Avoiding querydefs is avoiding one of the premier features of Access. Back in the 70's when I was working with embedded SQL in COBOL, I used to dream about a tool like QBE. Sure, there are things I would do to make QBE "better" but using the QBE allows me to build queries quickly and without have to memorize the schemas of hundreds of tables and thousands of column names. It is point and click. Also, I get to reuse queries in multiple places which avoids the subtle differences that can creep in when writing embedded SQL.