Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-23-2019, 01:30 AM   #1
GK in the UK
Newly Registered User
 
Join Date: Dec 2017
Posts: 101
Thanks: 13
Thanked 0 Times in 0 Posts
GK in the UK is on a distinguished road
SQL SubQuery and EXISTS

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):

Code:
''    ' 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 ?


Code:
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 & "'))"

GK in the UK is offline   Reply With Quote
Old 10-23-2019, 02:27 AM   #2
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,369
Thanks: 115
Thanked 3,111 Times in 2,829 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: SQL SubQuery and EXISTS

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?
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 10-23-2019, 03:20 AM   #3
GK in the UK
Newly Registered User
 
Join Date: Dec 2017
Posts: 101
Thanks: 13
Thanked 0 Times in 0 Posts
GK in the UK is on a distinguished road
Re: SQL SubQuery and EXISTS

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.

GK in the UK is offline   Reply With Quote
Old 10-23-2019, 03:53 AM   #4
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,369
Thanks: 115
Thanked 3,111 Times in 2,829 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: SQL SubQuery and EXISTS

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
GK in the UK (10-23-2019)
Old 10-23-2019, 05:03 AM   #5
GK in the UK
Newly Registered User
 
Join Date: Dec 2017
Posts: 101
Thanks: 13
Thanked 0 Times in 0 Posts
GK in the UK is on a distinguished road
Re: SQL SubQuery and EXISTS

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.
GK in the UK is offline   Reply With Quote
Old 10-23-2019, 05:13 AM   #6
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,285
Thanks: 97
Thanked 2,032 Times in 1,979 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: SQL SubQuery and EXISTS

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 10-23-2019, 05:38 AM   #7
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,369
Thanks: 115
Thanked 3,111 Times in 2,829 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: SQL SubQuery and EXISTS

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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 10-23-2019, 05:42 AM   #8
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,370
Thanks: 556
Thanked 948 Times in 897 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: SQL SubQuery and EXISTS

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
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 10-23-2019, 05:49 AM   #9
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,369
Thanks: 115
Thanked 3,111 Times in 2,829 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: SQL SubQuery and EXISTS

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 10-23-2019, 09:58 AM   #10
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,601 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: SQL SubQuery and EXISTS

Quote:
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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
I think I need a subquery nick1408 Queries 1 11-29-2016 01:28 AM
Subquery SQL help! Pienuts Queries 3 06-25-2015 05:46 AM
Referencing SubQuery Fields in a SubQuery lovelornloser Queries 6 12-04-2013 07:24 AM
need subquery help Milothicus Queries 4 10-27-2004 01:18 AM
Help with a subquery jonnycattt Queries 0 12-06-2001 05:02 AM




All times are GMT -8. The time now is 05:08 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World