[AccessD] Joining Queries via SQL rather than have aquerystoredin Access.

Collins, Darryl Darryl.Collins at anz.com
Thu Jan 21 16:58:02 CST 2010


Asger,

Many thanks for this.  Following the light from your example I quickly got an excellent working solution to this issue - and learn some new stuff in the process as an added bonus.
Great Stuff.

Cheers
Darryl. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Thursday, 21 January 2010 11:22 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Joining Queries via SQL rather than have aquerystoredin Access.

Pardon - the query should be like this:

SELECT 'Stuff'
FROM ITPMO_tblSchedule AS A INNER JOIN (SELECT lngDocRegID, pk_strDocId, fk_lngScheduleID FROM ITPMO_tblDocReg_Main WHERE fk_lngScheduleID Is Not Null) AS B ON A.fieldx=B.fieldx¨

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Asger Blond
Sendt: 21. januar 2010 01:13
Til: 'Access Developers discussion and problem solving'
Emne: Re: [AccessD] Joining Queries via SQL rather than have a querystoredin Access.


Something like:
SELECT 'Stuff'
FROM ITPMO_tblSchedule AS A INNER JOIN (qry_Exception_MissingDocRegSub1 ...) AS B ON A.fieldx=B.fieldx¨

Asger
-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Collins, Darryl
Sendt: 21. januar 2010 00:03
Til: Access Developers discussion and problem solving
Emne: [AccessD] Joining Queries via SQL rather than have a query storedin Access.

Hi Folks.

I hope the subject line makes sense.  I will try to explain what I am trying to do.

I have one simple query that I use to produce a RS to use in another query
'---------------
SELECT lngDocRegID, pk_strDocId, fk_lngScheduleID FROM ITPMO_tblDocReg_Main WHERE fk_lngScheduleID Is Not Null;
'--------------
As Query "qry_Exception_MissingDocRegSub1"

Then in "qry_Exception_MissingFromDocReg" I have this:
'-------------
SELECT 'Stuff'
FROM ITPMO_tblSchedule INNER JOIN (qry_Exception_MissingDocRegSub1 ...
Ra ra ra  ..
'-------------

Now this works fine etc, but I was hoping to code the whole thing in SQL, rather than having to have a query stored in Access and referencing "qry_Exception_MissingDocRegSub1" in the second query.

I keep thinking that I can use UNION or similar to do this, but my brain is not firing well today at all.

1: I hope that makes sense...
2: Anyone got any pointers on how to code that in SQL?

Regards
Darryl.

"This e-mail and any attachments to it (the "Communication") is, unless otherwise stated, confidential,  may contain copyright material and is for the use only of the intended recipient. If you receive the Communication in error, please notify the sender immediately by return e-mail, delete the Communication and the return e-mail, and do not read, copy, retransmit or otherwise deal with it. Any views expressed in the Communication are those of the individual sender only, unless expressly stated to be those of Australia and New Zealand Banking Group Limited ABN 11 005 357 522, or any of its related entities including ANZ National Bank Limited (together "ANZ"). ANZ does not accept liability in connection with the integrity of or errors in the Communication, computer virus, data corruption, interference or delay arising from or in respect of the Communication."

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

"This e-mail and any attachments to it (the "Communication") is, unless otherwise stated, confidential,  may contain copyright material and is for the use only of the intended recipient. If you receive the Communication in error, please notify the sender immediately by return e-mail, delete the Communication and the return e-mail, and do not read, copy, retransmit or otherwise deal with it. Any views expressed in the Communication are those of the individual sender only, unless expressly stated to be those of Australia and New Zealand Banking Group Limited ABN 11 005 357 522, or any of its related entities including ANZ National Bank Limited (together "ANZ"). ANZ does not accept liability in connection with the integrity of or errors in the Communication, computer virus, data corruption, interference or delay arising from or in respect of the Communication."




More information about the AccessD mailing list